Click here to Skip to main content
Click here to Skip to main content

XML Pivot To XML

, 27 Mar 2007
Rate this:
Please Sign up or sign in to vote.
This article describes how to convert an existing XML set of values that are contained in elements to valid elements of another XML, using an XSL transformation.

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 instrinsic 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 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 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!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Mike DiRenzo
Web Developer
United States United States
I have lived and worked throughout the globe (Brussels, London, Paris, West Indies, USA) as an IT consultant. My favorite S/W development concentrations are XML/XSLT, C#, Agile Model - Driven Development. I admire two well-renowned authors: Scott Ambler and Sal Mangano. Obviously there are many, many others but these two have been key to my successes over the years. I live and work in Charlotte, NC, USA and run a small S/W consulting practice called Business Automation Systems, Inc. I hold a Bachelors degree in Business with a concentration in Management Information Systems from the University of North Carolina at Charlotte.

Comments and Discussions

 
GeneralNice! PinmemberMember 45586611-Nov-09 3:21 
GeneralCall me slow, but... PinsitebuilderShog926-Mar-07 6:57 
GeneralRe: Call me slow, but... Pinmemberbug menot26-Mar-07 9:51 
GeneralRe: Call me slow, but... PinmemberMike DiRenzo26-Mar-07 13:35 
GeneralRe: Call me slow, but... PinmemberMike DiRenzo26-Mar-07 13:35 
GeneralRe: Call me slow, but... PinmemberMike DiRenzo27-Mar-07 2:30 
GeneralRe: Call me slow, but... PinsitebuilderShog927-Mar-07 5:55 
Generalnice! Pinmemberbrady gaster26-Mar-07 6:42 
GeneralRe: nice! PinmemberMike DiRenzo26-Mar-07 13:35 
GeneralRe: nice! PinmemberMike DiRenzo27-Mar-07 2:28 
GeneralRe: nice! Pinmemberbrady gaster27-Mar-07 2:36 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140821.2 | Last Updated 27 Mar 2007
Article Copyright 2007 by Mike DiRenzo
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid