User-defined qsl query file

[netboyks]

Has any one used this option in calling an anonymous pl/sql block in an OCI stage.
Thanks.

[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.

[roy]

Hi,
use the search here,
If my memory servs me it was already coverd.
Good Luck,