Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Render XML report using XSL transform Expand / Collapse
Author
Message
Posted Wednesday, July 24, 2013 6:02 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 18, 2014 10:18 AM
Points: 15, Visits: 73
Hi, I am upgrading reports from 2005 to 2008R2 and I noticed that two reports are failing on the new server. So, after looking up its issue, I realized that I might need to rewrite XSL that was formatting the XML output/render...

So, my scenario is as such:
the report renders xml with root node <Report blah blah bla> and then it renders my xml, with its own root like I need it. So it's kinda double wrapped, it's still full and proper xml after the render.

I got to the point where I get <?xml version="1.0"?> at the top of the rendered file, and then I have that <Report blah name blah> root that I have to remove... It's pretty confusing, spent all day searching for an answer.

How do I go about removing the first line and the <Report....> root from final rendered results?

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl"
>
<xsl:output method="xml" encoding="ascii" omit-xml-declaration="yes" indent="yes"/>

<xsl:template match="@* | node()">
<xsl:copy>
<xsl:apply-templates select="@* | node()"/>
</xsl:copy>
</xsl:template>
</xsl:stylesheet>

I'd appreciate some help here.
Post #1477324
Posted Thursday, July 25, 2013 1:27 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:07 AM
Points: 2,701, Visits: 1,694
Have you got an example before/after xml that you can post as that may help
Post #1477357
Posted Thursday, July 25, 2013 9:10 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 18, 2014 10:18 AM
Points: 15, Visits: 73
Yes, I can

Current Result:
<?xml version="1.0"?>
-<Report xmlns="test_3">
test_3
<Document>
<Assessments>
<Assessment>
<Date>05/01/2013</Date>
<Type>Physical</Type>
<Completed>1</Completed>
</Assessment>
<Assessment>
<Date>06/14/2013</Date>
<Type>Physical</Type>
<Completed>0</Completed>
</Assessment>
</Assessments>
</Document>
</Report>

Wanted result:
<Document>
<Assessments>
<Assessment>
<Date>05/01/2013</Date>
<Type>Physical</Type>
<Completed>1</Completed>
</Assessment>
<Assessment>
<Date>06/14/2013</Date>
<Type>Physical</Type>
<Completed>0</Completed>
</Assessment>
</Assessments>
</Document>

Post #1477560
Posted Thursday, July 25, 2013 9:47 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:07 AM
Points: 2,701, Visits: 1,694
I'm not very good at XSL myself, but does this get you what you need?
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:a="test_3">
<xsl:output method="xml" indent="yes" omit-xml-declaration="yes"/>
<xsl:template match="/">
<xsl:copy><xsl:apply-templates select="/a:Report/a:Document"/></xsl:copy>
</xsl:template>
<xsl:template match="*">
<xsl:element name="{local-name(.)}">
<xsl:apply-templates/>
</xsl:element>
</xsl:template>
</xsl:stylesheet>

Post #1477586
Posted Thursday, July 25, 2013 9:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 18, 2014 10:18 AM
Points: 15, Visits: 73
Unfortunately it's exactly the same thing as before. But thanks for trying
Post #1477592
Posted Thursday, July 25, 2013 10:01 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:07 AM
Points: 2,701, Visits: 1,694
danka_6786978 (7/25/2013)
Unfortunately it's exactly the same thing as before. But thanks for trying


That's odd then.. When i pass your "Current Result" xml example through that style sheet, it outputs in the format of your "Wanted Result" xml...

Like I say, i'm not an XSL expert so not sure to be honest..
Post #1477599
Posted Thursday, July 25, 2013 12:48 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 18, 2014 10:18 AM
Points: 15, Visits: 73
I re-ran it, I think I made a mistake when copying the xsl from her, sorry ... So, now that I corrected it, I only get an error: "XML document must have a top level element. Error processing resource file:...."
Post #1477679
Posted Monday, September 30, 2013 5:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 30, 2013 5:39 AM
Points: 1, Visits: 6
thx a lot for this information
Post #1499942
Posted Tuesday, October 8, 2013 4:26 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: 2 days ago @ 8:59 AM
Points: 646, Visits: 2,992
danka_6786978 (7/24/2013)
Hi, I am upgrading reports from 2005 to 2008R2 and I noticed that two reports are failing on the new server. So, after looking up its issue, I realized that I might need to rewrite XSL that was formatting the XML output/render...

So, my scenario is as such:
the report renders xml with root node <Report blah blah bla> and then it renders my xml, with its own root like I need it. So it's kinda double wrapped, it's still full and proper xml after the render.

I got to the point where I get <?xml version="1.0"?> at the top of the rendered file, and then I have that <Report blah name blah> root that I have to remove... It's pretty confusing, spent all day searching for an answer.

How do I go about removing the first line and the <Report....> root from final rendered results?

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl"
>
<xsl:output method="xml" encoding="ascii" omit-xml-declaration="yes" indent="yes"/>

<xsl:template match="@* | node()">
<xsl:copy>
<xsl:apply-templates select="@* | node()"/>
</xsl:copy>
</xsl:template>
</xsl:stylesheet>

I'd appreciate some help here.


I know I am a little late here but... You were close, you just needed to change the context in you template match statement to begin at Report.

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl"
>
<xsl:output method="xml" encoding="ascii" omit-xml-declaration="yes" indent="yes"/>

<xsl:template match="Report/@* | Report/node()">
<xsl:copy>
<xsl:apply-templates select="@* | node()"/>
</xsl:copy>
</xsl:template>
</xsl:stylesheet>



-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)

"I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001

My blog
Post #1502865
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse