1.you should install SQLXML3.0
2.IN SQL SERVER2K YOU CAN SEE sp_xml_preparedocument/sp_xml_removedocument.
3.SQLXML3.0 HAVE A LOT OF EXAMPLE.
FOR EXAMPLE:
Option Explicit
Sub main()
Dim oTestStream As New ADODB.Stream
Dim oTestConnection As New ADODB.Connection
Dim oTestCommand As New ADODB.Command
oTestConnection.Open "provider=SQLXMLOLEDB.3.0;data provider=SQLOLEDB;data source=(local);initial catalog=Northwind;user id=UserName;password=UserPassword;"
oTestCommand.ActiveConnection = oTestConnection
oTestCommand.Properties("ClientSideXML") = "True"
oTestCommand.CommandText = _
"
1<root xmlns:sql="urn:schemas-microsoft-com:xml-sql">" & _
2" <sql:query> " & _
3" SELECT FirstName, LastName FROM Employees FOR XML AUTO " & _
4" </sql:query> " & _
5" </root>
"
oTestStream.Open
' You need the dialect if you are executing a template.
oTestCommand.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}"
oTestCommand.Properties("Output Stream").Value = oTestStream
oTestCommand.Properties("Base Path").Value = "c:\Schemas\SQLXMLWR2\New Folder\ExecuteTemplateWithXSL"
oTestCommand.Properties("xsl").Value = "myxsl.xsl"
oTestCommand.Execute , , adExecuteStream
oTestStream.Position = 0
oTestStream.Charset = "utf-8"
Debug.Print oTestStream.ReadText(adReadAll)
End Sub
Sub Form_Load()
main
End Sub
The XSL template follows. The result of applying this XSL template is a two-column table.
1<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
2<xsl:template match="Employees">
3<tr>
4<td><xsl:value-of select="@FirstName"></xsl:value-of></td>
5<td><b><xsl:value-of select="@LastName"></xsl:value-of></b></td>
6</tr>
7</xsl:template>
8<xsl:template match="/">
9<html>
10<head>
11<style>th { background-color: #CCCCCC }</style>
12</head>
13<body>
14<table border="1" style="width:300;">
15<tr><th colspan="2">Employees</th></tr>
16<tr><th>First name</th><th>Last name</th></tr>
17<xsl:apply-templates select="ROOT"></xsl:apply-templates>
18</table>
19</body>
20</html>
21</xsl:template>
22</xsl:stylesheet>