CODEWITHSUNDEEP
xsltloopsdynamicnested
Fun2Learn
Fun2Learn

Reputation: 33

Nested loops in XSLT for dynamically building XML

I am getting an XML document output from Cognos which I want to use as Input for Crystal reports. However, the XML format needed by Crystal Report is different from the XML format of Cognos output.

I am trying to transform the Input XML document (Cognos) using XSLT to get desired XML for Crystal.

Having set the context, below is the Input XML coming from Cognos:

<?xml version="1.0"?>
<dataset>
<metadata>
    <item Name="EmpId" />
    <item Name="EmpName" />
    <item Name="DeptName" />
</metadata>
<data>
    <rows>
        <row>
            <value>1</value>
            <value>John</value>
            <value>Finance</value>
        </row>
        <row>
            <value>2</value>
            <value>Peter</value>
            <value>Admin</value>
        </row>
    </rows>
</data>

Desired XML format required by Crystal Report:

<?xml version="1.0"?>
<dataset>
<row>
    <EmpId>1</EmpId>
    <EmpName>John</EmpName>
    <DeptName>Finance</DeptName>
</row>
<row>
    <EmpId>2</EmpId>
    <EmpName>Peter</EmpName>
    <DeptName>Admin</DeptName>
</row>
</dataset>

I have written below XSLT for the desired transformation:

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" indent="yes"/>
<xsl:template match="/">
<dataset>
<xsl:for-each select="./dataset/data/rows/row">
    <row>
        <xsl:for-each select="/dataset/metadata/item">
            <xsl:element name="{@Name}">
                <xsl:for-each select="/dataset/data/rows/row/value">
                    <xsl:value-of select="."/>
                </xsl:for-each>                 
            </xsl:element>
        </xsl:for-each>             
    </row>
</xsl:for-each>
</dataset>
</xsl:template>
</xsl:stylesheet>

I am getting below Output:

<?xml version="1.0" encoding="UTF-16"?>
<dataset>
<row>
    <EmpId>1JohnFinance2PeterAdmin</EmpId>
    <EmpName>1JohnFinance2PeterAdmin</EmpName>
    <DeptName>1JohnFinance2PeterAdmin</DeptName>
</row>
<row>
    <EmpId>1JohnFinance2PeterAdmin</EmpId>
    <EmpName>1JohnFinance2PeterAdmin</EmpName>
    <DeptName>1JohnFinance2PeterAdmin</DeptName>
</row>

Where am I going wrong?

Upvotes: 1

Views: 2792

Answers (3)

Fun2Learn
Fun2Learn

Reputation: 33

After customizing the solution provided by @DimitreNovatchev I was able to get the desired transformation of XML.

Input XML

<?xml version="1.0"?>
<dataset  xmlns="http://developer.cognos.com/schemas/xmldata/1/"  xmlns:xs="http://www.w3.org/2001/XMLSchema-instance">
<metadata>
    <item name="Employee Id" />
    <item name="Employee Name" />
    <item name="Department Name" />
</metadata>
<data>      
    <row>
        <value>1</value>
        <value Salutation="Dr." >John</value>
        <value>Finance</value>
    </row>
    <row>
        <value>2</value>
        <value Salutation="Mr." >Peter</value>
        <value>Admin</value>
    </row>      
</data>

XSLT Transformation

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:c="http://developer.cognos.com/schemas/xmldata/1/" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output indent="yes"/>
<xsl:strip-space elements="*"/>
<xsl:variable name="vNames" select="/*/c:metadata/*/@name" />
<xsl:template match="/*/c:data">
<dataset>
    <xsl:apply-templates/>
</dataset>
</xsl:template>
<xsl:template match="c:row">
<row>
    <xsl:apply-templates/>
</row>
</xsl:template>
<xsl:template match="c:row/*">
<xsl:variable name="vPos" select="position()"/>
<xsl:element name="{translate($vNames[$vPos], ' ', '_')}">
    <xsl:apply-templates select="@*"/>
    <xsl:apply-templates/>
</xsl:element>
</xsl:template>
<xsl:template match="@*">
<xsl:attribute name="{name()}">
    <xsl:value-of select="." />
</xsl:attribute>
</xsl:template>

</xsl:stylesheet>

Output XML

<?xml version="1.0" encoding="UTF-16"?>
<dataset xmlns:c="http://developer.cognos.com/schemas/xmldata/1/">
<row>
    <Employee_Id>1</Employee_Id>
    <Employee_Name Salutation="Dr.">John</Employee_Name>
    <Department_Name>Finance</Department_Name>
</row>
<row>
    <Employee_Id>2</Employee_Id>
    <Employee_Name Salutation="Mr.">Peter</Employee_Name>
    <Department_Name>Admin</Department_Name>
</row>

Upvotes: 1

Peter
Peter

Reputation: 1796

I would use the <template> and <apply-templates> mechanism to solve this problem. For-each is not the right way to go here I think.

XML input:

<?xml version="1.0"?>
<dataset>
<metadata>
    <item Name="EmpId" />
    <item Name="EmpName" />
    <item Name="DeptName" />
</metadata>
<data>
    <rows>
        <row>
            <value>1</value>
            <value>John</value>
            <value>Finance</value>
        </row>
        <row>
            <value>2</value>
            <value>Peter</value>
            <value>Admin</value>
        </row>
    </rows>
</data>
</dataset>

apply this stylesheet to it:

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" indent="yes"/>

<xsl:template match="/">
    <dataset>
        <xsl:apply-templates select="//row"/>
    </dataset>
</xsl:template>

<xsl:template match="row">
    <row>
        <xsl:apply-templates select="value"/>
    </row>
</xsl:template>

<xsl:template match="value">
    <xsl:variable name="index">
        <xsl:number/>
    </xsl:variable>
    <xsl:element name="{../../../../metadata/item[position() = $index]/@Name}">
        <xsl:apply-templates select="@* | node()"/>
    </xsl:element>
</xsl:template>

</xsl:stylesheet>

you get this output:

<?xml version="1.0" encoding="utf-8"?>
<dataset>
<row>
    <EmpId>1</EmpId>
    <EmpName>John</EmpName>
    <DeptName>Finance</DeptName>
</row>
<row>
    <EmpId>2</EmpId>
    <EmpName>Peter</EmpName>
    <DeptName>Admin</DeptName>
</row>
</dataset>

The dataset and row elements I create by matching and applying certain templates.

Upvotes: 1

Dimitre Novatchev
Dimitre Novatchev

Reputation: 243459

This short and simple transformation:

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

 <xsl:variable name="vNames" select="/*/metadata/*/@Name"/> 

 <xsl:template match="/*/data">
     <dataset><xsl:apply-templates/></dataset>
 </xsl:template>

 <xsl:template match="row">
  <row><xsl:apply-templates/></row>
 </xsl:template>

 <xsl:template match="row/*">
  <xsl:variable name="vPos" select="position()"/>
  <xsl:element name="{$vNames[$vPos]}"><xsl:apply-templates/></xsl:element>
 </xsl:template>
</xsl:stylesheet>

when applied on the provided XML document (added a missing closing tag to make it well-formed):

<dataset>
    <metadata>
        <item Name="EmpId" />
        <item Name="EmpName" />
        <item Name="DeptName" />
    </metadata>
    <data>
        <rows>
            <row>
                <value>1</value>
                <value>John</value>
                <value>Finance</value>
            </row>
            <row>
                <value>2</value>
                <value>Peter</value>
                <value>Admin</value>
            </row>
        </rows>
    </data>
</dataset>

produces the wanted, correct result:

<dataset>
   <row>
      <EmpId>1</EmpId>
      <EmpName>John</EmpName>
      <DeptName>Finance</DeptName>
   </row>
   <row>
      <EmpId>2</EmpId>
      <EmpName>Peter</EmpName>
      <DeptName>Admin</DeptName>
   </row>
</dataset>

Explanation:

  1. Using templates and the XSLT template selection mechanism to do the job. As a rule in XSLT we prefer xsl:apply-templates to xsl:for-each -- thus getting simpler, more extensible, more understandable and maintainable code. This is an example of an almost 100% "push style" solution.

  2. Using xsl:variable to get (once and forever) the nodes we will be constantly working with.

  3. Saving position() in a variable for later use in other contexts -- position() is context-dependent.

Upvotes: 5

Related Questions