How to load in XML file

shivakumar · Post by **shivakumar** » Wed Jul 15, 2009 5:35 am

Hi ,

I am having the Sequential file data as follows.

ID,Name,Location,Phone
1,kumar,hyd,123
1,kumar,ban,456
1,kumar,tpt,789
2,siva,kud,111
2,siva,chit,223

I am getting the O/p in the Sequential file as follows.

Col1,col2,col3,col4,col5,col6,col7,col8
1,kumar,hyd,ban,tpt,123,456,789
2,siva,kud,,chit,111,223,,

I am getting the Output as above in the Sequential file as Output.But As per the requirement I have to store these values in the XML file.

1.How Can I define the meta data for the XML file?

2.At Presently I am getting Maximum 3 Locations and 3 Phone numbers based on that I have building the columns in the Transformer by using the Stage Variales.
Is there any method to creat the columns based on the maximum number of records for a particular ID (Dynamically). It is not possible by using the Aggregator Stage becuase it gives the Output the key column and the aggregated value.But I need the remaining columns also.

Can anyone help me regarding this.

Thanks in advance..

Regards
Siva

chulett · Post by **chulett** » Wed Jul 15, 2009 6:04 am

Typically one would generate the metadata from an xsd that describes the target XML and then use those XPath Expressions in the XML Output stage. Have you done/tried any of that?

shivakumar · Post by **shivakumar** » Thu Jul 16, 2009 12:24 am

Hi,

I have created the XSD file as follows.

</xsd:schema<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="Employee"
minOccurs="0"
maxOccurs="unbounded">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ID="xsd:string>
<xsd:element name="Name" type="xsd:string"/>
<xsd:element name="Location1" type="xsd:string"/>
<xsd:element name="Location2" type="xsd:string"/>
<xsd:element name="Location3" type="xsd:string"/>
<xsd:element name="Phone1" type="xsd:string"/>
<xsd:element name="Phone2" type="xsd:string"/>
<xsd:element name="Phone3" type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>

Now in job I have given path for the Output file as:/datastage/tmp/xml_out.xml

The job is finished successfully.

I am unable to view the Data in the above mentioned path and also in the datastage job.Actually the file xml_out.xml is not created at the above mentioned path...but the job is finished successsfully and 2 records are going in to the XML Output Stage.

Can anyone help me how to view the XML file whether the data ius inserted or not?

Thanks in advance..

Regards
Siva

ray.wurlod · Post by **ray.wurlod** » Thu Jul 16, 2009 1:08 am

If you can't find the file and can't view the data, what precisely do you mean by "finished successfully"? What are the row counts reported by the client tools?

shivakumar · Post by **shivakumar** » Thu Jul 16, 2009 3:51 am

Hi ray,

Actually the job is showing 2 records on the XML Outputstage link.

My question is in the Options tab of the XML Output stage I have given the path for the Output file as:/datastage/tmp/customer.xml.But no file is created in that Directory...

So How can I view the data in the XML file?

Regards
Siva

eostic · Post by **eostic** » Sun Jul 26, 2009 12:03 pm

Could be an xpath issue....

Could also be an environment issue.

To be sure, add an output link to the XMLOutput Stage and send the info to another I/O stage...like sequential. Put a single column on the output link, with varchar and some long length..put a single '/" in the Description property.

Then you will be doing the I/O with a Stage you are probably comfortable with. if nothing goes down the link, the issue is in your xpath (see the Description properties of your "input" link to the XMLOutput Stage.).

Ernie