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
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4640 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
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4640 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
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4640 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 Burstein
Alan Burstein
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32458 Visits: 8577
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


Helpful links:

Best practices for getting help on SQLServerCentral -- Jeff Moden
How to Post Performance Problems -- Gail Shaw

Nasty fast set-based string manipulation functions:
For splitting strings try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL Server 2012+)
To split strings based on patterns try PatternSplitCM
Need to clean or transform a string? try NGrams, PatExclude8K, PatReplace8K, DigitsOnlyEE, or Translate8K

I cant 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. -- Itzik 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