How to transpose XSLT from Rows Into Elements

Question

I am seeking advice on the corrext XSLT method to transpose XML output from rows to there correct logical elements.

As an example i have the following output

<?xml version="1.0" encoding="utf-8"?>
<Report>
    <Field>GetMembershipDetails</Field>
    <Row>
        <Item>MembershipID</Item>
        <Item>MemberNumber</Item>
        <Item>MembershipStatus</Item>
        <Item>BenefitStartDate</Item>
        <Item>BenefitEndDate</Item>
        <Item>MembershipStartDate</Item>
        <Item>MembershipEndDate</Item>
        <Item>MembershpResignationDate</Item>
        <Item>MembershipAnniversaryDate</Item>
        <Item>MembershipType</Item>
        <Item>ContactId</Item>
        <Item>ContactTitle</Item>
        <Item>ContactFirstName</Item>
        <Item>ContactLastName</Item>
        <Item>ContactGender</Item>
        <Item>ContactDateOfBirth</Item>
        <Item>ContactEmployeeNumber</Item>
        <Item>ContactEmployeDateJoined</Item>
        <Item>ContactCorporateAccountNo</Item>
        <Item>ContactEmailAddress</Item>
        <Item>ContactPostalStreet</Item>
        <Item>ContactPostalCity</Item>
        <Item>ContactPostalState</Item>
        <Item>ContactPostalPostCode</Item>
        <Item>ContactResidentialStreet</Item>
        <Item>ContactResidentialState</Item>
        <Item>ContactResidentialCity</Item>
        <Item>ContactResidentialPostCode</Item>
        <Item>ContactResidentialCountry</Item>
        <Item>ContactType</Item>
        <Item>ProductName</Item>
        <Item>ProductDescription</Item>
        <Item>ProductType</Item>
        <Item>ProductAmount</Item>
        <Item>MembershipProductID</Item>
    </Row>
    <Row>
        <Item>8</Item>
        <Item>A2000</Item>
        <Item>Current</Item>
        <Item>No Value</Item>
        <Item>No Value</Item>
        <Item>No Value</Item>
        <Item>No Value</Item>
        <Item>No Value</Item>
        <Item>No Value</Item>
        <Item>Current</Item>
        <Item>109</Item>
        <Item>Mr</Item>
        <Item>John</Item>
        <Item>Foley</Item>
        <Item>Male</Item>
        <Item>10/09/2014</Item>
        <Item>No Value</Item>
        <Item>No Value</Item>
        <Item>0</Item>
        <Item>jfoley@test.com</Item>
        <Item>No Value</Item>
        <Item>No Value</Item>
        <Item>No Value</Item>
        <Item>0</Item>
        <Item>No Value</Item>
        <Item>No Value</Item>
        <Item>No Value</Item>
        <Item>No Value</Item>
        <Item>Australia</Item>
        <Item>Primary</Item>
        <Item>No Value</Item>
        <Item>No Value</Item>
        <Item>No Value</Item>
        <Item>0</Item>
        <Item>8</Item>
    </Row>
</Report>

Which should become something like

<Table>
<Row>
<MembershipID>8</MembershipID>
...
...
</Row>
</Table>

Or Even

<Table>
<Row>
<Column name="MembershipID">8</Column>
...
</Row>
</Table>

Thanks in advance

Answer 1

If you want to handle simple rowspan and colspan, use class='transpose' on the table element with this transform:

<xsl:stylesheet version='1.0' xmlns:xsl='http://www.w3.org/1999/XSL/Transform'>
   <xsl:output method='xml'/>
   <xsl:template match='table[contains(@class, "transpose")]'>
      <table>
         <xsl:for-each select='(tr[1]/td|tr[1]/th)[position()]'>
            <tr>
               <xsl:for-each select='../..'>
                  <td>
                     <xsl:if test='tr[position()]/td[position()]/@rowspan'>
                        <xsl:attribute name='colspan'>
                           <xsl:value-of select='tr[position()]/td[position()]/@rowspan'/>
                        </xsl:attribute>
                     </xsl:if>
                     <xsl:value-of select='tr[position()]/td[position()]'/>
                  </td>
               </xsl:for-each>
            </tr>
         </xsl:for-each>
      </table>
   </xsl:template>
</xsl:stylesheet>

You can get more fancy handling edge cases, but it will take more code - hundreds of lines of xslt would be required to properly handle all special cases robustly.

Answer 2

Answer above is correct for the second option.

This is the result if you decide to do the first option.

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output indent="yes" omit-xml-declaration="yes" method="xml"/>
    <!-- Select the heading names into a variable -->
    <xsl:variable name="headingNames" select="//Row[1]/Item"/>
    <xsl:template match="/">
        <xsl:for-each select="Report/Row">
            <!--Check to make sure that we only select the second row onwards (as first row is the headings)--> 
            <xsl:if test="position() &gt; 1">
            <Row>
                <xsl:variable name="rowVal" select="."/>
                <xsl:for-each select="$rowVal/Item">
                    <xsl:variable name="currentPos" select="position()"/>
                    <xsl:element name="{$headingNames[$currentPos]}">
                        <xsl:value-of select="."/>
                    </xsl:element>
                </xsl:for-each>
                </Row>
            </xsl:if>           
        </xsl:for-each>     
    </xsl:template>
</xsl:stylesheet>

Answer 3

The second option is definitely preferable. Try it this way:

XSLT 1.0

<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="column-name" match="Row[1]/Item" use="count(preceding-sibling::Item)" />

<xsl:template match="/">
    <Table>
        <xsl:for-each select="Report/Row[position() > 1]">
            <Row>
                <xsl:apply-templates select="Item"/>
            </Row>
        </xsl:for-each>
    </Table>
</xsl:template>

<xsl:template match="Item">
    <Column name="{key('column-name', count(preceding-sibling::Item))}">
        <xsl:value-of select="."/>
    </Column>
</xsl:template>

</xsl:stylesheet>

--

Or perhaps you prefer changing:

<xsl:template match="Item">

to:

<xsl:template match="Item[not(.='No Value')]">