retrieve data from SQL server wih XML

Thebravehearth · October 6th, 2004, 03:53 AM

Hi all...

i'm newbie here..:)
yesterday i tried, to retrieve data from db SQL Server with XML.
this is the the code ... (prodtest.asp)

<%@ Language=VBScript%>
<%Server.ScriptTimeout=21478836%>
<%Response.Buffer=false%>
<%

dim objStream
dim objConn
dim objComm
dim objXML

set objStream = Server.CreateObject("ADODB.Stream")
set objConn = Server.CreateObject("ADODB.Connection")
set objComm = Server.CreateObject("ADODB.Command")
set objXML = Server.CreateObject("MSXML2.DOMDocument")

objConn.Open "Provider=SQLOLEDB; Data Source=intradka2; Initial Catalog=myProducts; UId=user; Pwd="
objComm.ActiveConnection = objConn

objComm.CommandText = "exec [dbo].sp_GetExplicitXML"
objStream.Open
objComm.Properties("Output Stream").Value = objStream
objComm.Execute , , 1024 'adExecuteStream = 1024
objStream.Position = 0
objXML.LoadXML("<?xml version='1.0'?><?xml-stylesheet type='text/xsl' href='prodtest.xsl'?><My_Products>" & objStream.ReadText & "</My_Products>")
if objXML.parseError.errorCode <> 0 then
Response.Write "Error loading XML: " & objXML.parseError.reason
Response.End
end if
Response.ContentType = "text/xml"
Response.Write objXML.xml

   Set objComm = Nothing
   objConn.Close
   Set objConn = Nothing

%>

[blue] and the XSL file is [/bue]

<?xml version="1.0" ?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/xsl/TRANSFORM">
<xsl:template match="/">
<html>
<head>
<title> Sample Products </title>
</head>
<body>
<h1>Books in Catalog</h1>
<table border="0" cellspacing="2" cellpadding="3">
<xsl:for-each select="My_Products/Category">
<tr>
<td width="100%" bgcolor="#C0FFC0">
<xsl:value-of select="@CatName"/>
</td>
</tr>
<xsl:for-each select="Product">
<tr>
<td width="100%" bgcolor="#E9E9E9">
<p style="margin-left:30"><xsl:value-of select="@ProdName"/></p>
</td>
</tr>
</xsl:for-each>
</xsl:for-each>
</table>
</body>
</html>
</xsl:template>
</xsl:stylesheet>

if i run this page with IE so i dont get the record
and if i run this page with MOzilla, i get error like this
size=3]ERROR LOADING STYLESHEET : PARSING AN XSLT STYLESHEET FAILED[/SIZE]

so... what's the matter in code.....

thx 4 ur advice

<b>~TAT TWAM ASI~</b>

joefawcett · October 6th, 2004, 07:23 AM

Difficult to debug this. What is returned by the command if you just display it without tring to add the processing instruction.
I would load the xml returned by the server and transform it via the stylesheet on the server before sending it to browser. If you must use the linked stylesheet approach then add the PI dynamically using the DOM and then save the result to the Response stream:

Code:

Response.ContentType = "text/xml" 
objXML.save Response

--

Joe (Co-author Beginning XML, 3rd edition)

pgtips · October 6th, 2004, 08:49 AM

I agree with Joe, do your transform at the server-side.

For info you can retrieve the XML from SQL Server directly into an XML DOM object, there's no need to go via an ADO Stream. Instead of this code:

objStream.Open
objComm.Properties("Output Stream").Value = objStream
objComm.Execute , , 1024 'adExecuteStream = 1024
objStream.Position = 0
objXML.LoadXML("<?xml version='1.0'?><?xml-stylesheet type='text/xsl' href='prodtest.xsl'?><My_Products>" & objStream.ReadText & "</My_Products>")

You would need:

objComm.Properties("Output Stream").Value = objXML
objComm.Properties("XML Root") = "My_Products"
objComm.Execute , , 1024 'adExecuteStream = 1024

hth
Phil

Thebravehearth · October 7th, 2004, 02:07 AM

thank alot my friends..

i gets ur ide...
i can solve this problem know .... :)

see you in another question ....