vbscript - 如何从SQL Server 2005检索XML数据？

Question

我的剧本:

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 Server实例上执行，但是什么都没有返回到输出流。

Answer 1

要将结果检索到流中，查询需要包括“FOR XML AUTO”。另外，将文本adExecuteStream更改为常量值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的值，请尝试以下方法:

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