XML format on input

[jatayl]

Dealing with xml, I've found that there can be attributes and elements inside a well-formed xml file.

Well, this is what I came across. Another application wanted to send me an xml file in the following format:

<Rows>
<Row Field='ColumnName1'>10</Row>
<Row Field='ColumnName2'>Bob</Row>
<Row Field='ColumnName3'>7/4/1776</Row>
</Rows>

This is just a sample of one repetitive record within a table, where ColumnName1 is the column name and 10 is the data within that column.

We can read it using the xml stage and handle this, but is the only way inside of DataStage to handle this is to basically pivot the data?

Anyone else seen this type of formatted xml and found another solution? My solution: I'm going back to the source and requesting the following:
<Rows>
<ColumnName1>10</ColumnName1>
<ColumnName2>Bob</ColumnName2>
<ColumnName3>7/4/1776</ColumnName3>
</Rows>

Thanks for your response.

Jason

[lstsaur]

To handle the XML file like that, all you have to do is to write a custom stylesheet instead of using generated XSLT stylesheet.

[eostic]

Yes, a pivot is needed, either "conceptually" by doing it via custom XSLT and placing the data into the columns you want, or via DataStage capabilities to do the same after you have retrived the columns as multiple rows.

...but major kudos to you for asking them for an xml document with a better design.

Ernie

[jatayl]

Could one of you elaborate a little more about using custom stylesheets with this sort of input file:

<value name="SEGMENT">1</value>

where SEGMENT is the column name and 1 is the value of that column?

Thanks,
Jason

[lstsaur]

OK. I am assuming that you know how to get the generated style sheet from the job. Then, modify the generated style sheet at value element as follows:
<xsl:template match="value">
<xsl:value-of select="."/>
<xsl:apply-templates select="@name"/>
</xsl:template>
<xsl:template match="@name">
<xsl:value-of select="."/>
</xsl:template>

The second "value-of-select" will get you the value of '1'. The first one will get you the value of "SEGMENT" if you also want it.

Hope this helps you on how to write your own XSLT style sheet.

[jatayl]

We have a solution:

Based on the input, the xpaths should be defined as
/rows/row[@field="ColumnName1"]/text()
/rows/row[@field="ColumnName2"]/text()
/rows/row[@field="ColumnName3"]/text()

on the Output tab in the xml Input

Thanks,
Jason