XML transformation help needed

jaysheel · Post by **jaysheel** » Thu Jul 12, 2012 6:33 am

Hi,

I have a requirement where I need to convert flat files to XML files. I am able to do it. Problem arrived when the customer changed the XML format. I need to trnaform it now as per the requirement.

The input looks like :

UPC-NUMBER | WMITEMNUMBER | DESCRIPTION | ATS| TROUBLE| RECEIPT-IN-PROGRESS| OIP |EIP |WIP |FACILITYID |QTY_ON_HAND |CREATE_DATE_TIME|DCID|COUNTRYCODE|ORGANIZATIONCODE|PRODUCTCLASS

0072228635653|0000003021995|BF SPORT NURS |123|0|0|0|0|0|5868|123|12/22/2011.10.00|6279|US|XYZ|auction
0001467100043|0000003016219|SN CNVRT STRPLES|28|0|0|1|0|0|5868|29|12/22/2011.10.00|6279|US|XYZ|auction

Previous output :

<Items ReasonText="20111222102212" ShipNode="5868"
YantraMessageGroupID="5868.20111222102212.303919.1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="Interface_IMS_InventorySupplyUpdate_eDC_Integration_Snapshot.xsd">`
<Item InventoryOrganizationCode="XYZ" ItemID="0000003021995"
ProductClass="auction">
<Supplies>
<Supply ATS="123" EIP="0.0" OIP="0.0" QTY_ON_HAND="212" RECEIPTIN-PROGRESS="0.0" TROUBLE="0.0" WIP="0.0" />
</Supplies>
</Item>
</Items>

Now the requirement is :

<Items ReasonText="20111222102212" ShipNode="5868"
YantraMessageGroupID="5868.20111222102212.303919.1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="Interface_IMS_InventorySupplyUpdate_eDC_Integration_Snapshot.xsd">
<Item InventoryOrganizationCode="XYZ" ItemID="3021995"
ProductClass="AUCTION">
<Supplies>
<Supply SupplyType="ATS" Quantity="123" />
<Supply SupplyType="EIP" Quantity="0.0" />
<Supply SupplyType="OIP" Quantity="0.0" />
<Supply SupplyType="RIP" Quantity="123" />
<Supply SupplyType="TRBL" Quantity="0.0" />
<Supply SupplyType="WIP" Quantity="0.0" />
</Supplies>
</Item>
</Items>

I am stuck with this format change. Do I need to go with XML transformer here ? I am generating XML based on a trigger column and have to pass it to a message queue.

chulett · Post by **chulett** » Thu Jul 12, 2012 6:39 am

Show us your XPath Expressions for Supplies/SupplyType/Quantity.

jaysheel · Post by **jaysheel** » Thu Jul 12, 2012 6:43 am

Craig,

I dont have an XPATH written yet. I am creating the XPATH from the sample output that I have here. I have to split this single row into multiple rows for the Supply category here.

I guess it will be /Supplies/Supply/@SupplyType
/Supplies/Supply/@Quantity

chulett · Post by **chulett** » Thu Jul 12, 2012 7:09 am

I meant the original one as I'm assuming it just needs to be 'tweaked'.

jaysheel · Post by **jaysheel** » Fri Jul 13, 2012 12:44 am

Craig,

The I am not provided with the XSD. The original format yielded me the below XPATH expression for the columns.

Items/Item/Supplies/Supply/@ATS
Items/Item/Supplies/Supply/@EIP
Items/Item/Supplies/Supply/@OIP
Items/Item/Supplies/Supply/@RIP
Items/Item/Supplies/Supply/@TROUBLE

Thr original format had all the Supply types in a single row with the value being written against them for the Supply attribute. Now the Supply attribute is split into SupplyType and the Quantity under Supply. This is like splitting a single row into multiple rows but within same XML block. I need to generate such XML documents based on Trigger column and the pass each document to a Message queue. On top of this, its going to be multiple instance job for different source files. Split the source files into XMLs for every 100 records in the source file. I managed to split them based on the rowcount. But stuck at this new XML format. I am not used to XML stages. But somehow got hold of the XML output Stage. But this is the current challenge infront of me.

chulett · Post by **chulett** » Fri Jul 13, 2012 7:22 am

Sorry, it's been years since I've had any dealings with XML so not sure what the change would need to be. However, you've posted some excellent supporting information so I'm sure someone who knows will come along and help you out.

Ravi.K · Post by **Ravi.K** » Fri Jul 13, 2012 11:35 am

Define SupplyType1,SupplyType2,SupplyType3,SupplyType4,SupplyType5 columns with derivation as ATS ,EIP ,OIP ,RIP ,TROUBLE for respective Quantities.

Pviot the data based on the below.

SupplyType - SupplyType1,SupplyType2,SupplyType3,SupplyType4,SupplyType5
Quantity - Quantity1,Quantity2,Quantity3,Quantity4,Quantity5

Apply below Xpath for the 2 attributes and make it as repititive element.

/Supplies/Supply/@SupplyType
/Supplies/Supply/@Quantity

jaysheel · Post by **jaysheel** » Mon Jul 16, 2012 12:13 am

Thanks Ravi for your response.
But I did not understand your solution.

Let me explain my understanding here.
You want me to define Supply type 1 to 5 in Transformer with values as ATS,EIP,OIP,RIP and TROUBLE in transformer. Here my questions is regarding the next sentence "For respective Quantities" - What is meant by this. I can make Supplytype1=ATS,SupplyType2=EIP etc. Where should I define their values here.
Currently My column names are ATS,EIP etc with their values mapped from source.

Next is regarding the repetitive element. What are they and how do I define it ?

jaysheel · Post by **jaysheel** » Mon Jul 16, 2012 12:48 am

Ravi, I tried the pivoting option. But could not understand the repetitive element option you told. So my output is coming as below.
Need to tune this now.

<Items ReasonText="20111222102212" ShipNode="5868"
YantraMessageGroupID="5868.20111222102212.303919.1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="Interface_IMS_InventorySupplyUpdate_eDC_Integration_Snapshot.xsd">
<Item InventoryOrganizationCode="XYZ" ItemID="3021995"
ProductClass="AUCTION">
<Supplies>
<Supply SupplyType="ATS" Quantity="123" />
</Supplies>
</Item>
</Items>

<Items ReasonText="20111222102212" ShipNode="5868"
YantraMessageGroupID="5868.20111222102212.303919.1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="Interface_IMS_InventorySupplyUpdate_eDC_Integration_Snapshot.xsd">
<Item InventoryOrganizationCode="XYZ" ItemID="3021995"
ProductClass="AUCTION">
<Supplies>
<Supply SupplyType="EIP" Quantity="123" />
</Supplies>
</Item>
</Items>

<Items ReasonText="20111222102212" ShipNode="5868"
YantraMessageGroupID="5868.20111222102212.303919.1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="Interface_IMS_InventorySupplyUpdate_eDC_Integration_Snapshot.xsd">
<Item InventoryOrganizationCode="XYZ" ItemID="3021995"
ProductClass="AUCTION">
<Supplies>
<Supply SupplyType="RIP" Quantity="123" />
</Supplies>
</Item>
</Items>

eostic · Post by **eostic** » Mon Jul 16, 2012 8:40 am

The key to Ravi's explanation is that the change that was made to the xml is that what "used to be" multiple attributes within one single element is now instead a set of "repeating elements" inside one single element.

In order for xmloutput to produce such a set of "repeating" elements, they first need to be a "set" of repeating rows (which you have done via the pivot)......

...and then, identify those particular columns as the "lowest level repeating node" that you are concerned with. This is called the "repeating element" when we are talking about the xmlInput and xmlOutput Stages --- and it is indicated syntactically by marking one of those columns as a "key".

Ernie

jaysheel · Post by **jaysheel** » Tue Jul 17, 2012 6:00 am

Ernie,

What I got was a bit different. I managed to pivot the data and write to XML.
But I wanted something like this.

<Items ReasonText="20111222102212" ShipNode="5868"
YantraMessageGroupID="5868.20111222102212.303919.1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="Interface_IMS_InventorySupplyUpdate_eDC_Integration_Snapshot.xsd">
<Item InventoryOrganizationCode="XYZ" ItemID="3021995"
ProductClass="AUCTION">
<Supplies>
<Supply SupplyType="ATS" Quantity="123" />
<Supply SupplyType="EIP" Quantity="0.0" />
<Supply SupplyType="OIP" Quantity="0.0" />
<Supply SupplyType="RIP" Quantity="123" />
<Supply SupplyType="TRBL" Quantity="0.0" />
<Supply SupplyType="WIP" Quantity="0.0" />
</Supplies>
</Item>
<Item InventoryOrganizationCode="WWW" ItemID="3021996"
ProductClass="AUCTION">
<Supplies>
<Supply SupplyType="ATS" Quantity="456" />
<Supply SupplyType="EIP" Quantity="0.0" />
<Supply SupplyType="OIP" Quantity="0.0" />
<Supply SupplyType="RIP" Quantity="123" />
<Supply SupplyType="TRBL" Quantity="0.0" />
<Supply SupplyType="WIP" Quantity="0.0" />
</Supplies>
</Item>
</Items>

But What i got was

xsi:noNamespaceSchemaLocation="Interface_IMS_InventorySupplyUpdate_eDC_Integration_Snapshot.xsd">
<Item InventoryOrganizationCode="XYZ" ItemID="3021995"
ProductClass="AUCTION">
<Supplies>
<Supply SupplyType="ATS" Quantity="123" />
<Supply SupplyType="EIP" Quantity="0.0" />
<Supply SupplyType="OIP" Quantity="0.0" />
<Supply SupplyType="RIP" Quantity="123" />
<Supply SupplyType="TRBL" Quantity="0.0" />
<Supply SupplyType="WIP" Quantity="0.0" />
<Supply SupplyType="ATS" Quantity="456" />
<Supply SupplyType="EIP" Quantity="0.0" />
<Supply SupplyType="OIP" Quantity="0.0" />
<Supply SupplyType="RIP" Quantity="123" />
<Supply SupplyType="TRBL" Quantity="0.0" />
<Supply SupplyType="WIP" Quantity="0.0" />
</Supplies>
</Item>

I gave the Supplytype and Quantity as Key which came as repeated elements as required. But They repeated even for the next set of outer tags also. I wanted for each Item information the Supply types to be repeated in set of 6 rows. But If I make the Item attributes also as keys then every single Supply attribute data is coming for the Item attribute. In short row by row. So I am kind of struggling from past few days to get this resolved.

eostic · Post by **eostic** » Tue Jul 17, 2012 9:23 am

We will assume that this comes in as at least 2 rows, and is turned into twelve...and that the first six have organization code of XYZ and that the next six have organization code of WWW (plus the other attribute values).

I can't remember ever seeing the xml stage blatantly "drop" data....where did that other organization code go?

Make only the SupplyType a key (no others) and be sure you select "Aggregate all rows" as the option.

It could be that the implmentation of all attributes instead of elements might be an issue, but try the above and let us know what happens.

Ernie