- This topic has 7 replies, 1 voice, and was last updated 9 years, 1 month ago by
.
-
Posts
-
-
Dear Collegs,
I have one issue and hope that somebody can help me. Namely, that
question is reagarding one specific mapping. In this case I need to
make one very simple MS BizTalk application which will pickup one XML
file and inside Orchestration transform it (with mapping) into CSV
which later will be imported to one system. Process is very simple but
unfortionatly I faced with the big problem with mapping.Namely structure of XML file is very strange and have only two level Pages and Items.
Example input XML:
<Page>
<Item attributeID=”5″ attributeID2 =”5″ attributeValue=”OrderNumber10″ />
<Item attributeID=”6″ attributeID2 =”5″ attributeValue=”Customer10″ />
<Item attributeID=”7″ attributeID2 =”5″ attributeValue=”LineNumber1″/>
<Item attributeID=”8″ attributeID2 =”5″ attributeValue=”Item1″ />
<Item attributeID=”9″ attributeID2 =”5″ attributeValue=”Quantity1″ />
<Item attributeID=”7″ attributeID2 =”5″ attributeValue=”LineNumber2″/>
<Item attributeID=”8″ attributeID2 =”5″ attributeValue=”Item2″ />
<Item attributeID=”9″ attributeID2 =”5″ attributeValue=”Quantity2″ />
</Page>This should be mapped with output file which has this structure
<Order>
<OrderHeader>
<Customer></Customer>
<OrderNo></OrderNo>
</OrderHeader>
<OrderLine>
<LineNumber></LineNumber>
<Product></Product>
<Quantity></Quantity>
</OrderLine>
</Order>The principe of mapping is that attributeID and attributeID2 determinate where
attributeValue must be mapped in destination Schema.My problem is that
when I did mapping as a result I got a (CSV)file which has large number
of empty rows (for order header must be only one per order and for
order line can one or more rows). BizTalk for every Item tag create one
row in output file, for example I have than one row in which have only
linenumber information and all other fields are empty, in the second
row I have only Item information and other fields are empty and at the
end I have one row at which have only Quantity informations and other
fields are empty.Can somebody help me with this, is possible to map two files with this structure.
If it is possible what is the best way on which I can achieve this.
Is neccesery to use XSLT or can do this without that.
Thanks in advance.
-
Hello,
Have you tried conditional looping? For the given input, can you post the output desired in XML? That might help further understand the issue.
-
Hi,
Thanks on your update.
Will try to explain on better way.
Namely this is input file (XML)
</Pages>
<Page>
<Item Value=”ORDER DATE” CtrlID=”4″ SectionID=”1″ />
<Item Value=”ORDER NUMBER” CtrlID=”18″ SectionID=”15″ />
<Item Value=”CUSTOMER NAME” CtrlID=”66″ SectionID=”15″ />
<Item Value=”CUSTOMER ADRESS” CtrlID=”57″ SectionID=”15″ />
<Item Value=”COUNTRY” CtrlID=”59″ SectionID=”15″ />
<Item Value=”SHIPMENT DATE” CtrlID=”33″ SectionID=”15″/>
<Item Value=”AMOUNT” CtrlID=”7″ SectionID=”3″ />
<Item Value=”LOCATION” CtrlID=”38″ SectionID=”3″ />
<Item Value=”ITEM CODE” CtrlID=”5″ SectionID=”3″ />
<Item Value=”LINE NUMBER” CtrlID=”211″ SectionID=”3″ />
<Item Value=”ITEM DESCRIPTION” CtrlID=”223″ SectionID=”3″/>
<Item Value=”AMOUNT” CtrlID=”7″ SectionID=”3″ />
<Item Value=”LOCATION” CtrlID=”38″ SectionID=”3″ />
<Item Value=”ITEM CODE” CtrlID=”5″ SectionID=”3″ />
<Item Value=”LINE NUMBER” CtrlID=”211″ SectionID=”3″ />
<Item Value=”ITEM DESCRIPTION” CtrlID=”223″ SectionID=”3″ />
<Item Value=”AMOUNT” CtrlID=”7″ SectionID=”3″ />
<Item Value=”LOCATION” CtrlID=”38″ SectionID=”3″ />
<Item Value=”ITEM CODE” CtrlID=”5″ SectionID=”3″ />
<Item Value=”LINE NUMBER” CtrlID=”211″ SectionID=”3″ />
<Item Value=”ITEM DESCRIPTION” CtrlID=”223″ SectionID=”3″/>
</Page>
</Pages>As you saw here are 3 attributes, Value (this should be mapped in source schema) , CtrlID and SectionID (these two attributes determinate where Value must be mapped , for example if SectionID is 3 and CtrlID is 5 that means that this should go to Line amount in destination schema).
Output (in CSV) should be something like this:
oRDERDATE|ORDERNUMBER|CUSTOMERNUMBER|CUSTOMERADRESS|SHIPMENTDATE|COUNTRY|ORDERTYPE(STATIC VALUE)|OWNER(STATIC VALUE) this is OrderHeader
LINENUMBER|ORDERNUMBER|ITEMCODE|ITEMDESCRIPTION|COUNTRY|SHIPMENTDATE|OWNER (STATIC VALUE) this is OrderLine
LINENUMBER|ORDERNUMBER|ITEMCODE|ITEMDESCRIPTION|COUNTRY|SHIPMENTDATE|OWNER (STATIC VALUE) this is OrderLine
LINENUMBER|ORDERNUMBER|ITEMCODE|ITEMDESCRIPTION|COUNTRY|SHIPMENTDATE|OWNER (STATIC VALUE) this is OrderLineHope that now is more clear.
-
Hi,
Have you considered using Table Looping/Table Extractor functoids to achieve your scenario? Check this following blog for an example, which I believe is close to your requirements:
http://geekswithblogs.net/evankoch/archive/2007/08/07/114470.aspx
Hope this helps,
Daniel.
-
Hi Daniel,
Thanks on your answer.
I tried to applly this on my case but withotu success. Do you maybe have some ideas how it can be applied?
Is problem that if I add two links in looping Table it doesn’t work.
One again thanks.
-
I think that I am on good way to find solution and figoured out how this can work.
Namely I did next :
Added a looping functiod with an incoming link from Item, then an outgoing link to each of the elements (Order\OrderNo, Order\CustomerNo, etc). Then for each unique CtrlID, added an Equal functoid and a Value Mapping functoid. The Equal functoid have an incoming link of the Item\CtrlID and a static value. The Value Mapping functoid have an incoming linke of the Equal functiod (this must be first), and then the Item\Value element link. The Value Mapping functoid then map to the Order\OrderNo element on the outgoing schema. This repeat for all outgoing elements that need to map.
Namely this works fine for Order Header when I have only one row but the problem is with the Orders Line when I should to have more than one rows.
When I apply the same solution for the OrderLine part than I got this error message :The adapter failed to transmit message going to send port “SendPortOrder” with URL “C:\Documents and Settings\Administrator\Desktop\PROJECTS\Solution_4_29-09_11-00\out\%MessageID%.CSV”. It will be retransmitted after the retry interval specified for this Send Port. Details:”Unable to read the stream produced by the pipeline. Details: Cannot find definition for the input: LINE_ID “.
It means that I have more Items with the same CtrlID in input.
Do maybe have idea how this can work with the OrderLines where I have more than one lines.Once again thanks.
-
One issue which I just discovered in work with this.
Order number ( <Item Value=”ORDER NUMBER” CtrlID=”18″
SectionID=”15″ /> it is mapped with <Order_id>) exists only
one in Input file and it is not problem with the mapping for
OrderHeader.The problem starts with OrderLines, in that case namely I must
mapped this Order Number which showed only one in Input file into every
row of OrderLine because every order line also have a tag
<Order_id>.Can somebody maybe also help with this issue.
Thanks in advance.
-
Just one new update, namely, I figured out what should be solution and now I am bussy with XSLT inline script which should to provide that.
On this input file for order lines I applied this script :
<OrderLine>
<xsl:for-each select=”Pages”>
<xsl:for-each select=”Page”>
<xsl:for-each select=”Item”>
<xsl:variable name=”var:v24″ select=”string(@CtrlID)” />
<xsl:variable name=”var:v25″ select=”userCSharp:LogicalEq($var:v24 , "211")” />
<xsl:for-each select=”Item”>
<xsl:variable name=”var:v34″ select=”string(@CtrlID)” />
<xsl:variable name=”var:v35″ select=”userCSharp:LogicalEq($var:v34 , "223")” />
<OrderLines>
<Record_Type>
<xsl:text>ODL</xsl:text>
</Record_Type>
<MERGE_ACTION>
<xsl:text>A</xsl:text>
</MERGE_ACTION>
<xsl:if test=”string($var:v25)=’true'”>
<xsl:variable name=”var:v26″ select=”@Value” />
<LINE_ID>
<xsl:value-of select=”$var:v26″ />
</LINE_ID>
</xsl:if>
<xsl:if test=”string($var:v35)=’true'”>
<xsl:variable name=”var:v36″ select=”@Value” />
<SKU_ID>
<xsl:value-of select=”$var:v36″ />
</SKU_ID>
</xsl:if>
</OrderLines>
</xsl:for-each>
</xsl:for-each>
</xsl:for-each>
</xsl:for-each>
</OrderLine>and as result got empty line withotu result. Something like this :
ODH|A|DVHC|4950885|Normal|Released|0|NLLO|DVHC|20100507||||||Conmpany AAA|London||||||| (THIS IS ORDER HEADER)
(empty line)
(HERE SHOULD TO BE INFORMATIONS ABOUT ORDER LINE)
ODH|A|DVHC|4950885|Normal||Conmpany AAA|||||||||||||||| (THIS IS ORDER HEADER NOTES)From some reason result is empy line. Do maybe somebody know where made mistake here and how is possible to fulfill line with good data.
Thanks in advance.
-
-
-
-
-
-
-
- The forum ‘BizTalk 2004 – BizTalk 2010’ is closed to new topics and replies.