XML Wirter Stage and Output

[netboyks]

Hi there,
We are using DataStage 6.0.1 and I installed xml pack 2.0 and trying to create an xml output. I am facing a problem where there are multiple products for an Invoice and I do not know how to repeat the tags for the products. The outpur records looks like this.

<Invoices>
<Invoice>
<Invoice_Num>1</Invoice_Num>
<Invoice_Dt>2004-02-20</Invoice_Dt>
<Invoice_amt>56789.98</Invoice_amt>
<Invoice_Status>Y</Invoice_status>
-<Products>
<Products>
<Product_Num>12</Product_Num>
<Product_name>ABCS</Product_Name>
<Units>63</Units>
</Product>
</Products>
</Invoice>
<Invoice>
<Invoice_Num>1</Invoice_Num>
<Invoice_Dt>2004-02-20</Invoice_Dt>
<Invoice_amt>56789.98</Invoice_amt>
<Invoice_Status>Y</Invoice_status>
-<Products>
<Products>
<Product_Num>45</Product_Num>
<Product_name>DUEF</Product_Name>
<Units>48</Units>
</Product>
</Products>
</Invoice>
</Invoices>

But I want the output in the following format where the Product is repeating for the same Invoice.

<Invoices>
<Invoice>
<Invoice_Num>1</Invoice_Num>
<Invoice_Dt>2004-02-20</Invoice_Dt>
<Invoice_amt>56789.98</Invoice_amt>
<Invoice_Status>Y</Invoice_status>
-<Products>
<Product>
<Product_Num>12</Product_Num>
<Product_name>ABCS</Product_Name>
<Units>63</Units>
</Product>
<Product>
<Product_Num>45</Product_Num>
<Product_name>DUEF</Product_Name>
<Units>48</Units>
</Product>
</Products>
</Invoice>
</Invoices>


The Invoices and the Products are two hash files in the job which are joined in the transformer and sent out to XML writer stage.
Do I have to pass the output to the Sequential file stage from the XML Writer stage?

Could someone suggest me to get around this problem ASAP please.

Thanks

Netboyks.

[vmcburney]

I find when I have problems with the XML writer it helps to work backwards. Create the XML output you want to achieve, which you've done in your post, save it to a file. Import this file definition using the DataStage Manager XML Meta Data Importer.

You had a couple title case mistakes but the using the following XML:

<Invoices>
<Invoice>
<Invoice_Num>1</Invoice_Num>
<Invoice_Dt>2004-02-20</Invoice_Dt>
<Invoice_amt>56789.98</Invoice_amt>
<Invoice_Status>Y</Invoice_Status>
-<Products>
<Product>
<Product_Num>12</Product_Num>
<Product_Name>ABCS</Product_Name>
<Units>63</Units>
</Product>
<Product>
<Product_Num>45</Product_Num>
<Product_Name>DUEF</Product_Name>
<Units>48</Units>
</Product>
</Products>
</Invoice>
</Invoices>

Should give you this heirarchy which you can load into the Description column of you XML writer stage:

Code: Select all

Name          Description
Invoice         /Invoices/Invoices/Text()
Invoice_Status  /Invoices/Invoice/Invoice_Status/tex()
Product_Num    /Invoices/Invoices/Products/Product/Product_Num/text()
Product_Name   /Invoices/Invoices/Products/Product/Product_Name/text()
...