SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Render XML report using XSL transform


Render XML report using XSL transform

Author
Message
danka_6786978
danka_6786978
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 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.
Arthur Olcot
Arthur Olcot
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3560 Visits: 1779
Have you got an example before/after xml that you can post as that may help
danka_6786978
danka_6786978
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 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>


Arthur Olcot
Arthur Olcot
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3560 Visits: 1779
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/aBigGrinocument"/></xsl:copy>
</xsl:template>
<xsl:template match="*">
<xsl:element name="{local-name(.)}">
<xsl:apply-templates/>
</xsl:element>
</xsl:template>
</xsl:stylesheet>


danka_6786978
danka_6786978
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 73
Unfortunately it's exactly the same thing as before. But thanks for trying :-)
Arthur Olcot
Arthur Olcot
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3560 Visits: 1779
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..
danka_6786978
danka_6786978
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 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:...."
aglos
aglos
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 6
thx a lot for this information
Alan.B
Alan.B
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12695 Visits: 7992
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



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search