SSRS XML reformating using xslt during export

Question

I am trying to create data feed in following format,

    <rss version="2.0">
    <channel>
    <Title>FeedTitle</Title>
    <link>http://www.mydomain.com</link>
    <description>My Products</description>
    <item>
        <Id>10890</Id>
        <Title>Benetton 01</Title>
    </item>
    <item>
        <Id>10700</Id>
        <Title>Benetton 02</Title>
    </item>
    </channel>
    </rss>

BUT,Reporting Services export option has generated following xml data feed which doesn't work on Google Merchant Center.

    <Report xsi:schemaLocation="pg_google_data_feed http://reportserver?%2Fpg_google_data_feed&rs%3AFormat=XML&rc%3ASchema=True" Name="pg_google_data_feed">
    <Title>FeedTitle</Title>
    <link>http://www.mydomain.com</link>
    <description>My Products</description>
    <ProductList>
        <Details_Collection>
            <Details>
                <Id>10890</Id>
                <Title>Benetton 01</Title>
            </Details>
            <Details>
                <Id>10700</Id>
                <Title>Benetton 02</Title>
            </Details>
                   </Details_Collection>
    </ProductList>
    </Report>

It would be very helpful if any body tell me what type of XSLT would required to reformat the XML data into another xml file.

EDIT :

step 1. Created xslt file using following code.

<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="Details">
        <Details>
            <xsl:for-each select="@*">
                <xsl:element name="{name(.)}">
                    <xsl:value-of select="." />
                </xsl:element>
            </xsl:for-each>
        </Details>
    </xsl:template>
</xsl:stylesheet>

Step 2: set Property of report to "datafeed.xslt"

Without applying xslt to my ssrs report result appears like this,

    <Report xsi:schemaLocation="pg_google_data_feed http://reportserver?%2Fpg_google_data_feed&rs%3AFormat=XML&rc%3ASchema=True" Name="pg_google_data_feed">
        <Title>FeedTitle</Title>
        <link>http://www.mydomain.com</link>
        <description>My Products</description>
        <ProductList>
            <Details_Collection>
                <Details>
                    <Id>1000</Id>
                </Details>
                <Details>
                     <Id>1000</Id>
                 </Details>
             </Details_Collection> 
        </ProductList>
    </Report>

if i attached above mentioned xslt to the report through DataTransform Property, am getting following output.

XML Parsing Error: syntax error
Location: file:///C:/Documents%20and%20Settings/Administrator/Desktop/pg_google_data_feed.xml
Line Number 1, Column 39:<?xml version="1.0" encoding="utf-8"?>1089010947109191093310895108921092406598115141151311512
--------------------------------------^

Thank you in advance Sudhakar

When you say "which type of XSLT" do you mean "which version"? For your task XSLT 1.0 should work fine since you only have to suppress a few tags and rename others. Please, consult with a general guide on XSLT (e.g. start with https://en.wikipedia.org/wiki/XSLT) first. If you run into troubles with a concrete XSLT you can add it to your question above and you will be helped. — Marcus Rickert, Oct 26 '13 at 09:21
actually i need xslt code for suppressing tag Details_Collection> and rename the tag
to . i would really appreciate if you could provide me xslt code for that. thanks! — user2538900, Oct 26 '13 at 11:03

score 2 · Answer 1 · answered Oct 27 '13 at 11:50

With a slightly updated wrapper around your input

<?xml version="1.0" encoding="ISO-8859-1"?>
<Report 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="pg_google_data_feed http://reportserver?%2Fpg_google_data_feed&amp;rs%3AFormat=XML&amp;rc%3ASchema=True" Name="pg_google_data_feed">
  <!-- everything else stays the same here -->
</Report>

the following XSLT will provide the required conversions and filtering

<?xml version="1.0" encoding="ISO-8859-1"?>
<xsl:stylesheet 
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    exclude-result-prefixes="xsi">
  <xsl:output method="xml" indent="yes" encoding="utf-8" />

  <!-- rule to suppress the undesired nodes -->
  <xsl:template match="Report|ProductList|Details_Collection">
    <xsl:apply-templates/>
  </xsl:template>

  <!-- rule to rename the Details node -->
  <xsl:template match="Details">
    <item>
      <xsl:apply-templates/>
    </item>
  </xsl:template>

  <!-- rule to copy everything else -->
  <!-- see http://stackoverflow.com/questions/857010/xsl-avoid-exporting-namespace-defintions-to-resulting-xml-documents-->
  <!-- see http://stackoverflow.com/questions/14166259/xslt-default-template-confusion -->
  <xsl:template match="*|@*">
    <xsl:element name="{name()}">
      <xsl:apply-templates/>
    </xsl:element>
  </xsl:template>

  <!-- rule to start the conversion and provide the wrapper tags -->
  <xsl:template match="/">
    <rss version="2.0">
      <channel>
        <xsl:apply-templates/>
      </channel>
    </rss>
  </xsl:template>

</xsl:stylesheet>

Notes:

This XSLT does just the conversion of one XML into another. You should setup a test environment to check just this aspect before you proceed.
As a second step you should make the modifications (if required) to integrate the XSLT into the automatic processing setup that you seem to have. I have noted that string "pg_google_data_feed http://reportserver?%2Fpg_google_data_feed&rs%3AFormat=XML&rc%3ASchema=True" does not seem to be valid since it contains unquoted ampersands. You may want to use "pg_google_data_feed http://reportserver?%2Fpg_google_data_feed&rs%3AFormat=XML&rc%3ASchema=True" instead.

SSRS XML reformating using xslt during export

1 Answers1

Linked