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!