XML Single Line

dstage2006 · Post by **dstage2006** » Thu Jul 23, 2009 2:18 pm

Hi All,

I am trying to generate xml with a aggreate mode and i want all the columns in a single row.

ex:
col1 col2 col3
1 2 3
4 5 6

output:

Record1: <col1></col1><col2></col2><col3></col3>
record2 : <col1></col1><col2></col2><col3></col3>

whole xml need to be created in a single line per reocrd .

options that i opted:

aggregate all rows, uncheck the formatting.

i think its not able to recognise the second row. I am able to produce only first record.

Thanks for your suggestions

ray.wurlod · Post by **ray.wurlod** » Thu Jul 23, 2009 5:13 pm

Why? XML is, by definition, free format.

There are XML formatting tools out there. Use one of those if you must. Formatting XML is not an ETL task.

chulett · Post by **chulett** » Thu Jul 23, 2009 5:54 pm

... and what you get without any DataStage 'formatting' is one long record, which is what any parser is actually looking for. Formatting ('pretty printing') is for for peoples.

eostic · Post by **eostic** » Sun Jul 26, 2009 11:53 am

Is it pretty printing that you are looking for? ...or a truly single document? ...if it's pretty printing, follow the advice above...you shouldn't be needing to worry about it, as noted.

....but if it's a "single" xml doc, it might be an issue in the way you are specifying things....

...first, are you using the XMLOutput stage to do the writing to disk? For a test, don't --- use a single link for output, and put a single column on it....such as "myXMLContent" with a varchar and long length as formats. Put a single slash in the Description property and send it to a sequential stage.

....how many "rows" go down this link? If you are getting "2" rows then your issue can be managed...if you are getting "1" row, then it's probably just a pretty print issue that you can and should forget about.

Ernie

dstage2006 · Post by **dstage2006** » Mon Jul 27, 2009 3:52 pm

No i am not looking for a pretty printing this is what the requirement is. Need a single row per record.

Ernie--I did paased to sequential file with '/' as a description and (col1:Col2:Col3) in the Derivation part in that single column. I passed around 8 records and i got eight of them passed out to the sequential file..

As another test i added up a record with a null " " in the new column derivation and used unix new line, passed it to XML output still it shows the same. one single record with the aggreate option.

Thanks in advance for your suggestions.

eostic · Post by **eostic** » Mon Jul 27, 2009 4:55 pm

Make sure you choose aggregate and not trigger, and be sure that you have a summary style node structure in your xpath....

Something like:

for a link that has 3 columns: col1, col2, col3.... have xpath (the Description attribute) that has:

/myRows/myRow/col1/text()
/myRows/myRow/col2/text()
/myRows/myRow/col3/text()

That should do it. If you don't want the wrapping element(s), pass the string to a transformer afterwards and remove them there.

Ernie

dstage2006 · Post by **dstage2006** » Mon Jul 27, 2009 7:08 pm

my assumption is to do it in this way

first transformer concatinate all together and read them in all one column

Col_all='<head>':'<Child1>'Col1'</Child1>':'<Child2>'Col2'</Child2>':'<child3>'Col3'</Child>':'</Head>'

from there in a second transformer

description: /myrows/myrow/Col_all

Then pass it to XML Output as a single column.

Please correct me if i am wrong.

Thanks for your valuable suggestion

dstage2006 · Post by **dstage2006** » Tue Jul 28, 2009 11:32 am

Ernie--i am unable to follow you. Can you please explain it more breifly.

Thanks..

dstage2006 · Post by **dstage2006** » Tue Jul 28, 2009 5:10 pm

I am able to get it in the sequential file but not in the xml output..what could be the reason can any one please give me some suggestions on it..

Thanks for your suggestions.

chulett · Post by **chulett** » Tue Jul 28, 2009 6:43 pm

dstage2006 wrote:I am able to get it in the sequential file but not in the xml output.

First suggestion would be to explain this statement, I for one am unclear on what it is you are trying to say.

eostic · Post by **eostic** » Tue Jul 28, 2009 10:06 pm

I'm not sure I can be any more explicit than what I have up above...

First, I am assuming that you have (in the example) two rows coming into the XMLOutput Stage....and you want "one row" going out.

So...for example, a flat file with two rows, or a relational table with two rows and three columns....col1 col2 and col3....

When finished, you should have a single "row" with a big chunk of xml (a whole document) with:

<myRows><myRow> ...col1...col2...col3...etc...</myRow><myRow><col1>....etc......</myRow></myRows>

Ernie

dstage2006 · Post by **dstage2006** » Wed Jul 29, 2009 6:09 am

Ernie: You are absolutely correct. I am passing two rows and expecting a big chunk of xml in a single row.

My XPath descriptions are

/myxml/myrow/col1/text()
/myxml/myrow/col2/text()
/myxml/myrow/col3/text()

Xml Out put:

- <myxml>
<myrow>
<col1>131</col1>
<col1>121</col1>
<col2>Name</col2>
<col3>City</col3>
</myrow>
</myxml>

Where as in sequential file :
131NameCity
121NameCity

I have selected aggregate all rows option in XML output stage. any other selections that i have to make.

Thanks.

eostic · Post by **eostic** » Wed Jul 29, 2009 2:40 pm

hmmm.... it looks like you have the xpath and everything correct, so it might just be an oddity in the values. There are still ways to skin this cat, but let's prove it first. What happens when the values coming in are 100% unique? (ie...where Name and City are each truly unique strings in each row).

Ernie

eostic · Post by **eostic** » Wed Jul 29, 2009 2:43 pm

PS....I saw this once a long time ago when I was building SOAP bodies for input into WSTransformer.... on a whim, also do a test where you add about 4 or 5 more columns (just create some dummies in a prior Transformer and give them the xpath that uses the same pattern as above). I can't remember the exact pattern, but if it's something like I ran into then, the magic number was either 2, 3, or 4 columns. The behavior went away with a larger number of cols.

This may not have anything to do with that, but I wanted to share this just in case.

Ernie

dstage2006 · Post by **dstage2006** » Sat Aug 01, 2009 5:48 pm

As i said before i passed all these records from XML OUTPUT to File.. and i observed all the records are passing through the file but they are ending with different tags

ex:
<head> <child1><child2>
<head> <child1><child2><hea
<head> <child1><child2></head> <head> <chil1

ernie: I am passing one of the value as a primary key and passing unique key values.

Thanks in advance for all your suggestions.