SQL XML query escapes HTML tags

     just after the big show stopper, I'm now dealing with another one..

Lets say for now, I have something like this

SELECT 'and i'd give up forever to touch ' 'line'
UNION
SELECT 'you cause i know that you feel me so love, your the closest to heaven that i've ever been ' 'line'
UNION
SELECT 'and i don't want to go home right  ' 'line'

UNION
SELECT 'now  and all i can taste is your sweetness ' 'line'
UNION
SELECT 'and all i can breath is your life ' 'line'

FOR XML PATH('test'),TYPE


and, I run it... I'll get those angle brackets escaped (< and >)




fortunately, there is a way to still make those angle brackets, but will lose the XML thing by using value


SELECT 'and i'd give up forever to touch ' 'line'

UNION
SELECT 'you cause i know that you feel me so love, your the closest to heaven that i've ever been ' 'line'
UNION
SELECT 'and i don't want to go home right  ' 'line'

UNION
SELECT 'now  and all i can taste is your sweetness ' 'line'
UNION
SELECT 'and all i can breath is your life ' 'line'

FOR XML PATH('test'),TYPE).value('(/test)[1]','NVARCHAR(MAX)')

will keep the angle brackets, but it is once again, an NVARVCHAR field...

much of an interest, but doesn't help me because I have a large nested XML PATH query.. :(

XSLT8690: XSLT processing failed and all HTML are displayed as mere text

You checked for the encoding, you checked for the xsl output set to html, and even removed all the comments before the xsl template but still you are getting. All the contents displayed as plain text and when you checked your developer tool,

XSLT8690: XSLT processing failed

And frustrated to take hit on google to find the solution? well, relax because I encountered this once and even wasted almost a week worth of things to do because it works in Chrome, it works in Firefox, it works in Safari, but NOT IE...(8 or 9 too).

If I've done it right, then how come it is displaying properly in other browsers and NOT IT... I understand how you feel..

But the real reason is, you "might" have screwed it a little and those non-IE friends just wouldn't mind about it. Too bad that it will not tell you where did you get wrong (even if you sing out loud.. LOL).

We don't know where it got penalized by IE,so we're asking .NET side where. Using XslCompiledTransform.

It is simple, just instantiate XslCompiledTransform object and pass true in the constructor like this.

XslCompiledTransform sparemyxsl = new XslCompiledTransform(true);

sparemyxsl.Load(pathToYourStyleSheet);

Even if this stylesheet is using include or import, once you invoked the Load method, it will process all related XSL stylesheets, merge it and check what's going on. It will throw an exception when it finds something bad.

On my experience, I accidentally added an extra line of include that also refers to the same xsl file. It is a good start. (^_^). I hope you can find the fault part. 

Happy Coding.



and don't forget the namespace for XslCompiledTransform

System.Xml;
System.Xml.Xsl;

Adding stylesheet to XML document.

I am currently working on a web enhancement where XML datas are passed on the response object. It seems that the website will serve as a service point for some request. Since it is in XML format, it can be consumed by some applications with minimal parsing. Even with javascripts and XSLT too, which makes it really flexible.

Right after it is fetched from the database, the result is in Scalar form XML, thanks to SQL's XML PATH, which removes XML parsing from data resultset.

Doing this with XML LINQ is pretty easy.

XDocument xml = XDocument.Parse(stringFromDataBaseScalarResult);
var pi = new XProcessingInstruction("xml-stylesheet","type=\"text/xsl\" href=\"yourxslfile.xsl\"");
xml.Root.AddBeforeSelf(pi);
doc.Save(Response.Output);




or without LINQ




XmlDocument xml = new XmlDocument();
xml.LoadXml(stringFromDataBaseScalarResult);
XmlProcessingInstruction pi = xml.CreateProcessingInstruction("xml-stylesheet","type=\"text/xsl\" href=\"yourxslfile.xsl\"");
xml.InsertBefore(pi,xml.DocumentElement);
xml.Save(Response.Output);


I have to find some better clean way on the second one, because my initial attempt using Append method placed the processing instruction at the bottom of the xml document, although the stylesheet is still applied. It is an eyesore to some. (including me... hehe).