XSLT: Breaking CSV from one source XML node into multiple target XML nodes.

Question

Hi,

Thanks to help of fellow SO users that helped me out in this question I am half way through my XML transformation.

The problem that I hit a wall with transforming comma seperated values (DATE and QTY):

<?xml version="1.0" encoding="utf-8"?>
<results>
<recordset rowCount="68" fieldNames="ITEM,ECL,LEAD_TIME,QTY,DATE" 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>

<field name="QTY">
<string>10,13,3</string>
<string>1,5,2</string>
<string>6,10,25</string>
</field>

</recordset>
<var name="DATE_LABELS">
<string>05-18,05-25,06-01</string>
</var>
</results>

Into:

<records>
<item_line>
 <item>ITEM_A</item>
 <rev>A</rev>
 <lead_time>10</lead_time>
 <values>
    <qty date="05-18>10</qty>
    <qty date="05-25>13</qty>
    <qty date="06-01>3</qty>
 </values>
</item_line>
<item_line>
 <item>ITEM_B</item>
 <rev>B</rev>
 <lead_time>15</lead_time>
 <values>
    <qty date="05-18>1</qty>
    <qty date="05-25>5</qty>
    <qty date="06-01>2</qty>
 </values>
</item_line>
<item_line>
 <item>ITEM_C</item>
 <rev>C</rev>
 <lead_time>25</lead_time>
 <values>
    <qty date="05-18>6</qty>
    <qty date="05-25>10</qty>
    <qty date="06-01>25</qty>
 </values>
</item_line>
</records>

Is it at all possible to express it with XSLT?

I am starting to hate the guy that "designed" the source xml schema...

UPDATE: Thanks for your help!

Answer 1

Maybe you can ask this piece of cake in one question next time, so we don't have to do things twice, but here we go!

<?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="//var" />

  <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>
      <values>
        <xsl:call-template name="split-csv">
          <xsl:with-param name="list">
            <xsl:value-of select="//recordset/field[@name = 'QTY']/string[position() = $loc]" />
          </xsl:with-param>
          <xsl:with-param name="labels">
            <xsl:value-of select="//var[@name = 'DATE_LABELS']/string" />
          </xsl:with-param>
        </xsl:call-template>
      </values>
    </item_line>
  </xsl:template>

  <xsl:template name="split-csv">
    <xsl:param name="list" />
    <xsl:param name="labels" />
    <xsl:variable name="first" select="substring-before($list, ',')" />
    <xsl:variable name="remaining" select="substring-after($list, ',')" />
    <xsl:variable name="label" select="substring-before($labels, ',')" />
    <qty>
      <xsl:choose>
        <xsl:when test="not($first) and $list">
          <xsl:attribute name="date">
            <xsl:value-of select="$labels"/>
          </xsl:attribute>
          <xsl:value-of select="$list" />
        </xsl:when>
        <xsl:otherwise>
          <xsl:attribute name="date">
            <xsl:value-of select="$label"/>
          </xsl:attribute>
          <xsl:value-of select="$first" />
        </xsl:otherwise>
      </xsl:choose>
    </qty>
    <xsl:if test="$remaining">
      <xsl:call-template name="split-csv">
        <xsl:with-param name="list" select="$remaining" />
        <xsl:with-param name="labels" select="substring-after($labels, ',')" />
      </xsl:call-template>
    </xsl:if>
  </xsl:template>
</xsl:stylesheet>

Answer 2

I've updated the solution I provided to your other question:

<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="/results/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="/results/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:template match="field[@name = 'QTY']">
                    <xsl:param name="pos"/>
                    <values>
                      <xsl:apply-templates mode="qty" select="*[$pos]"/>
                    </values>
                  </xsl:template>

  <xsl:template mode="qty" match="field/*">
    <!-- ASSUMPTION: date-list and value-list will have same number of commas -->
    <xsl:param name="date-list" select="/results/var[@name = 'DATE_LABELS']/string"/>
    <xsl:param name="value-list" select="."/>
    <!-- Extract the date -->
    <xsl:variable name="date">
      <xsl:call-template name="next-item">
        <xsl:with-param name="csv" select="$date-list"/>
      </xsl:call-template>
    </xsl:variable>
    <!-- Extract the value -->
    <xsl:variable name="value">
      <xsl:call-template name="next-item">
        <xsl:with-param name="csv" select="$value-list"/>
      </xsl:call-template>
    </xsl:variable>
    <!-- Output the element -->
    <qty date="{$date}">
      <xsl:value-of select="$value"/>
    </qty>
    <!-- Keep processing if there are more left -->
    <xsl:if test="contains($date-list,',')">
      <xsl:apply-templates mode="qty" select=".">
        <xsl:with-param name="date-list"  select="substring-after($date-list,  ',')"/>
        <xsl:with-param name="value-list" select="substring-after($value-list, ',')"/>
      </xsl:apply-templates>
    </xsl:if>
  </xsl:template>

          <xsl:template name="next-item">
            <xsl:param name="csv"/>
            <xsl:choose>
              <xsl:when test="contains($csv, ',')">
                <xsl:value-of select="substring-before($csv, ',')"/>
              </xsl:when>
              <xsl:otherwise>
                <xsl:value-of select="$csv"/>
              </xsl:otherwise>
            </xsl:choose>
          </xsl:template>

</xsl:stylesheet>

Let me know if you have any questions.