PROBLEM:
Guys I have specific problem related to xml stages. I have two tables i.e. ADDRESS,USER with data as mentioned below [A,B]. The output required is XML in format given [C].
ANALYSIS TILL NOW:
I have managed to get output as mentioned in [D]. I developed a job inner joining ADDRESS & USER to get data as mentioned in [E]. Then I used XML table defination XML output stage by keeping user as key column.
Query:
- Please let me know the steps I shud use to get below XML output? is my approach correct.
[A]
ADDRESS:
LE,ADDR
----------
le1,add11
le1,add12
le2,add21
le2,add22
le2,add23
USER:
LE,USER
----------
le1,user11
le1,user12
le1,user13
le1,user14
le1,user15
le2,user21
le2,user22
[C]
<customer>
<legal_entities>
<legal_entity> LE1 </legal_entity>
<addresses>
<adress> add11 </adress>
<adress> add12 </adress>
</addresses>
<users>
<user> User11 </user>
<user> User12 </user>
<user> User13 </user>
<user> User14 </user>
<user> User15 </user>
</users>
<legal_entity> LE2 </legal_entity>
<addresses>
<adress> add21 </adress>
<adress> add22 </adress>
<adress> add23 </adress>
</addresses>
<users>
<user> User21 </user>
<user> User22 </user>
</users>
</legal_entities>
</customer>
[D]
ADDRESS inner join USER:
LE, ADDR, USER
----------
"le1","add1","user1"
"le1","add1","user2"
"le1","add1","user3"
"le1","add1","user4"
"le1","add1","user5"
"le1","add2","user1"
"le1","add2","user2"
"le1","add2","user3"
"le1","add2","user4"
"le1","add2","user5"
"le1","add3","user1"
"le1","add3","user2"
"le1","add3","user3"
"le1","add3","user4"
"le1","add3","user5"
"le2","add4","user6"
"le2","add4","user7"
"le2","add5","user6"
"le2","add5","user7"
"le2","add6","user6"
"le2","add6","user7"
[E]
<?xml version="1.0" encoding="UTF-8"?>
<!--
- Generated by Ascential Software Corporation, DataStage - XMLOutput stage -
- Tue Apr 4 05:26:11 2006
-->
<customer>
<legal_entities>
<legal_entity> le1 </legal_entity>
<addresses>
<adress> add1 </adress>
</addresses>
<users>
<user> user1 </user>
<user> user2 </user>
<user> user3 </user>
<user> user4 </user>
<user> user5 </user>
</users>
</legal_entities>
<legal_entities>
<legal_entity> le1 </legal_entity>
<addresses>
<adress> add2 </adress>
</addresses>
<users>
<user> user1 </user>
<user> user2 </user>
<user> user3 </user>
<user> user4 </user>
<user> user5 </user>
</users>
</legal_entities>
<legal_entities>
<legal_entity> le1 </legal_entity>
<addresses>
<adress> add3 </adress>
</addresses>
<users>
<user> user1 </user>
<user> user2 </user>
<user> user3 </user>
<user> user4 </user>
<user> user5 </user>
</users>
</legal_entities>
<legal_entities>
<legal_entity> le2 </legal_entity>
<addresses>
<adress> add4 </adress>
</addresses>
<users>
<user> user6 </user>
<user> user7 </user>
</users>
</legal_entities>
<legal_entities>
<legal_entity> le2 </legal_entity>
<addresses>
<adress> add5 </adress>
</addresses>
<users>
<user> user6 </user>
<user> user7 </user>
</users>
</legal_entities>
<legal_entities>
<legal_entity> le2 </legal_entity>
<addresses>
<adress> add6 </adress>
</addresses>
<users>
<user> user6 </user>
<user> user7 </user>
</users>
</legal_entities>
</customer>
XML output in specific format
Moderators: chulett, rschirm, roy
The biggest hurdle in XML generation seems to be the XPath statements needed. Best Practice is to import the metadata for your target XML and let DataStage generate it for you automatically. Do you have or can you get (or generate) an xsd for this? The other big hurdle is proper designation of the repeating element - the field marked as the Key field.
Read the XML Pack Design Guide pdf that should be in your Docs directory - it outlines techniques to simplify this. I haven't done this in a PX job but I know what I would do in a Server job - chunks. Don't try to build it all at once, put 'XML chunks' (it's a checkbox in the stage) into hashed files keyed in your case by Legal Entity. One would hold all Addresses per LE and the second would hold all Addresses. Then simply stream in a distinct list of LEs, do the lookups and stick the bits together. Works great.
As I mentioned, it's all spelled out in that pdf. I'm sure it could be adapted to work in a PX job. Or perhaps someone could explain a more 'PX appropriate' technique to use...
Read the XML Pack Design Guide pdf that should be in your Docs directory - it outlines techniques to simplify this. I haven't done this in a PX job but I know what I would do in a Server job - chunks. Don't try to build it all at once, put 'XML chunks' (it's a checkbox in the stage) into hashed files keyed in your case by Legal Entity. One would hold all Addresses per LE and the second would hold all Addresses. Then simply stream in a distinct list of LEs, do the lookups and stick the bits together. Works great.
As I mentioned, it's all spelled out in that pdf. I'm sure it could be adapted to work in a PX job. Or perhaps someone could explain a more 'PX appropriate' technique to use...
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers