Transforming XML to XML with XSLT - ugly ColdFusion export.

Question

I have a following XML structure that I need to transform:

<recordset rowCount="68" fieldNames="ITEM,ECL,LEAD_TIME" type="**coldfusion.sql.QueryTable**">
<field name="ITEM">
 <string>ITEM_A</string>
 <string>ITEM_B</string>
 <string>ITEM_C</string>
</field>
<field name="REV">
 <string>A</string>
 <string>B</string>
 <string>C</string>
</field>
<field name="LEAD_TIME">
 <string>10</string>
 <string>15</string>
 <string>25</string>
</field>
</recordset>

Into:

<records>
<item_line>
 <item>ITEM_A</item>
 <rev>A</rev>
 <lead_time>10</lead_time>
</item_line>
<item_line>
 <item>ITEM_B</item>
 <rev>B</rev>
 <lead_time>15</lead_time>
</item_line>
<item_line>
 <item>ITEM_C</item>
 <rev>C</rev>
 <lead_time>25</lead_time>
</item_line>
</records>

My knowledge of XSLT is very limited...

Thank you in advance!

Answer 1

<xsl:stylesheet
  version="1.0"
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
>

  <xsl:output method="xml" indent="yes" omit-xml-declaration="yes" />

  <xsl:template match="/recordset">
    <records>
      <xsl:apply-templates select="field[@name='ITEM']/string" />
    </records>
  </xsl:template>

  <xsl:template match="field[@name='ITEM']/string">
    <xsl:variable name="currpos" select="position()" />

    <item_line>
      <item>
        <xsl:value-of select="." />
      </item>
      <rev>
        <xsl:value-of select="/recordset/field[@name='REV']/string[$currpos]" />
      </rev>
      <lead_time>
        <xsl:value-of select="/recordset/field[@name='LEAD_TIME']/string[$currpos]" />
      </lead_time>
    </item_line>
  </xsl:template>

</xsl:stylesheet>

A slightly more readable and probably marginally faster version would be using an <xsl:key>:

<xsl:stylesheet
  version="1.0"
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
>

  <xsl:output method="xml" indent="yes" omit-xml-declaration="yes" />

  <xsl:key name="k_field" match="recordset/field" use="@name" />

  <xsl:template match="/">
    <records>
      <xsl:apply-templates select="key('k_field', 'ITEM')/string" />
    </records>
  </xsl:template>

  <xsl:template match="field[@name='ITEM']/string">
    <xsl:variable name="currpos" select="position()" />

    <item_line>
      <item>
        <xsl:value-of select="." />
      </item>
      <rev>
        <xsl:value-of select="key('k_field', 'REV')/string[$currpos]" />
      </rev>
      <lead_time>
        <xsl:value-of select="key('k_field', 'LEAD_TIME')/string[$currpos]" />
      </lead_time>
    </item_line>
  </xsl:template>

</xsl:stylesheet>

Answer 2

A simple approach:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"&gt;
    <xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>

    <xsl:template match="//recordset">
     <records>
      <xsl:apply-templates select="field[@name = 'ITEM']/string"/>
     </records>
    </xsl:template>

    <xsl:template match="string">
     <xsl:variable name="loc" select="position()"/>
     <item_line>
      <item>
       <xsl:value-of select="."/>
      </item>
      <rev>
       <xsl:value-of select="//recordset/field[@name = 'REV']/string[position() = $loc]"/>
      </rev>
      <lead_time>
       <xsl:value-of select="//recordset/field[@name = 'LEAD_TIME']/string[position() = $loc]"/>
      </lead_time>
     </item_line>
    </xsl:template>

</xsl:stylesheet>

And below is a more complicated xslt than above. It will allow you to further extend revTemplate and leadTimeTemplate without cluttering the string template.

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"&gt;
    <xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>

    <xsl:template match="//recordset">
     <records>
      <xsl:apply-templates select="field[@name = 'ITEM']/string"/>
     </records>
    </xsl:template>

    <xsl:template match="string">  
     <item_line>
      <item>
       <xsl:value-of select="."/>
      </item>
      <xsl:call-template name="revTemplate">
       <xsl:with-param name="loc" select="position()"/>
      </xsl:call-template>
      <xsl:call-template name="leadTimeTemplate">
       <xsl:with-param name="loc" select="position()"/>
      </xsl:call-template>
     </item_line>
    </xsl:template>

    <xsl:template name="revTemplate">
     <xsl:param name="loc"/>
     <rev>
      <xsl:value-of select="//recordset/field[@name = 'REV']/string[position() = $loc]"/>
     </rev>
    </xsl:template>

    <xsl:template name="leadTimeTemplate">
     <xsl:param name="loc"/>
     <rev>
      <xsl:value-of select="//recordset/field[@name = 'LEAD_TIME']/string[position() = $loc]"/>
     </rev>
    </xsl:template>

</xsl:stylesheet>

Answer 3

The following solution works for any field name and any number of fields. The format of your data suggests that the field names might be dynamic. It also doesn't assume that the field children will always be named "string". (The name "string" made me suspicious that other data types might appear. Whether they do or not, this solution will still work.)

<xsl:stylesheet version="1.0"
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"&gt;

  <xsl:output indent="yes"/>

  <xsl:template match="/">
    <records>
      <!-- Just process the first field's children, 
           to get the list of line items -->
      <xsl:apply-templates select="/recordset/field[1]/*"/>
    </records>
  </xsl:template>

  <!-- Convert each field child to a line item -->
  <xsl:template match="field/*">
    <item_line>
      <!-- Then query all the fields for the value at this position -->
      <xsl:apply-templates select="/recordset/field">
        <xsl:with-param name="pos" select="position()"/>
      </xsl:apply-templates>
    </item_line>
  </xsl:template>

  <xsl:template match="field">
    <xsl:param name="pos"/>
    <!-- Convert the field name to lower case -->
    <xsl:element name="{translate(
      @name,
      'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
      'abcdefghijklmnopqrstuvwxyz'
    )}">
      <xsl:value-of select="*[$pos]"/>
    </xsl:element>
  </xsl:template>

</xsl:stylesheet>

Let me know if you have any questions.

---

EDIT: A streamlined version of the above:

<xsl:stylesheet version="1.0"
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"&gt;

  <xsl:output indent="yes"/>

  <xsl:template match="/">
    <records>
      <xsl:for-each select="/recordset/field[1]/*">
        <xsl:variable name="pos" select="position()" />
        <item_line>
          <xsl:apply-templates select="/recordset/field/*[$pos]" />
        </item_line>
      </xsl:for-each>
    </records>
  </xsl:template>

  <xsl:template match="field/*">
    <xsl:element name="{translate(
      ../@name,
      'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
      'abcdefghijklmnopqrstuvwxyz'
    )}">
      <xsl:value-of select="."/>
    </xsl:element>
  </xsl:template>
</xsl:stylesheet>