CODEWITHSUNDEEP
xmlxslt
user3303297
user3303297

Reputation: 13

Create A Table w/ XSLT 1.0 & Concatenate Multiple Values

So, I need to create a dynamic table using xslt 1.0. I've looked at several articles/posts but I can't seem to put it all together. In many instances, there will be several table cells with empty data. In other instances, there will be multiple values that need to concatenated into one cell. Here is sample XML:

<Document>
<Records>
  <User>
    <Name>User1</Name>
    <list xid="data.set">
      <Data>
        <Column>2</Column>
        <Type>Carbs</Type>
        <RowValue>Oatmeal</RowValue>
      </Data>
      <Data>
        <Column>1</Column>
        <Type>Protein</Type>
        <RowValue>sausage</RowValue>
        <RowValue>eggs</RowValue>
        <RowValue>turkey</RowValue>
      </Data>
    </list>
  </User>
  <User>
    <Name>User2</Name>
    <list xid="data.set">
      <Data>
        <Type>Vegetables</Type>
        <Column>8</Column>
        <RowValue>Squash</RowValue>
      </Data>
      <Data>
        <Column>3</Column>
        <Type>Sweets</Type>
        <RowValue>cake</RowValue>
        <RowValue>cookies</RowValue>
      </Data>
      <Data>
        <Column>5</Column>
        <Type>Other</Type>
      </Data>
      <Data>
        <Column>6</Column>
        <Type>Beverage</Type>
        <RowValue>grape juice</RowValue>
      </Data>
    </list>
    </User>
  <User>
    <Name>User4</Name>
    <list xid="data.set">
        <Data>
          <Column>7</Column>
          <Type>Fats</Type>
          <RowValue>cashews</RowValue>
        </Data>
        <Data>
          <Column>8</Column>
          <Type>Vegetables</Type>
          <RowValue>Green Beans</RowValue>
        </Data>
        <Data>
          <Column>2</Column>
          <Type>Carbs</Type>
          <RowValue>Brown Rice</RowValue>
        </Data>
      </list>
    </User>
    <User>
      <Name>User5</Name>
      <list xid="data.set">
        <Data>
          <Column>3</Column>
          <Type>Sweets</Type>
          <RowValue>gummy worms</RowValue>
        </Data>
        <Data>
          <Column>4</Column>
          <Type>Fruit</Type>
          <RowValue>apples</RowValue>
        </Data>
      </list>
    </User>
    <User>
      <Name>User5</Name>
      <list xid="data.set">
        <Data>
          <Column>3</Column>
          <Type>Sweets</Type>
          <RowValue>gummy worms</RowValue>
        </Data>
        <Data>
          <Column>4</Column>
          <Type>Fruit</Type>
          <RowValue>grapes</RowValue>
        </Data>
      </list>
    </User>
    <User>
      <Name>User5</Name>
      <list xid="data.set">
        <Data>
          <Column>3</Column>
          <Type>Sweets</Type>
          <RowValue>gummy worms</RowValue>
        </Data>
        <Data>
          <Column>4</Column>
          <Type>Fruit</Type>
          <RowValue>grapes</RowValue>
        </Data>
      </list>
    </User>
</Records>
</Document>

EDIT : In HTML output there are duplicate values in table cells when the username exists more than once (see below). I would like to remove the duplicate values present in each cell such as "gummy worms, gummy worms, gummy worms." For example, there are three "users5" listed. I would like to keep each row containing "user5" but remove the duplicate values in each of the cells for those row.

<table border="1">
<thead>
<tr>
<th>
</th>
<th>Protein</th>
<th>Carbs</th>
<th>Sweets</th>
<th>Fruit</th>
<th>Other</th>
<th>Beverage</th>
<th>Fats</th>
<th>Vegetables</th>
</tr>
<tr>
<th></th>
<th>1</th>
<th>2</th>
<th>3</th>
<th>4</th>
<th>5</th>
<th>6</th>
<th>7</th>
<th>8</th>
</tr></thead><tbody><tr><th>User1</th>
<td>sausage, eggs, turkey</td>
<td>Oatmeal</td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
<tr><th>User2</th>
<td></td>
<td></td>
<td>cake, cookies</td>
<td></td>
<td></td>
<td>grape juice</td>
<td></td>
<td>Squash</td>
</tr>
<tr><th>User4</th>
<td></td>
<td>Brown Rice</td>
<td></td>
<td></td>
<td></td>
<td></td>
<td>cashews</td>
<td>Green Beans</td>
</tr>
<tr><th>User5</th>
<td></td>
<td></td>
<td>gummy worms, gummy worms, gummy worms</td>
<td>apples, grapes, grapes</td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
<tr><th>User5</th>
<td></td>
<td></td>
<td>gummy worms, gummy worms, gummy worms</td>
<td>apples, grapes, grapes</td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
<tr><th>User5</th>
<td></td>
<td></td>
<td>gummy worms, gummy worms, gummy worms</td>
<td>apples, grapes, grapes</td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
</tbody>
</table>

Here is my VERY feeble attempt at trying to make this work. I'm a newbie, gentle please...

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl">


  <xsl:key name="food-by-Category" match="Data" use="Column" />
 <xsl:key name="food-by-Value" match="Columns" use="RowValue" />

  <xsl:template match="/Document/Records/User/list[@xid = 'data.set']">

 <table><tr>
    <xsl:for-each select="Data[count(. | key('food-by-Category', Column)[1]) = 1]">
      <!-- Sort by the Category -->
      <xsl:sort select="Column" />
 <td>
      <xsl:value-of select="Type" />
</td>
</xsl:for-each>
</tr>

  <xsl:for-each select="Data[count(. | key('food-by-Category', Column)[1]) = 1]">
 <tr>
      <xsl:for-each select="key('food-by-Category', Column)">
         <!-- Sort by the item Value -->
        <xsl:sort select="RowValue" />
<td>
        <xsl:value-of select="RowValue" />
</td>      
    </xsl:for-each>
 </tr>
</xsl:for-each>
</table>
  </xsl:template>
</xsl:stylesheet>

As you can see it's not grouping the table headers/cells together or concatenating the values when there are multiple < RowValue >.

Upvotes: 1

Views: 1935

Answers (2)

helderdarocha
helderdarocha

Reputation: 23637

I did it using recursive templates. I just didn't sort the data, and the number of columns is hardwired. You can improve it adding a key with all the columns and selecting the largest number, or setting it through a variable or parameter.

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl">

    <xsl:output method="html" indent="yes"/>

    <xsl:key name="food-by-Category" match="Data" use="Type" />

    <!-- Match the Records tree, set the table elements and first row -->
    <xsl:template match="Records">
        <table width="800" border="1">
            <tbody>
                <tr>
                    <td>Name</td>
                    <xsl:for-each select="User/list/Data[count(. | key('food-by-Category', Type)[1]) = 1]">
                        <td><xsl:value-of select="Type"/></td>
                    </xsl:for-each>
                </tr>
                <!-- Apply the templates for each User subtree -->
                <xsl:apply-templates select="User" />
            </tbody>
        </table>
    </xsl:template>

    <!-- Set up one row for each User, get User name -->
    <xsl:template match="User">
        <tr>
            <td><xsl:value-of select="Name"/></td>
            <!-- Apply the templates for the list subtree -->
            <xsl:apply-templates select="list" />
        </tr>
    </xsl:template>

    <!-- This is a recursive template used to generate the columns 
         It's hardwired to generate 8 columns -->
    <xsl:template name="empty-tds">
        <xsl:param name="columns" />
        <xsl:param name="i" />
        <xsl:if test="$i &lt;= 8">
            <!-- Fills in each column with RowValues -->
            <td><xsl:apply-templates select="Data[Column = $i]/RowValue" /></td>
            <xsl:call-template name="empty-tds">
                <xsl:with-param name="i" select="$i + 1" />
            </xsl:call-template>
        </xsl:if>
    </xsl:template>

    <!-- This calls the template above and generates columns -->
    <xsl:template match="list">
        <xsl:call-template name="empty-tds">
            <xsl:with-param name="i" select="1" />
        </xsl:call-template>
    </xsl:template>

    <!-- This one concatenates multiple RowValues within Data -->
    <xsl:template match="RowValue">
        <xsl:value-of select="."/>
        <xsl:if test="position() != last()">
            <xsl:text>, </xsl:text>
        </xsl:if>
    </xsl:template>

</xsl:stylesheet>

The last User (#6) isn't printed because in the source it was placed inside the list.

Upvotes: 0

michael.hor257k
michael.hor257k

Reputation: 116993

Wow. I think you have at least three different questions' worth in there:

  1. how to produce the unique column headers;
  2. how to get data from a sparse array into a table;
  3. how to concatenate multiple matching data items into one cell.

If that wasn't complicated enough, you made sure it would be by mangling the last user's data (making <Name> a child of <list> instead of a sibling).

Anyway, have a look at the following stylesheet:

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

<xsl:key name="data_by_column" match="Data" use="Column" />
<xsl:key name="value_by_cell" match="RowValue" use="concat(ancestor::User/Name, '|', preceding-sibling::Column)" />

<xsl:template match="/">
<table border="1">
<thead>
    <tr>
        <th/>
        <!--unique column headers -->
        <xsl:for-each select="Document/Records/User/list/Data[count(. | key('data_by_column', Column)[1]) = 1]">
        <xsl:sort select="Column" data-type="number" order="ascending"/>
        <th><xsl:value-of select="Type"/></th>
        </xsl:for-each>
    </tr>
    <tr>
        <th/>
        <!--unique column headers -->
        <xsl:for-each select="Document/Records/User/list/Data[count(. | key('data_by_column', Column)[1]) = 1]">
        <xsl:sort select="Column" data-type="number" order="ascending"/>
        <th><xsl:value-of select="Column"/></th>
        </xsl:for-each>
    </tr>
</thead>
<tbody>
    <xsl:for-each select="Document/Records/User">
    <xsl:variable name="row" select="Name" />
    <tr>
        <th><xsl:value-of select="$row"/></th>
        <!-- for each unique column header -->
        <xsl:for-each select="/Document/Records/User/list/Data[count(. | key('data_by_column', Column)[1]) = 1]">
        <xsl:sort select="Column" data-type="number" order="ascending"/>
        <xsl:variable name="col" select="Column" />
        <!-- new cell -->
        <td>
            <!-- get matching data -->
            <xsl:for-each select="key('value_by_cell', concat($row, '|', $col))">
                <xsl:value-of select="."/>
                <xsl:if test="position()!=last()">
                    <xsl:text>, </xsl:text>
                </xsl:if>
            </xsl:for-each>     
        </td>
        </xsl:for-each>
    </tr>
    </xsl:for-each>
</tbody>
</table>
</xsl:template>
</xsl:stylesheet>

Note: I could have probably eliminated repeating the same code for unique column headers three times by dumping it into a variable first, but I ran out of time.

Upvotes: 2

Related Questions