XML Output Generation

BIDSDEV · Post by **BIDSDEV** » Thu Dec 13, 2012 8:43 am

Hi All,

Below is our Source

REFERENCE	MODELCODE	OPTIONCODE	Acc1			Acc2
AUDA11001	8X1AA4		4025100		YES-A-B     YES-1-2
AUDA11002	8X1ABC		4025200		NO-C-D      YES-3-4

Below is our Target XML

Code: Select all

<?xml version="1.0" encoding="UTF-8"?>
<body>
   <vehicle>
    <specifications>
      <REFERENCE>AUDA11001</REFERENCE>
      <MODELCODE>8X1AA4</MODELCODE>
      <OPTIONCODE>4025100</OPTIONCODE>  
	</specifications>
   <accessory>
        <id>YES</id>
        <name>A</name>
        <type>B</type>
    </accessory>
    <accessory>
        <id>YES</id>
        <name>1</name>
        <type>2</type>
    </accessory>	
	<specifications>
      <REFERENCE>AUDA11002</REFERENCE>
      <MODELCODE>8X1ABC</MODELCODE>
      <OPTIONCODE>4025200</OPTIONCODE> 
	</specifications>
   <accessory>
        <id>YES</id>
        <name>3</name>
        <type>4</type>
    </accessory> 
	
  </vehicle>
</body>

Here is the Explanation for requirement:

As we can see first 3 fields (REFERENCE,MODELCODE,OPTIONCODE) goes under specifications which occurs once for each record

and is followed accessory Element which is repeated and each of Acc1 and Acc2 must be created for each record as shown under <accessory>

Trickish part here is , in the fileds Acc1 and Acc2, If the First portion of the value before '-' is "NO" Then that elemnet must not be created

For example, for the second record , we will not create Acc1, but create only Acc2 as shown below

Code: Select all

   <accessory> 
        <id>YES</id> 
        <name>3</name> 
        <type>4</type> 
    </accessory>

Please let me know the ways to implement this scenario

PS, this is sample example and in the actual file we have around 10 accessories ( till Acc10 ) that must be created for each record

Regards
Aj

eostic · Post by **eostic** » Thu Dec 13, 2012 9:30 am

the repeating node (accessory) is dictated by the number of "rows" that come in for that particular node.

So....from a basic perspective, you would want to pivot each row so that you have "n" rows for each model, where each row contains the model and spec information and one instance of the accessory.

If you have 3 accessories, you'll have 3 rows going in per model....if 97 accessories, 97 rows going into the xmlOutput Stage, per model.

Ernie

BIDSDEV · Post by **BIDSDEV** » Thu Dec 13, 2012 10:03 am

Hi Ernie,

Thanks for the reply

Just for clarification, do you want me to pivot the data before loading into XML

Also my target XML structure doesn't look like it is repeating in proportion to number of Accesory elements

Please let me know if we have any other way to handle this ( I will pivoting option also)

Please advise

Regards
Aj

BIDSDEV · Post by **BIDSDEV** » Tue Dec 18, 2012 1:35 am

Hi All,

I managed to sort this requirement by using Looping in Transformer stage

I did pivot the source data using Transformer and then created the XML in normal method

Thanks Ernie for your reply

Regards
Aj

eostic · Post by **eostic** » Tue Dec 18, 2012 6:33 am

That's a creative way to do a pivot. Nice.