I'm trying to write complex XML output using the XML output stage. The information comes from different tables (customer, address, contacts = phone numbers). Since addresses and telephone numbers can be mutiple for 1 customer, they must appear in repeating groups in the XML.
My question: how do I produce such an output? Given 1 customer, I could do a lookup for the addresses, but then I could get multiple results in the lookup, how do I merge them into the main customer flow to get 1 XML output message? Anyone with a good suggestion?
Thanks in advance, Paul Mulder
Complex XML output generation
Moderators: chulett, rschirm, roy
Craig, thanks for your answer. Yes, I've read the XML pack designer guide. It seems the input must be delivered in denormalized format, i.e. columns with repeated content for XML elements at a higher level, and columns with detailed content at a lower level.
But how must this input be created, then? It seems to be a join in a 1:n relationship, but how do you deal with joins to 2 different tables, like in my example of customer with n addresses and m phonenumbers?
Maybe I should change the way I look for a solution; in that case please give me a hint. Thanks, Paul
But how must this input be created, then? It seems to be a join in a 1:n relationship, but how do you deal with joins to 2 different tables, like in my example of customer with n addresses and m phonenumbers?
Maybe I should change the way I look for a solution; in that case please give me a hint. Thanks, Paul
Interesting... memory is a harsh mistress. I thought all the tips we got on how to handle situations like yours was in the pdf document I mentioned. Now that I actually go back through it, I see that wasn't the case. 
Going back through my collection of DataStage flotsam and jetsam, I found that what I was thinking of is a 2003 Ascential document by Hernando Borda called Using the XML PACK - Best Practices which came in zip file with a number of exported jobs and xml file samples. It documents the techniques that we've been using to great effect. Hopefully I can find someplace here to post it.
Basically, it involves creating 'chunks' of xml and loading them into hashed files keyed at the proper level. In your case, there would be one for addresses and another for phone numbers. Then your main processing job can stream keys in, pick those chunks up from the hashed files, and then generate the output you need. Not sure if that's enough of a 'hint' to get you going.
Going back through my collection of DataStage flotsam and jetsam, I found that what I was thinking of is a 2003 Ascential document by Hernando Borda called Using the XML PACK - Best Practices which came in zip file with a number of exported jobs and xml file samples. It documents the techniques that we've been using to great effect. Hopefully I can find someplace here to post it.
Basically, it involves creating 'chunks' of xml and loading them into hashed files keyed at the proper level. In your case, there would be one for addresses and another for phone numbers. Then your main processing job can stream keys in, pick those chunks up from the hashed files, and then generate the output you need. Not sure if that's enough of a 'hint' to get you going.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Paul,
I think your requirement is to get Address and Telephone number as repeating elements in the XML. This has been explained on page 4-16 and 17 of the XMLPACK_20_Designer document.
For Example - To enable the <Address> element to repeat within the <Customer> node, you need to define this (that of Address) XPath expression as the repetition path. To do
this, mark the associated input column as the key in the XML Output stage.
If there are multiple Addresses/Phone numbers for a particular Customer, then you would need to pivot these before sending it to the XML Output stage.
In this manner, you would get multiple addresses/phone numbers as repeating in a single Customer node.
Hope this helps.
The Bird
I think your requirement is to get Address and Telephone number as repeating elements in the XML. This has been explained on page 4-16 and 17 of the XMLPACK_20_Designer document.
For Example - To enable the <Address> element to repeat within the <Customer> node, you need to define this (that of Address) XPath expression as the repetition path. To do
this, mark the associated input column as the key in the XML Output stage.
If there are multiple Addresses/Phone numbers for a particular Customer, then you would need to pivot these before sending it to the XML Output stage.
In this manner, you would get multiple addresses/phone numbers as repeating in a single Customer node.
Hope this helps.
The Bird
Craig, thanks again for your fast reply. If you could find the 2003 document, I would be very grateful if you could send this to me: paul.mulder@nl.ibm.com
TheBird, thank you for your reply too, I know how to handle this part now, but then how do you generate this kind of input, like I mentioned in one of the earlier postings (see above). Craig suggested another approach described in a document from 2003, which I hope to receive.
TheBird, thank you for your reply too, I know how to handle this part now, but then how do you generate this kind of input, like I mentioned in one of the earlier postings (see above). Craig suggested another approach described in a document from 2003, which I hope to receive.