xslt

XSLT 1.0 Report distinct values from XML

This is a sample of data from my XML table:

<table>
<tr>
<td>LCC/8700087542</td>
<td>010E</td>
<td>LHAO</td>
<td>HWAA</td>
<td>Add</td>
<td>010E</td>
</tr>
<tr>
<td>LCC/8700087487</td>
<td>010E</td>
<td>LHAO</td>
<td>HWAA</td>
<td>Add</td>
<td>010E</td>
</tr>
<tr>
<td>10014752</td>
<td>049E</td>
<td>LHAO</td>
<td>HWAA</td>
<td>Add</td>
<td>049E</td>
</tr>
<tr>
<td>10005064</td>
<td>007E</td>
<td>LHAO</td>
<td>HWAA</td>
<td>Add</td>
<td>007E</td>
</tr>
</table>

I need to use stylesheet to convert it to csv where the unique codes are used in the header section of the file. The codes are in the 6th field, i.e. 010E, 049E, 007E.

Using XSLT 2.0 and for-each-group I was able to produce the output I need:

1,Inspection Route,Record Id,Code,Work Group
2,Inspection Route,010E,010E,HWAA
2,Inspection Route,049E,049E,HWAA
2,Inspection Route,007E,007E,HWAA
1,Inspection Route Feature,Parent Id,Central Asset Id,Next Inspection Due Date,Delete
2,Inspection Route Feature,010E,LCC/8700087542,,N
2,Inspection Route Feature,010E,LCC/8700087487,,N
2,Inspection Route Feature,049E,10014752,,N
2,Inspection Route Feature,007E,10005064,,N

The XSLT I used to produce the output:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0"
                 xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output method='text' version='1.0' encoding='UTF-8'/>
    <xsl:template match="table">
    <xsl:text>1,Inspection Route,Record Id,Code,Work Group</xsl:text>
    <xsl:text>&#10;</xsl:text>
    <xsl:for-each-group select="tr" group-by="td[06]">
        <xsl:text>2,Inspection Route,</xsl:text>
        <xsl:value-of select="current-grouping-key()"/>
        <xsl:text>,</xsl:text>
        <xsl:value-of select="td[06]"/>
        <xsl:text>,</xsl:text>
        <xsl:value-of select="td[04]"/>
        <xsl:text>&#10;</xsl:text>
    </xsl:for-each-group>
    <xsl:text>1,Inspection Route Feature,Parent Id,Central Asset Id,Next Inspection Due Date,Delete</xsl:text>
    <xsl:text>&#10;</xsl:text>
    <xsl:for-each select="tr">
        <xsl:text>2,Inspection Route Feature,</xsl:text>
        <xsl:value-of select="td[06]"/>
        <xsl:text>,</xsl:text>
        <xsl:value-of select="td[01]"/>
        <xsl:text>,</xsl:text>
        <xsl:choose>
            <xsl:when test="td[05]='Add'">
                <xsl:text>,N</xsl:text>
            </xsl:when>
            <xsl:when test="td[03]='Delete'">
                <xsl:text>,Y</xsl:text>
            </xsl:when>
        </xsl:choose>
        <xsl:text>&#10;</xsl:text>
    </xsl:for-each>
    </xsl:template>
</xsl:stylesheet>

I found the software I'm using for the translation only supports XSLT 1.0 so am looking for an alternative solution. The best I've come up with so far is:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output method='text' version='1.0' encoding='UTF-8'/>
  
  <xsl:key name="routeCodeDistinct" match="/table/tr" use="td[06]"/>
  <xsl:template match="table">
  <xsl:for-each select="table[generate-id() = generate-id(key('routeCodeDistinct', td[06])[1])]">
  </xsl:for-each>
      <xsl:text>1,Inspection Route,Record Id,Code,Work Group,&#10;</xsl:text>
          <xsl:for-each select="key('routeCodeDistinct',td[06])">
                <xsl:text>2,Inspection Route,</xsl:text>
                <xsl:text>,</xsl:text>
                <xsl:value-of select="td[06]"/>
                <xsl:text>,</xsl:text>
                <xsl:value-of select="td[04]"/>
                <xsl:text>&#10;</xsl:text>
            </xsl:for-each>

      <xsl:text>1,Inspection Route Feature,Parent Id,Central Asset Id,Next Inspection Due Date,Delete&#10;</xsl:text>
      <xsl:for-each select="tr">
          <xsl:text>2,Inspection Route Feature,</xsl:text>
          <xsl:value-of select="td[06]"/>
          <xsl:text>,</xsl:text>
          <xsl:value-of select="td[01]"/>
          <xsl:text>,</xsl:text>
          <xsl:choose>
              <xsl:when test="td[05]='Add'">
                <xsl:text>,N</xsl:text>
              </xsl:when>
              <xsl:when test="td[03]='Delete'">
                <xsl:text>,Y</xsl:text>
              </xsl:when>
          </xsl:choose>
          <xsl:text>&#10;</xsl:text>
      </xsl:for-each>
    </xsl:template>
</xsl:stylesheet>

but this does not return the distinct code in the header that I need. Can someone please give me a steer as I'm out of ideas!

Solution

  • I don't know why your for-each using the Muenchian grouping is empty, you want an approach like

      <xsl:key name="routeCodeDistinct" match="/table/tr" use="td[6]"/>
  <xsl:template match="table">
    <xsl:text>1,Inspection Route,Record Id,Code,Work Group,&#10;</xsl:text>

    <xsl:for-each select="tr[generate-id() = generate-id(key('routeCodeDistinct', td[6])[1])]">
              <xsl:text>2,Inspection Route,</xsl:text>
              <xsl:text>,</xsl:text>
              <xsl:value-of select="td[6]"/>
              <xsl:text>,</xsl:text>
              <xsl:value-of select="td[4]"/>
              <xsl:text>&#10;</xsl:text>
    </xsl:for-each>


      <xsl:text>1,Inspection Route Feature,Parent Id,Central Asset Id,Next Inspection Due Date,Delete&#10;</xsl:text>
      <xsl:for-each select="tr">
          <xsl:text>2,Inspection Route Feature,</xsl:text>
          <xsl:value-of select="td[06]"/>
          <xsl:text>,</xsl:text>
          <xsl:value-of select="td[01]"/>
          <xsl:text>,</xsl:text>
          <xsl:choose>
              <xsl:when test="td[05]='Add'">
                <xsl:text>,N</xsl:text>
              </xsl:when>
              <xsl:when test="td[03]='Delete'">
                <xsl:text>,Y</xsl:text>
              </xsl:when>
          </xsl:choose>
          <xsl:text>&#10;</xsl:text>
      </xsl:for-each>
    </xsl:template>

    I think an additional <xsl:value-of select="td[6]"/> is missing to substitute the <xsl:value-of select="current-grouping-key()"/> but I guess you will be able to add that.