Solved

XML returned from SQL Server is missing XML markup tags? (ASP/XML/XSLT)

Posted on 2004-04-09
4
611 Views
Last Modified: 2013-11-19
Experts,
I am trying to write an ASP that executes a SQL Server stored procedure with FOR XML AUTO, ELEMENTS.  I want to return this resultset and process it through an XSLT in order to get an HTML page reporting on the data returned by the stored procedure.

I have looked all over, including in PAQs here at Experts Exchange.  I have found a couple of approaches but neither of them are working for me.  I have generated the following dummy sample based on Northwind.  My actual program is quite a bit more involved but I get the same problem with this pared down version.

This is my stored proc definition:

    create procedure usp_test as
    select EmployeeID,LastName,FirstName from Employees for xml auto,elements


This is my XSLT (test.xsl):

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output method="html"/>
  <xsl:template match="/">
    <html>
      <body>
        <b>Northwind Employees</b> <br />
        <xsl:for-each select="root/Employees">
          <xsl:value-of select="FirstName" />&#x20;<xsl:value-of select="LastName" />
          is employee number <xsl:value-of select="EmployeeID" />.<br />
        </xsl:for-each>
      </body>
    </html>
  </xsl:template>
</xsl:stylesheet>

I have a local copy of SQL Server on my machine with SQL XML set up to run against the master database.  I can open an IE window and type the following URL:

http://localhost/master?sql=use%20Northwind%20exec%20usp_test&root=root&xsl=template/test.xsl

This returns a page that looks like this:
(nb: lines added to show page boundaries)
------------------------------------------------
Northwind Employees
Nancy Davolio is employee number 1.
Andrew Fuller is employee number 2.
Janet Leverling is employee number 3.
Margaret Peacock is employee number 4.
Steven Buchanan is employee number 5.
Michael Suyama is employee number 6.
Robert King is employee number 7.
Laura Callahan is employee number 8.
Anne Dodsworth is employee number 9.
------------------------------------------------

This is exactly what I am looking to get with my ASP.  However, this is not what I end up with.

Here is the first version of my ASP:
<%
Dim objConn, objStream, objCmd

Set objConn = Server.CreateObject("ADODB.Connection")
Set objStream = Server.CreateObject("ADODB.Stream")
Set objCmd = Server.CreateObject("ADODB.Command")

objConn.Open "PROVIDER=SQLOLEDB;DATA SOURCE=laptop;UID=sa;PWD=;DATABASE=Northwind "
objStream.Charset="windows-1252"
objStream.Open
Set objCmd.ActiveConnection = objConn
objCmd.CommandText="exec usp_test"
objCmd.Properties("Output Stream")=objStream
objCmd.Properties("XML Root")="Employees"
objCmd.Properties("Output Encoding")="iso-8859-1"
objCmd.Properties("XSL")=Server.MapPath("test.xsl")
objCmd.Execute ,,1024      
objCmd.ActiveConnection.Close
objStream.Position=0

Response.Write(objStream.ReadText)

objStream.Close

Set objCmd = Nothing
Set objStream = Nothing
Set objConn = Nothing
%>

When I run this first version, all I get is the static text from the XSLT, namely:
------------------------------------------------
Northwind Employees
------------------------------------------------

Here is the second version of my ASP:

<%
Dim objXMLDoc,objXSLDoc,objXSLTemplate,objXSLProcessor
Dim objConn, objStream, objCmd

Set objXMLDoc = Server.CreateObject("MSXML2.DOMDocument")
objXMLDoc.async = False
objXMLDoc.setProperty "SelectionLanguage", "XPath"
objXMLDoc.resolveExternals = False
objXMLDoc.validateOnParse = False

Set objConn = Server.CreateObject("ADODB.Connection")
Set objStream = Server.CreateObject("ADODB.Stream")
Set objCmd = Server.CreateObject("ADODB.Command")

objConn.Open "PROVIDER=SQLOLEDB;DATA SOURCE=laptop;UID=sa;PWD=;DATABASE=Northwind "
objStream.Charset="windows-1252"
objStream.Open

Set objCmd.ActiveConnection = objConn
objCmd.CommandText="exec usp_test"
objCmd.Properties("Output Stream")=objXMLDoc
objCmd.Properties("XML Root")="Employees"
objCmd.Properties("Output Encoding")="iso-8859-1"
objCmd.Execute ,,adExecuteStream
objCmd.ActiveConnection.Close
objStream.Position=0

Set objXSLDoc = Server.CreateObject("MSXML2.FreeThreadedDomDocument")
objXSLDoc.async = False
objXSLDoc.resolveExternals = False
objXSLDoc.validateOnParse = False
objXSLDoc.load(Server.MapPath("test.xsl"))

Set objXSLTemplate = Server.CreateObject("MSXML2.XSLTemplate")
objXSLTemplate.stylesheet = objXSLDoc
Set objXSLProcessor = objXSLTemplate.createProcessor()
objXSLProcessor.input = objXMLDoc
objXSLProcessor.transform()

Response.Write(objXSLProcessor.output)      '<= LINE "A" (see notes)
' Response.Write(objXMLDoc.xml)      '<= LINE "B" (see notes)

objStream.Close
Set objXMLDoc=Nothing      
Set objXSLDoc=Nothing      
Set objXSLTemplate=Nothing      
Set objXSLProcessor=Nothing      
Set objCmd = Nothing
Set objStream = Nothing
Set objConn = Nothing
%>

When I run the second version I get exactly the same results as with the first version.  However, if I comment out Line A and uncomment Line B (to see the contents of the DOM document) then what I get is a listing of the data from the stored procedure, but without any XML markup.  Instead, the fields are just run together with no delimiters like so:

------------------------------------------------
1DavolioNancy2FullerAndrew3LeverlingJanet4PeacockMargaret5BuchananSteven6SuyamaMichael7KingRobert8CallahanLaura9DodsworthAnne
------------------------------------------------

I should also note that there was a third version, which was much like version 2, except instead of using an ADODB Command object I used an ADODB Recordset object which I persisted to the DOM Document.  This didn't work because the data coming back was garbled.  It appeared to be the wrong encoding since there were lots of unprintable binary characters in it.  Due to this I gave up on the recordset approach in favour of the command approach.

I am really at a loss here.  Your help is very much appreciated!

Joel Brown


0
Comment
Question by:mooseware_ca
  • 2
  • 2
4 Comments
 
LVL 26

Accepted Solution

by:
rdcpro earned 500 total points
ID: 10794656
Instead of:

Response.Write(objXSLProcessor.output)     '<= LINE "A" (see notes)
' Response.Write(objXMLDoc.xml)     '<= LINE "B" (see notes)

use:

objXSLProcessor.output = Response
objXSLProcessor.input = objXMLDoc
objXSLProcessor.transform()

Avoid using Response.Write here, because you can use the IStream interface of the Response object directly, which is safer (encoding is preserved) and faster (no conversion to a string).

Or, a much more simple approach (does not use ADO) is to load your XML directly from the virtual directory.  Create a template sub folder in the Northwind SQL XML virtual directory.  Using the SQL XML configuration snap-in, add the template virtual name to the SQL XML virtual directory, and point it to the new template sub directory you created.  Then put an XML document in there that looks like:

employees.xml
========================
<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
    <sql:query>
        exec usp_test
    </sql:query>
</ROOT>

I would create the virtual folder to point at the DB I want, but if you insist on setting up SQL XML to point at master, you could always add  "use northwind" to the template:

employees.xml
========================
<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
    <sql:query>
        use Northwind
        exec usp_test
    </sql:query>
</ROOT>

You can test this easily with a browser, by opening the template file using HTTP.  On my machine, it would look like:

http://localhost/nwindVirtualRoot/template/employees.xml

Then if your sproc uses FOR XML AUTO, ELEMENTS you should see the XML produced by the sproc.  You can even save a copy to your local drive to use with an XML/XSL IDE to build the XSLT.  

Now all you need to do is instantiate your XML document in the ASP code:

Set objXMLDoc = Server.CreateObject("MSXML2.DOMDocument")
objXMLDoc.async = False
objXMLDoc.setProperty "ServerHTTPRequest", true
objXMLDoc.setProperty "SelectionLanguage", "XPath"
objXMLDoc.resolveExternals = False
objXMLDoc.validateOnParse = False
objXMLDoc.load "http://localhost/nwindVirtualRoot/template/employees.xml"

Set objXSLDoc = Server.CreateObject("MSXML2.FreeThreadedDomDocument")
objXSLDoc.async = False
objXSLDoc.resolveExternals = False
objXSLDoc.validateOnParse = False
objXSLDoc.load(Server.MapPath("test.xsl"))

Set objXSLTemplate = Server.CreateObject("MSXML2.XSLTemplate")
objXSLTemplate.stylesheet = objXSLDoc
Set objXSLProcessor = objXSLTemplate.createProcessor()
objXSLProcessor.output = Response
objXSLProcessor.input = objXMLDoc
objXSLProcessor.transform()

I rather like this approach, but it does require configuring a virtual directory using the SQL XML 3.0 tool.  But you've already done that.  Make sure you use SQL XML 3.0, and not the RTM version that shipped with SQL Server 2000.

Regards,
Mike Sharp
0
 
LVL 1

Author Comment

by:mooseware_ca
ID: 10796550
Mike,

As usual, your response is insightful and thorough.  I have downloaded and applied SQLXML 3.0 SP2.  I have tried making the change you suggested to my second version (replacing Line A/Line B).  I have also tried your SQL XML approach.  I can view a well-formed XML in IE by opening the employees.xml file in my browser.  Unfortunately, when I try it via an ASP, I get a strange result (whether I use my second version or your SQL XML suggestion).  The returned page now looks like this:
--------------------------------------------------------------------------------
ÿþNorthwind Employees

--------------------------------------------------------------------------------

When I view the source, it appears to have extra spaces between the characters like so:
<!--METADATA TYPE="ASP_DEBUG_INFO"
-->

<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">

</HEAD>
<BODY>
ÿþ< b > N o r t h w i n d   E m p l o y e e s < / b > < b r >
<P>&nbsp;</P>

</BODY>
</HTML>

I'm presuming that I must have some kind of configuration issue.  Any idea what it might be?

Regards,
Joel Brown
0
 
LVL 1

Author Comment

by:mooseware_ca
ID: 10800882
Mike,

After fighting with this for a little while longer, I was able to determine that I had two small problems.  The first was in my XSLT where I was specifying the output encoding.  I had to move the encoding directive down to the xsl:output tag.  The second was a pretty boneheaded problem with this line in my ASP:

objCmd.Properties("XML Root")="Employees"

The XSL was looking for "root/Employees", not "Employees/Employees".  It's the dumbest mistakes that always seem to take the longest to find don't they?

Thanks again for your excellent assistance.

Regards,
Joel Brown
0
 
LVL 26

Expert Comment

by:rdcpro
ID: 10801199
Yes, I've seen the situation where you get extra spaces in the output...this is because the output is actually UTF-16, which is double byte, but there is something that's specifying the output in some single byte charset.  So every other byte is an "0x00", which is rendered as a space, since that doesn't represent a character. Using IStream like:

objXSLProcessor.output = Response

usually solves this...but you can also specify UTF-16 in the output via the xsl:output.  Or, by setting the output method to HTML, if there is a <head> tag in the output, MSXML will insert a meta tag identifying the charset as UTF-16.

And yes, sometimes the more obvious the mistake, the harder it is to find!  That's why I llike the SQL XML virtual directory, too, as it allows me to rule out data tier problems entirely!

Regards,
Mike Sharp
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Shoutout to Emily Plummer (http://www.experts-exchange.com/members/eplummer26.html) for giving me this article! She did most of it, I just finished it up and posted it for her :)    Introduction In a previous article (http://www.experts-exchang…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to dynamically set the form action using jQuery.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now