如何从 SQL Server 2005 检索 XML 数据? [英] How to retrieve XML data from SQL Server 2005?

问题描述

我的脚本:

Dim myStream, myConnection, myCommand
Set myStream = CreateObject("ADODB.Stream")
Set myConnection = CreateObject("ADODB.Connection")
Set myCommand = CreateObject("ADODB.Command")
'
myConnection.Open "Provider=SQLOLEDB;Integrated Security=SSPI;" & _
"Persist Security Info=False;Initial Catalog=DSIPAR;Data Source=.\DSIDATA"

myCommand.ActiveConnection = myConnection

myCommand.CommandText = "SELECT itemsgt, item FROM NIFItem"

myStream.Open

myCommand.Properties("Output Stream") = myStream
myCommand.Execute , , adExecuteStream

myStream.Position = 0
myStream.Charset = "ISO-8859-1"

Dim strxml
strxml = myStream.ReadText
MsgBox (strxml)

我可以运行脚本，我可以看到查询在我的 SQL 服务器实例上执行，但没有任何东西返回到输出流.

I can run the script and I can see the query execute on my SQL server instance, but nothing is ever returned to the output stream.

推荐答案

要将结果检索到流中，查询需要包含FOR XML AUTO".还将文本 adExecuteStream 更改为常量值 1024.

To retrieve the results into a stream, the query needs to include "FOR XML AUTO". Also change the text adExecuteStream to the constant value 1024.

完整代码:

Dim myStream, myConnection, myCommand
Set myStream = CreateObject("ADODB.Stream")
Set myConnection = CreateObject("ADODB.Connection")
Set myCommand = CreateObject("ADODB.Command")
myConnection.Open "Provider=SQLOLEDB;Integrated Security=SSPI;" & _
"Persist Security Info=False;Initial Catalog=DSIPAR;Data Source=.\DSIDATA"
myCommand.ActiveConnection=myConnection

myCommand.CommandText="SELECT itemsgt,item FROM NIFItem FOR XML AUTO"

myStream.Open
myCommand.Properties("Output Stream") = myStream
myCommand.Properties("xml root") = "root"

myCommand.Execute ,,1024 'instead of adExecuteStream
myStream.Position=0
myStream.Charset="ISO-8859-1"
Dim strxml
strxml = myStream.ReadText
MsgBox (strxml)

如果您不需要流，而是想读取 itemsgt 和 item 的值，请尝试以下操作:

If you don't need a stream and instead want to read the values of itemsgt and item, try this instead:

Dim myStream, myConnection, myCommand, adoRec
Set myStream = CreateObject("ADODB.Stream")
Set myConnection = CreateObject("ADODB.Connection")
Set myCommand = CreateObject("ADODB.Command")
myConnection.Open "Provider=SQLOLEDB;Integrated Security=SSPI;" & _
"Persist Security Info=False;Initial Catalog=DSIPAR;Data Source=.\DSIDATA"
myCommand.ActiveConnection=myConnection
myCommand.CommandText="SELECT itemsgt,item FROM NIFItem"
SET adoRec = myCommand.Execute()
'Get the first results
If Not adoRec.EOF then
  MsgBox "itemsgt = "  & adoRec(0) & vbcrlf & "item="  &  adoRec(1)
End If

'Iterate through the results
While Not adoRec.EOF
  itemsgt = adoRec(0)
  item = adoRec(1)
  'MsgBox "itemsgt = " & itemsgt & vbcrlf & "item="  & item
  adoRec.MoveNext
Wend

这篇关于如何从 SQL Server 2005 检索 XML 数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！