Export (0) Print
Expand All
1 out of 7 rated this helpful - Rate this topic

Applying Transformations to XML Output

SQL Server 2005

Updated: 12 December 2006

You can use the XML rendering extension to produce XML files that are created during report export operations at run time. The resulting XML file can be subsequently transformed into almost any format using XSL Transformations (XSLT). You can use the XML rendering extension and XSLT to produce formats that are not supported by existing rendering extensions.

There are three common scenarios in which you would use XSLT:

  • Data transfer   You can transform the XML output from Reporting Services into another XML format that can be read by another application.
  • Text rendering   You can transform an XML report into a plain text file. Depending on the report data, you might find that special characters (such as the ampersand character) are rendered in an HTML-encoded format (for example, &amp;). To avoid HTML encoding of special characters, add the following method attribute to your XSLT file: <xsl:output method="text" encoding="utf-8" media-type="text/plain"/>.
  • HTML rendering   You can create HTML or other documents from an XML report.

The XML produced by the XML rendering extension is specific to the report. Because of this, the XSL transformation that you write has to be specific to the report.

ms159716.note(en-US,SQL.90).gifNote:
For security reasons, Reporting Services does not support XSL transformations with embedded Microsoft Visual Basic code.

The following XSL, when saved to a file and uploaded to the report server, can be used to transform the Product Catalog sample report into a list of product models in plain text.

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> 

<xsl:output method="text" encoding="utf-8" media-type="text/plain"/>
<xsl:strip-space elements="*"/> 

<xsl:template match="/">
  <xsl:text>Product Models</xsl:text>
  <xsl:text>&#xD;&#xA;</xsl:text>
  <xsl:apply-templates/>
</xsl:template>

<xsl:template match="Report">
  <xsl:for-each select="ProductTable/ProductTable_Group1_Collection/ProductTable_Group1">
    <xsl:text>&#xD;&#xA;</xsl:text>
    <xsl:value-of select="@Category"/>
    <xsl:for-each select="ProductTable_Group2_Collection/ProductTable_Group2">
      <xsl:text>&#xD;&#xA;    </xsl:text>
      <xsl:value-of select="@SubCategory"/>
      <xsl:for-each select="ProductTable_Group3_Collection/ProductTable_Group3">
        <xsl:text>&#xD;&#xA;        </xsl:text>
        <xsl:value-of select="@Model"/>
      </xsl:for-each>
    </xsl:for-each>
  </xsl:for-each>
  <xsl:apply-templates/>
</xsl:template>

</xsl:stylesheet>

To transform the sample report, you can use device information settings in the URL used to run the report. For example, the following URL runs the report, applies the transformation, and outputs the file that has an extension of .txt. This example assumes that the XSL from the previous example is stored with the name ProductModels.xsl in the same folder as the Product Catalog report on the report server.

http://localhost/reportserver?/AdventureWorks%20Sample%20Reports/Product+Catalog&rs:Command=Render&rs:Format=XML&rc:OmitSchema=True&rc:FileExtension=txt&rc:XSLT=ProductModels.xsl

You are not limited to using Reporting Services to transform your XML report using XSLT. You can also retrieve the XML from the report server and apply XSLT using your own application.

ms159716.note(en-US,SQL.90).gifNote:
The XSL file that is used in this example is specific to the Product Catalog sample report. If a different report is used, or the Product Catalog report is altered, the file may not be transformed correctly.

Release History

12 December 2006

Changed content:
  • Corrected default sample path in xml rendering command.

5 December 2005

New content:
  • Text rendering warning.

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.