SQL server 的 for xml 子句

      数据库相关 2004-1-15 14:1
SQL server 有for xml子句可以导出为xml数据 以下为ASP中使用ADO的一个应用,连接必须指定为SQL OLEDB
'建立conn连接
Dim conn
Set conn = CreateObject("ADODB.Connection")
conn.Provider = "SQLOLEDB"
conn.Open "server=192.168.1.32; database=databasename; uid=sa; pwd=****; "
conn.Properties("SQLXML Version") = "SQLXML.2.0"

'新建Command对象并指定链接字符串
Dim objCmd,source
Set objCmd = CreateObject("ADODB.Command")
objCmd.ActiveConnection = conn

'指定查询schema
strQry = _
"<Date xmlns:sql='urn:schemas-microsoft-com:xml-sql'>"
strQry = strQry & "<sql:query>"
strQry = strQry & "SELECT * FROM TableName FOR XML RAW"
strQry = strQry & "</sql:query></Date>"

'给 Command 对象指定查询模板
objCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"
objCmd.CommandText = strQry

'创建 Stream 对象
Dim objStrmResults
Set objStrmResults = CreateObject("ADODB.Stream")
objStrmResults.Open
objCmd.Properties("Output Stream") = objStrmResults

'执行查询并处理结果
objCmd.Execute, , 1024 '(1024 指定以Stream方式传回数据而不是以Recordset类型)
Dim strXML
strXML = objStrmResults.ReadText
Response.write strXML
标签集:TAGS:
回复Comments() 点击Count()

回复Comments

{commentauthor}
{commentauthor}
{commenttime}
{commentnum}
{commentcontent}
作者:
{commentrecontent}
}