Category: XML
SQL Server to Data Island
I am getting data out of SQL Server 2000 using xml in an asp. How do I now create a data island which will allow the users to update the description field and post back to the DB.
Here is my ASP code:
<%
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=(local); Initial Catalog=XMLData; UId=sa; Pwd="
objComm.ActiveConnection = objConn
objComm.CommandType = adCmdStoredProc
objComm.CommandText = "sp_GetExplicitXML"
objStream.Open
objComm.Properties("Output Stream").Value = objStream
objComm.Execute ,, adExecuteStream
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
%>
Here is my xsl page:
<?xml version="1.0" ?><xsl:stylesheet xmlns:xsl="http://www.w3.org/TR/WD-xsl">
<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><p style="margin-left:30"><xsl:value-of select="@Description"/></p></td></tr>
</xsl:for-each>
</xsl:for-each>
</table>
</body>
</html>
</xsl:template></xsl:stylesheet>
At the moment I get the correct data in a table format, but I want more
Any help much appreciated !!!