DSXchange

Hi All,

I have a source file like
Metadata--custno,add1,add2,city,add1,add2,city,amount
Data file---11111,abcd,defg,xyz,lkmn,abcd,pune,1000.

I want output as
<Cust>
<Customerno> 11111 </Customerno>
<add1>abcd</add1>
<add2>defg</add2>
<city>xyz</city>
<add1>lkmn</add1>
<add2>abcd</add2>
<city>pune</city>
<amount>1000</amount>
</Cust>

I have tried with every options(i.e by taking each and every column as primary key and using trigger column) but i am not getting output in required format.

I am taking Xpath Expression as
/Cust/customerID/text()
/Cust/customerID/add1/text()
/Cust/customerID/add2/text()
/Cust/amount/text()
/Cust/customerID/add1/text()

but i am not getting write output.

I have gone through XML best practice document too.

Please help.

Thanks
Murari

What are you getting?

Hi,

I am getting output as
<Cust>
<Customerno> 11111 </Customerno>
<add1>abcd</add1>
<add2>defg </add2>
<city>xyz, lkmn, abcd, pune</city>
<amount>1000</amount>
</Cust>

Means it is not taking second record as new line.

Thanks
Murari

You may need to group those together, as they probably should be in their xsd..... for the sake of testing, try wrapping those three elements in a larger element, such as "detail":

.../detail/add1/text()
.../detail/add2/text()
.../detail/city/text()

Make one of them the repeating element and use Aggregate as your option.

...if that works, it tells us a bit more about your structure and rows, and then also we can look at possible ways to remove it afterwards.

Ernie

Hi All,

I am getting same ouput only after including one more parent
i.e
/Cust/add/add1/text()

then also i am not getting right answer.

Please help me.

I am getting output as
<Cust>
<Customerno> 11111 </Customerno>
<add>
<add1>abcd</add1>
<add2>defg </add2>
<city>xyz, lkmn, abcd, pune</city>
</add>
</Cust>

Hi All,

Please reply to this question.

Thanks in Advance

I think I see the issue. Sorry I didn't notice it before. In order to get DataStage XMLOutput to treat these as separate elements (whether in a greater parent or not, which you still may need to do), you need to get them truly into two rows. I failed to notice first time around that the columns are all in one row. Pivot the data first so that you have two lines of address information, each line (row) having the common customer key value. Then "aggregation" makese sense, and it can group the two addresses into a larger "addresses" element "under" the customer.

There are lots of entries in the forum here on pivot, but before you try that, set up a test file that just has two rows in it, each with a single add1, add2, city set of columns.

Ernie

Hi bikan,

Same type of requirement I handled like mentioned below with server job by using XML Writer stage in Datastage 7.5. Before this we tried with the Parallel job and the output also generated but the format of the output file is not proper. I mean the open tag and closed tag's are not generated in single line if we open the file in internat explorer. Because of this reason we handled this with server job by using XML Writer stage.

My output is:
<List>
<AsOf>value</AsOf>
<fund>
<fundID>value</fundID>
<share>
<class>value</class>
<Code>value</Code>
<Name>value</Name>
<IDate>Date</IDate>
<Symbol>value</Symbol>
<nav>
<List>

Logic:
/List/AsOf
/List/fund/fundID
/List/fund/share/class
/List/fund/share/Code
/List/fund/share/Name
/List/fund/share/IDate
/List/fund/share/Symbol
/List/fund/share/nav

bikan wrote:Hi All,

I have a source file like
Metadata--custno,add1,add2,city,add1,add2,city,amount
Data file---11111,abcd,defg,xyz,lkmn,abcd,pune,1000.

I want output as
<Cust>
<Customerno> 11111 </Customerno>
<add1>abcd</add1>
<add2>defg</add2>
<city>xyz</city>
<add1>lkmn</add1>
<add2>abcd</add2>
<city>pune</city>
<amount>1000</amount>
</Cust>

I have tried with every options(i.e by taking each and every column as primary key and using trigger column) but i am not getting output in required format.

I am taking Xpath Expression as
/Cust/customerID/text()
/Cust/customerID/add1/text()
/Cust/customerID/add2/text()
/Cust/amount/text()
/Cust/customerID/add1/text()

but i am not getting write output.

I have gone through XML best practice document too.

Please help.

Thanks
Murari

I have observerd that file formatting functions are better enabled in server jobs than in parallel jobs

Regards
Sreeni