on 08-22-2006 10:11 AM
Hi everybody!
I have an interesting problem - and so far, I haven't been able to
solve it. Maybe somebody else has an idea...
I have a source xml structure that comes from a legacy system. It is
somewhat similar to a database structure:
<data>
<fieldnames>
<name>Column 1</name>
<name>Column 2</name>
...
</fieldnames>
<rows>
<row id="1">
<value>Value 1</value>
<value>Value 2</value>
...
</row>
<row id="2">
....
</row>
</rows>
</data>
This should be tranformed to a structure much like this:
<data>
<record id="1">
<col1>Value 1</col1>
<col2>Value 2</col2>
...
</record>
<record id="2">
...
</record>
</data>
All of this wouldn't be too hard if I could hard-code the respective
column indices, i.e. if 'Column 1' was alwas at position 1 etc. But
unfortunately that is not always the case - the columns can 'switch
places' - and therefore I cannot select <value> tags according to their
'position()'.
I am at a loss here - any ideas?
Thanks,
Joerg
Hi Joerg,
plz explain your task:
We dont find a rule for fieldnames, 'Column 1', there is no information, what to do with it and it does not appear in the target!?
Regards,
Udo
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
Let me explain: The <fieldnames> structure in the source XML encapsulates a sort of database schema, i.e. each <field> element represents the name of a database column. In the <datagram> section I have multiple lines which correspond to lines in the database. The <value> elements correspond to database fields. The sequence of fields is the same as in the db schema and in the <fieldnames> section. However, the ordering can (and does) change.
The mapping should result in multiple rows. Each 'anonymous' field of the source (i. e. <value> Element) should be mapped to a 'named' field in the destination:
<data>
<fieldnames>
<field>STATE</field>
<field>REFNR</field>
<field>XRFNR</field>
</fieldnames>
<rows>
<row key="1">
<value>value11</value>
<value>value12</value>
<value>value13</value>
</row>
<row key="2">
<value>value21</value>
<value>value22</value>
<value>value23</value>
</row>
...
</rows>
</data>
This should be mapped to :
<rows>
<row key="1">
<STATE>value11</STATE>
<REFNR>value12</REFNR>
<XRFNR>value13</XRFNR>
</row>
<row key="2">
<STATE>value21</STATE>
<REFNR>value22</REFNR>
<XRFNR>value23</XRFNR>
</row>
...
</rows>
Hope this makes it somewhat clearer...
Thanks in advance,
Joerg
Hi Joerg,
check out:
[code]<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
<rows>
<xsl:for-each select="//row">
<row>
<xsl:attribute name="key"><xsl:value-of select="@key"/></xsl:attribute>
<xsl:for-each select="./*">
<xsl:variable name="POS">
<xsl:value-of select="position()"/>
</xsl:variable>
<xsl:variable name="REF">
<xsl:value-of select="//fieldnames/field[position()=$POS]"/>
</xsl:variable>
<xsl:text disable-output-escaping="yes"><![CDATA[<]]></xsl:text>
<xsl:value-of select="$REF"/>
<xsl:text disable-output-escaping="yes"><![CDATA[>]]></xsl:text>
<xsl:value-of select="."/>
<xsl:text disable-output-escaping="yes"><![CDATA[</]]></xsl:text>
<xsl:value-of select="$REF"/>
<xsl:text disable-output-escaping="yes"><![CDATA[>]]></xsl:text>
</xsl:for-each>
</row>
</xsl:for-each>
</rows>
</xsl:template>
</xsl:stylesheet>
[/code]
Regards,
Udo
Hi,
Assuming your target structure is fixed..
<rows>
<row key="1">
<STATE>value11</STATE>
<REFNR>value12</REFNR>
<XRFNR>value13</XRFNR>
</row>
<row key="2">
<STATE>value21</STATE>
<REFNR>value22</REFNR>
<XRFNR>value23</XRFNR>
</row>
...
</rows>
Map <row> to target <row>.
Then try this write a UDF which will take in 3 parameters
1. Constant (Constant for sending the Field Name)
2. <field> as queue
3. <value> as queue
Loop thru the field and find out the <b>Index</b> of the where <b>field<i> = Constant</b> that you have passed.
Then you can find out the corresponding value of the field by <b>value[index].</b>
Note: if you are mapping for <b>STATE</b> the assign "<b>STATE</b>" to the <b>constant</b>.
Hope this helps.
Thanks,
Prakash
Hi Joerg,
two approaches:
1) Did you try the function <i>sort/sortbykey</i>?
2) Use a UDF which sorts your data. To get all the data in your UDF set the context the the upper node.
Regards Mario
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
83 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.