Xpath Expressions in XML output stage

[shails]

Hi,

I am trying to generate a XML file with the data coming from a database table.

Input columns are status,last_name.

The structure of my XML file should be like this:
<Exchange>
<User>
<Attribute name="status">
<value>Active</value>
</Attribute>
<Attribute name="last_name">
<value>Mandy</value>
</Attribute>
</User>
<User>
<Attribute name="status">
<value>InActive</value>
</Attribute>
<Attribute name="last_name">
<value>Alan</value>
</Attribute>
</User>
</Exchange>

The values in blue are the column names and the values in bold are the values for the respective columns.
There should be one <User> Tag for each row and one <Exchange> Tag for the file.

I used the following stages in my Job:
Oracle Stage ---> Transformer --->Pivot ---> XML Output stage

In the inputs tab of the XML Output stage the XPath expressions are defined as follows:

User --- /Exchange/User
name -- /Exchange/User/Attribute/@name
value -- /Exchange/User/Attribute/value/text()

But when I do this the <User> Tag is getting generated for every column instead it should be generated for every row.

<Exchange>
- <User>
- <Attribute name="status">
<value>Active</value>
</Attribute>
</User>
- <User>
- <Attribute name="last_name">
<value>Mandy</value>
</Attribute>
</User>
- <User>
- <Attribute name="status">
<value>InActive</value>
</Attribute>
</User>
- <User>
- <Attribute name="last_name">
<value>Alan</value>
</Attribute>
</User>
</Exchange>

Can anyone help me understand what is the mistake I am making in the XPath expressions.
Thanks in advance.

[joffijoy]

Hi,

I am trying to generate a XML file with the data coming from a database table.

Input columns are status,last_name.

The structure of my XML file should be like this:
<Exchange>
<User>
<Attribute name="status">
<value>Active</value>
</Attribute>
<Attribute name="last_name">
<value>Mandy</value>
</Attribute>
</User>
<User>
<Attribute name="status">
<value>InActive</value>
</Attribute>
<Attribute name="last_name">
<value>Alan</value>
</Attribute>
</User>
</Exchange>

The values in blue are the column names and the values in bold are the values for the respective columns.
There should be one <User> Tag for each row and one <Exchange> Tag for the file.

I used the following stages in my Job:
Oracle Stage ---> Transformer --->Pivot ---> XML Output stage

In the inputs tab of the XML Output stage the XPath expressions are defined as follows:

User --- /Exchange/User
name -- /Exchange/User/Attribute/@name
value -- /Exchange/User/Attribute/value/text()

But when I do this the <User> Tag is getting generated for every column instead it should be generated for every row.

<Exchange>
- <User>
- <Attribute name="status">
<value>Active</value>
</Attribute>
</User>
- <User>
- <Attribute name="last_name">
<value>Mandy</value>
</Attribute>
</User>
- <User>
- <Attribute name="status">
<value>InActive</value>
</Attribute>
</User>
- <User>
- <Attribute name="last_name">
<value>Alan</value>
</Attribute>
</User>
</Exchange>

Can anyone help me understand what is the mistake I am making in the XPath expressions.
Thanks in advance.

Are you making User as the key and setting it as repitative?

[shails]

I made User as key but still it didnt work.
This is a parallel Job and I could not find the repetitive property in the XML output stage.
Correct me if i am wrong.

[eostic]

Hi...

I've seen this before with poorly designed xml documents. How much nicer it would be if the xml tags were the actual meta data names! (<user><firstName>value</firstName><lastName>value</lastName></user>). Even in it's current form it seems odd, because "attribute" could just as easily be a pure mixed element, as <attribute name="value">value</attribute>, instead of having the extra "value" element...

The problem is then multiplied by the fact that you have individual rows coming in (after the pivot) so "user" is assigned to every row.

It's possible you may be able to come up with a technique to make this functional using XMLOutput, and it's also possible that XMLOutput is not going to be able to do it....

Instead, I would suggest that you build the xml string in a transformer (or part of it, you may be able to get one column to cooperate), and then pass that column into the XMLOutput Stage with Data Element of 'XML', so that it doesn't get re-parsed, along with your other columns and xpath....for the pre-created xml snippet, use just a '/' in the description column.

It will take some trial and error, for sure...but try working on a mixed method -- where you build some of the xml, and xmlOutput builds the rest.

Good luck.

Ernie

[eostic]

...and I'd probably try this without the Pivot...build the hand cooked XML for the "attribute w/column name" part, and then try letting XMLOutput correctly wrap the "user" element on a true row by row basis, and of course build the rest of the xml for you.

-e

[shails]

Hi Ernie,

I tried using the following string in the transformer:

'<Attribute name=':'"status"':'><value>': DSLink2.STATUS :'</value></Attribute>':'<Attribute name=':'"last_name"':'> <value>': DSLink2.LAST_NAME:'</value></Attribute>'

and passed the value to a XML Output stage with the description as /User and data element set to XML but still it doesnt help. I get the following error when I view the xml file in the browser

Only one top level element is allowed in an XML document. Error processing resource 'file:///C:/Documents and Settings/Yeru...

<User></User>

But If I put the description as / for the column I am getting the foll error:
Derivation rule "/" is invalid. Message = "Element or attribute expected at "/""

Any idea on this error. Thanks

[eostic]

For problems unrelated to this I can't get into DS to try a theory I have right now, but if you are getting that error when you use your browser, it sounds like /user is at the wrong level and being duplicated (open it in notepad or other editor instead to let us know what it really looks like). In the meantime in your example it would be interesting to know what happens if you use "/Exchange/User" (w/o the quotes of course).

Ernie