XML Pivot To XML

• Download source - 574 B

Introduction

This article describes how to convert an existing XML set of values that are contained in elements to valid elements of another XML using a XSL transformation.

Overview

Pivoting an arbitrary set of data such that its values become columns is often accomplished using Excel's Pivot Table feature or maybe some complicated T-SQL statement. Using a simple XSL technique, one can accomplish this using intrinsic functions of XSL: <xsl:variable/> and <xsl:element/>.

To be clear, let me qualify the source of xsl using the following common namespace declaration:

xmlns:xsl="http://www.w3.org/1999/XSL/Transform"

The XML

This simple XML data will be used to convert a particular value contained in a particular element to a valid XML element with the said value as the element name. Confused? Think Pivot Table!

<NewDataSet title="XMLPivotXML">
    <Table>
        <Arg>ArgValue123</Arg>
        <CtxtKeyID>CTXT123</CtxtKeyID>
        <CtxtName>ElementValue237</CtxtName>
    </Table>
    <Table>
        <Arg>ArgValue196</Arg>
        <CtxtKeyID>CTXT133</CtxtKeyID>
        <CtxtName>ElementValue238</CtxtName>
    </Table>
</NewDataSet>

The XSL

In the following example, I am using Arg as the desired value to convert to an element. Note the code snippet:

<xsl:variable name='arg'><xsl:value-of select="Arg" /></xsl:variable>

The intent is to convert all Arg element values to elements with the said values as their respective names through transformation. In the following transformations, I replace Arg with other valid elements contained in the source XML. Here is the full XSL:

<xsl:stylesheet version="1.0" 
     xmlns:xsl="http://www.w3.org/1999/XSL/Transform" >
    <xsl:template match="/"> 
        <args>
            <xsl:attribute name="ContextName">
                <xsl:value-of select="NewDataSet/@title"/>
            </xsl:attribute>
            <xsl:for-each select="//Table">
                <xsl:variable name='arg'><xsl:value-of select="Arg" />
                </xsl:variable>
                <xsl:element name='{$arg}'/>
            </xsl:for-each>
        </args>
    </xsl:template>
</xsl:stylesheet>        

The Transformation

Using Arg as the target element to convert, I get the following results:

<args ContextName="XMLPivotXML">
    <ArgValue123 />
    <ArgValue196 />
</args>

Using CtxtKeyID as the target element to convert, I get the following results:

<args ContextName="XMLPivotXML">
    <CTXT123 />
    <CTXT133 />
</args>

Using CtxtName as the target element to convert, I get the following results:

<args ContextName="XMLPivotXML">
    <ElementValue237 />
    <ElementValue238 />
</args>

As you can see, the possibilities are wide open with what one can accomplish using this simple technique. I was thinking of using this as a template function that accepts a parameter. Good luck and happy transforming!