How to transform a XML document with metadata using XSLT?

Question

I have a report generator tool that produces XML documents for different kinds of reports. Each of those XML documents will have a set of fields (reporting:columns in the below XML document) which are used to describe the actual data (reporting:line in the below XML document) in it. Since the list of fields is dynamic and keep changing for every kind of report, how to write a generic XSL template that use the 'fields' to transform the data into a csv file.

My sample XML document:

<?xml version="1.0" encoding="UTF-8" standalone="no" ?>
<reporting:root xmlns:reporting="http://www.something.net/reporting">

  <reporting:default0 reporting:type="Portfolio">
    <reporting:header>      
      <reporting:configuration>
        <reporting:columns>
          <reporting:column reporting:group="instrument" reporting:name="Ident" reporting:tag="ident" reporting:type="int"/>
          <reporting:column reporting:group="prices" reporting:name="Last (Time)" reporting:tag="lastTime" reporting:type="string"/>
          <reporting:column reporting:group="noGroup" reporting:name="RIC" reporting:tag="ric" reporting:type="string"/>
          <reporting:column reporting:group="instrument" reporting:name="Reference" reporting:tag="reference" reporting:type="string"/>
         <reporting:column reporting:group="result" reporting:name="Currency" reporting:tag="currency" reporting:type="string"/>          
        </reporting:columns>
     </reporting:configuration>      
    </reporting:header>
    <reporting:window reporting:Id="36674" reporting:level="0" reporting:name="MY_PORTFOLIO" reporting:parentId="11991">
      <reporting:line reporting:Id="67520135" reporting:level="1" reporting:name="INTERNATIONAL BUSINESS MACHINES CORP" reporting:parentId="36674" reporting:positionType="0">
        <reporting:ident>643633</reporting:ident>        
        <reporting:reference>IBM.USD</reporting:reference>
        <reporting:currency>USD</reporting:currency>        
      </reporting:line>
     <reporting:line reporting:Id="67520179" reporting:level="1" reporting:name="GENERAL ELECTRIC CO" reporting:parentId="36674" reporting:positionType="0">
        <reporting:ident>643635</reporting:ident>
        <reporting:ric>GE.N</reporting:ric>
        <reporting:reference>GE.USD</reporting:reference>
        <reporting:currency>USD</reporting:currency>        
     </reporting:line>
    </reporting:window>
  </reporting:default0>
</reporting:root>

I need to see the output formated like:

ident,lastTime,ric,reference,currency
643633,,,IBM.USD,USD
643635,,GE.N,GE.USD,USD

I did some work on it but it was half way through. Could not find how the handle the 'dynamic' list of fields and use them as tags that describe the actual data.

Can somebody help ?

Thanks.

Answer 1

There are various ways of tackling this in XSLT, but I think the most elegant and maintainable is to write a stylesheet that processes the source document given, and outputs an XSLT stylesheet to do the actual reporting. I've seen this approach used very succesfully in a number of projects.

Answer 2

<xsl:output method="text"/>

<xsl:template match="/">

    <xsl:for-each select="/*:root/*:default0/*:header/*:configuration/*:columns/*:column">
        <xsl:value-of select="@reporting:tag"/>
        <xsl:text>,</xsl:text>
    </xsl:for-each>
    <xsl:for-each select="/*:root/*:default0/*:window/*:line">
        <xsl:variable name="theline"  select="."/>
        <xsl:text>&#x0a;</xsl:text>

        <xsl:for-each select="/*:root/*:default0/*:header/*:configuration/*:columns/*:column" >
            <xsl:variable name="oname"  select="@reporting:tag" />
              <xsl:value-of select="$theline/*[local-name()=$oname]" /><xsl:text>,</xsl:text>
        </xsl:for-each>
    </xsl:for-each>
</xsl:template>

Will produce:

ident,lastTime,ric,reference,currency,

643633,,,IBM.USD,USD,

643635,,GE.N,GE.USD,USD,

( You'll need to stick on a check for last to get rid of the final comma. )

Answer 3

Try

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:r="http://www.something.net/reporting">
    <xsl:output method="text" version="1.0"/>
    <xsl:template match="/">
        <xsl:for-each select="/r:root/r:default0/r:header/r:configuration/r:columns/r:column">
            <xsl:value-of select="@r:tag"/>
            <xsl:if test="position() != last()">,</xsl:if>
        </xsl:for-each>
        <xsl:text>&#xA;</xsl:text>
        <xsl:for-each select="/r:root/r:default0/r:window/r:line">
            <xsl:variable name="POSITION" select="position()"/>
            <xsl:for-each select="//r:root/r:default0/r:header/r:configuration/r:columns/r:column">
                <xsl:variable name="TAGNAME" select="@r:tag"/><xsl:value-of select="//r:line[$POSITION]/*[local-name()=$TAGNAME]"/><xsl:if test="position() != last()">,</xsl:if>
            </xsl:for-each>
            <xsl:text>&#xA;</xsl:text>
        </xsl:for-each>
    </xsl:template>
</xsl:stylesheet>

Note that I just shortened the namespace prefix to make it easier to read (and type).

Answer 4

This short and simple transformation:

<xsl:stylesheet version="1.0"
 xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
 xmlns:r="http://www.something.net/reporting">
 <xsl:output omit-xml-declaration="yes" indent="yes"/>
 <xsl:strip-space elements="*"/>

 <xsl:variable name="vColNames" select="/*/*/r:header/*/*/*/@r:tag"/>

 <xsl:template match="/">
  <xsl:for-each select="$vColNames">
   <xsl:value-of select="concat(., ',')"/>
  </xsl:for-each>
  <xsl:apply-templates/>
 </xsl:template>

 <xsl:template match="r:line">
  <xsl:variable name="vThis" select="."/>
  <xsl:text>&#xA;</xsl:text>
  <xsl:for-each select="$vColNames">
    <xsl:if test="position() > 1">,</xsl:if>
    <xsl:apply-templates select="$vThis/*[local-name() = current()]"/>
  </xsl:for-each>
 </xsl:template>
</xsl:stylesheet>

when run on the provided XML document:

<reporting:root xmlns:reporting="http://www.something.net/reporting">

  <reporting:default0 reporting:type="Portfolio">
    <reporting:header>
      <reporting:configuration>
        <reporting:columns>
          <reporting:column reporting:group="instrument" reporting:name="Ident" reporting:tag="ident" reporting:type="int"/>
          <reporting:column reporting:group="prices" reporting:name="Last (Time)" reporting:tag="lastTime" reporting:type="string"/>
          <reporting:column reporting:group="noGroup" reporting:name="RIC" reporting:tag="ric" reporting:type="string"/>
          <reporting:column reporting:group="instrument" reporting:name="Reference" reporting:tag="reference" reporting:type="string"/>
         <reporting:column reporting:group="result" reporting:name="Currency" reporting:tag="currency" reporting:type="string"/>
        </reporting:columns>
     </reporting:configuration>
    </reporting:header>
    <reporting:window reporting:Id="36674" reporting:level="0" reporting:name="MY_PORTFOLIO" reporting:parentId="11991">
      <reporting:line reporting:Id="67520135" reporting:level="1" reporting:name="INTERNATIONAL BUSINESS MACHINES CORP" reporting:parentId="36674" reporting:positionType="0">
        <reporting:ident>643633</reporting:ident>
        <reporting:reference>IBM.USD</reporting:reference>
        <reporting:currency>USD</reporting:currency>
      </reporting:line>
     <reporting:line reporting:Id="67520179" reporting:level="1" reporting:name="GENERAL ELECTRIC CO" reporting:parentId="36674" reporting:positionType="0">
        <reporting:ident>643635</reporting:ident>
        <reporting:ric>GE.N</reporting:ric>
        <reporting:reference>GE.USD</reporting:reference>
        <reporting:currency>USD</reporting:currency>
     </reporting:line>
    </reporting:window>
  </reporting:default0>
</reporting:root>

produces the wanted, correct result:

ident,lastTime,ric,reference,currency,
643633,,,IBM.USD,USD
643635,,GE.N,GE.USD,USD