DSXchange

Hi,
I have a problem in achieving the following XML output.Please help me out with this!

Input : 3 columns
ID1 ID2 ID3
45 52 87

Marked in red are Hard-coded columns.

Output :

<TCRMOrganizationBObj>
<TCRMOrganizationNameBObj>
<OrganizationName>Corporate Markets</OrganizationName>
</TCRMOrganizationNameBObj>
<MasterID>
<admID>45</admID>
<SysID>1000015</SysID>
</MasterID>
<MasterID>
<admID>52</admID>
<SysID>1000014</SysID>
</MasterID>
<MasterID>
<admID>87</admID>
<SysID>CODOS</SysID>
</MasterID>
</TCRMOrganizationBObj>


I m finding it difficult to join the "MasterID" XML chunk.. i tried to produce 3 diff chunks and merge then and connect it to another XML output, it still doesn work!

If only admID would be present, then i could've pivoted the rows and triggered based on admID column, but here i have one more column SysID in the same hierarchy..

Please help me out with these!!!!

XML has many flavors...... my favourite is one where all repeating groups are "collected" under a common compound element...... The better designed documents, imho, are those that have a major element, such as "AllMasterIDs", with the individual MasterIDs beneath it or inside it. I can't say for sure if this is the issue you are having, but for a quick test, add another element your XPATH (the description property of your columns on the input link), just in front of MasterID. Call it AllMasterIDs. Use the Aggregate option and see if that puts them all nicely in place, albeit with the extra tag. Then send your XML output to a Transformer (hopefully it's not "too" huge to have on a link in a single column for a parallel Job) and edit out the un-necessary tag. This is very easy to do in Server or in a BASIC transformer using sometihng like ereplace.

Ernie

Thank you!

You are welcome. Let us know how it goes. I did one today for a site that was just like this, where I had a repeating complex <DETAIL> element (it had a few sub-elements) directly under the main element.

...what was desired was something like this (not the exact strings, but I thought I'd outline it here for illustration purposes)...


<mainElementOfTheDocument>
<DETAIL><TYPE/><VALUE/></DETAIL>
<DETAIL>...
<DETAIL>...
</mainElementOfTheDocument>

originally I had this xpath:

/mainElementOfTheDocument/DETAIL/TYPE/text()

having some issues, I immediately added some dummy elements:

/mainElementOfTheDocument/Custom/Elements/DETAIL/TYPE/text()

...and everything came out perfect, but with the extra tags:

<mainElementOfTheDocument>
...
<Custom>
<Elements>
<DETAIL .....>
<DETAIL ....>
<DETAIL ....>
</Elements>
</Custom>
</mainElementOfTheDocument>

The tags were very easy to remove with a follow-up transform Derivation:

ereplace(ereplace(ereplace(ereplace(inLink.xmlContentColumn,"<Custom>"," "),"<Elements>"," "),</Custom>"," "),"</Elements>"," ")


A bit odd I'll admit, but hey, the whole thing was working in less than 10 minutes.

Ernie

Thanks a lot! I suppose 10 mins is a bit too much for my requirement.
The testing would really come up screaming at me! Thanks anyway!

You seem to have misunderstood the "10 minutes" comment - that was how long it took Ernie to build the job.

Yeah...I thought 8 minutes to solve the problem and move onto the next thing was pretty good! ; )

Hell, it takes me longer than that to come up with a new job's name.

ouch! sorry abt that!!!

chulett wrote:Hell, it takes me longer than that to come up with a new job's name.

Methinks you need simpler job naming standards.