XSLT on SSRS report

Question

I want to translate an XML file with data like the following:

<FlatData>
    <Details1_Collection>
        <Details1 Customer1="Customer" Total1="3" />
        ...
    </Details1_Collection>
</FlatData>

The data I am interested in is the attributes and their values in each Details1. The problem is that these attributes are not necessarily going to be the same in every XML file I want to translate, and I want a general purpose XSL that could handle such Details1 as these:

<Details1 Customer1="Customer" Total1="3" />
<Details1 Name="Jim" Age="14" Weight="180" />
<Details1 Date="2009-07-27" Range="1-5" Option1="True" />

These different Details1 would not occur in the same source XML file, but rather in different files. However, I would like to use the same XSL on each.

I was thinking I needed something like <xsl:value-of select="@attribute_name"/> but what do I put for @attribute_name when I don't know beforehand what attributes there will be? Also, how do I capture the attribute name? I would like to explode the source XML above to something like:

<Details1>
    <Customer1>Customer</Customer1>
    <Total1>3</Total1>
</Details1>

Edit: thanks for the responses! I'm having trouble getting more than the following output, however:

<?xml version="1.0" encoding="UTF-8"?>
<FlatData>
<Details1_Collection></Details1_Collection>
</FlatData>

I've tried both lavinio's and Jörn Horstmann's answers, as well as trying to combine the two. I run this command:

msxsl.exe -o output.xml input.xml transform.xsl

I think something that's getting in the way is a namespace in the input file:

<Report Name="MyReport" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="MyReport">

Answer 1

You can use "@*" to refer to all attributes, like these examples:

• <xsl:value-of select="@*"/>
• <xsl:apply-templates select="@*"/>
• <xsl:template match="@*">

The <xsl:element name=""> construct can be used to create a new element with an arbitrary name, and the functions name() or local-name() will return the name of a specific attribute.

To do what you want, try something along these lines:

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"&gt;
 <xsl:output method="xml" indent="yes"/>
 <xsl:template match="/">
  <FlatData>
   <Details1_Collection>
    <xsl:apply-templates select="FlatData/Details1_Collection/Details1"/>
   </Details1_Collection>
  </FlatData>
 </xsl:template>
 <xsl:template match="Details1">
  <Details1>
   <xsl:apply-templates select="@*"/>
  </Details1>
 </xsl:template>
 <xsl:template match="@*">
  <xsl:element name="{name()}">
   <xsl:value-of select="."/>
  </xsl:element>
 </xsl:template>
</xsl:stylesheet>

Answer 2

Does this Transformation give the result you want?

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">

    <xsl:output method="xml" indent="yes" encoding="utf-8" />

    <xsl:template match="//Details1">
        <Details1>
            <xsl:for-each select="@*">
                <xsl:element name="{name(.)}"><xsl:value-of select="." /></xsl:element>
            </xsl:for-each>
        </Details1>
    </xsl:template>

</xsl:stylesheet>

Answer 3

another way to write Jörn Horstmann's answer (if you need to do this with Details1, Details2, and so on...) would be:

<xsl:template match="//Details1 | //Details2 | //whatever">
  <xsl:copy>
    <xsl:apply-templates select="@*"/>
  </xsl:copy>
</xsl:template>

<xsl:template match="@*">
  <xsl:element name="{name(.)}">
    <xsl:value-of select="." />
  </xsl:element>
</xsl:template>

Answer 4

Probably the simplest way to do it:

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

  <xsl:template match="/">
    <FlatData>
      <xsl:copy-of select="//Details1" />
    </FlatData>
  </xsl:template>
</xsl:stylesheet>

Answer 5

To solve the namespace problem (for both answers), add a namespace declaration with a prefix to your XLST:

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                xmlns:r="MyReport"
                version="1.0">

and then use it in all your XPath expressions to qualify elements, for example:

<xsl:template match="//r:Details1">

Answer 6

There was increased difficulty because of the Microsoft SQL Reporting Services 2008 namespace that was part of the input XML. I didn't realize at first that <Report Name="MyReport" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="MyReport"> was such an important line. Thanks to Pavel Minaev for the namespace comment. The following XSL worked to extract the data I wanted:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:a="EXQC005">
  <xsl:output method="xml" indent="yes" encoding="utf-8"/>

  <xsl:template match="/">
    <xsl:for-each select="a:Report/a:FlatData/a:Details1_Collection/a:Details1">
      <xsl:element name="{name(.)}">
        <xsl:for-each select="@*">
          <xsl:element name="{name(.)}">
            <xsl:value-of select="."/>
          </xsl:element>
        </xsl:for-each>
      </xsl:element>
    </xsl:for-each>
  </xsl:template>
</xsl:stylesheet>

I think I will try to clean this up to use the apply-templates style that lavinio suggested. Thanks also to Jörn Horstmann for the select="@*" code in for-each loops. It would be interesting to figure out why Reporting Services reports are dumped initially with the xmlns value set to the name of the report, and not a schema URL.

I'll continue to update this answer as I refine this XSL.

Edit: here's a namespace-agnostic version since, for each different report from Reporting Services, there will apparently be a different namespace:

<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"&gt;
  <xsl:output method="xml" indent="yes" encoding="utf-8"/>

  <xsl:template match="/">
    <xsl:for-each select="*[local-name()='Report']/*[local-name()='FlatData']/*[local-name()='Details1_Collection']/*[local-name()='Details1']">
      <Details>
        <xsl:for-each select="@*">
          <xsl:element name="{name(.)}">
            <xsl:value-of select="."/>
          </xsl:element>
        </xsl:for-each>
      </Details>
    </xsl:for-each>
  </xsl:template>
</xsl:stylesheet>