Flat file Input to XML Output

bireswar.goswami · Post by **bireswar.goswami** » Thu Feb 26, 2009 9:06 am

Hi All,
I am facing a problem with Flat file input and XML output.
I have a flat file with the structure: Name,PhoneNo,Email,City. The data is like:
ABC,983212345,,Bangalore
CDE,983219999,abc@abc.com,Bangalore
Now I need to put this into an XML like:
<CustomerObject>
<CustomerName>ABC</CustomerName>
<contactMethod>
<usagetype>phone</usagetype>
<valuestring>983212345</valuestring>
</contactMethod>
<City>Bangalore</City>
</CustomerObject>
<CustomerObject>
<CustomerName>CDE</CustomerName>
<contactMethod>
<usagetype>phone</usagetype>
<valuestring>983219999</valuestring>
</contactMethod>
<contactMethod>
<usagetype>email</usagetype>
<valuestring>abc@abc.com</valuestring>
</contactMethod>
<City>Bangalore</City>
</CustomerObject>
Now when I am inporting the XML definition in DataStage I am getting the following structure only:
<CustomerObject>
<CustomerName></CustomerName>
<contactMethod>
<usagetype></usagetype>
<valuestring></valuestring>
</contactMethod>
<City></City>
</CustomerObject>
Now my problem is how can I map the phone and email both inside a single destination tag for
<contactMethod>
<usagetype></usagetype>
<valuestring></valuestring>
</contactMethod>
Please help me resolve this.
Thanks,
Bireswar

eostic · Post by **eostic** » Thu Feb 26, 2009 12:30 pm

There are a lot of details missing, in terms of what your problems might be.... things like nulls can impact whether elements are produced or not.....however, your xpath is the key to all the writing that is done....look at the "Description" property of the columns on your XMLOutput stage input link...... play with that to see how it behaves... you may resolve this easily yourself....

Ernie

bireswar.goswami · Post by **bireswar.goswami** » Sun Mar 01, 2009 12:49 am

eostic wrote:There are a lot of details missing, in terms of what your problems might be.... things like nulls can impact whether elements are produced or not.....however, your xpath is the key to all the writing that is done....look at the "Description" property of the columns on your XMLOutput stage input link...... play with that to see how it behaves... you may resolve this easily yourself....

Ernie

Sorry for the missing details. My text file like:
Customer_Name,Customer_Type,PhoneNo,Email,City
Bireswar,P,983212345, ,Bangalore
ABC,O,983219999,abc@abc.com,Bangalore
Now PhoneNo, Email both or anyone can have value for 'P' & 'O' type customers.
Now I have to generate the following XML:
<Customer>
<Name>Bireswar Goswami</Name>
<City>Bangalore</City>
<contact>
<contacttype>phone</contacttype>
<!value can be phone or email for the above line>
<contactdetail>
<value>983212345</value>
<usage>personal phone</usage>
<!value will be for phone personal phone for 'P' type customer and business phone for 'O' type customer & for email personal email for 'P' & business email for 'O'>
</contactdetail>
</contact>
</Customer>
<Customer>
<Name>ABC</Name>
<City>Bangalore</City>
<contact>
<contacttype>phone</contacttype>
<!value can be phone or email for the above line>
<contactdetail>
<value>983219999</value>
<usage>business phone</usage>
<!value will be personal phone for 'P' type customer and business phone for 'O' type customer>
</contactdetail>
</contact>
<contact>
<contacttype>email</contacttype>
<!value can be phone or email for the above line>
<contactdetail>
<value>abc@abc.com</value>
<usage>business email</usage>
<!value will be for phone personal phone for 'P' type customer and business phone for 'O' type customer & for email personal email for 'P' & business email for 'O'>
</contactdetail>
</contact>
</Customer>
Now the problems are:
1. When I am importing XML defination I am getting only one tag for <contact></contact>. So when I am going to map it with the source I have to chose either email or phone.But I need both of them and also some processing logic as discussed. So mapping and then assigning proper values to proper places is my problem.
As suggested I have also tried palying with the "Description" property,but in vain.
Please look into this and let me know how can I overcome this.

sima79 · Post by **sima79** » Sun Mar 01, 2009 2:23 am

Looks like you will need to construct the contact detail XML section separately.

This involves creating a flow that creates just the <contactDetail> XML fragment. Then join this back with the other customer details to create the final XML output. Therefore you will have two XML output stages. One for the contactDetail the other for the final XML document starting with the root element CustomerObject.

bireswar.goswami · Post by **bireswar.goswami** » Sun Mar 01, 2009 11:30 pm

sima79 wrote:Looks like you will need to construct the contact detail XML section separately.

This involves creating a flow that creates just the <contactDetail> XML fragment. Then join this back with the other customer details to create the final XML output. Therefore you will have two XML output stages. One for the contactDetail the other for the final XML document starting with the root element CustomerObject.

The contactDetail can be made separately. But then how can I merge it with the original?

sima79 · Post by **sima79** » Sun Mar 01, 2009 11:47 pm

Use a join stage to link it back to the main customer details. Have a look at the document XML Pack Best Practices. Particularly section 3.2 BUILDING AN XML DOCUMENT WITH MULTIPLE REPEATED ELEMENTS, courtesy of Kim Duke: http://www.duke-consulting.com/Download ... ctices.zip

ray.wurlod · Post by **ray.wurlod** » Mon Mar 02, 2009 12:46 am

Moderator: please move to Server forum

chulett · Post by **chulett** » Mon Mar 02, 2009 8:10 am

And in Server Speak, 'join stage' means hashed file stage.

virupakshagm · Post by **virupakshagm** » Mon Mar 02, 2009 12:58 pm

Use the Description property on the Columns pages within the output stage to parse the sequential file into XML document