ADODB XML datatype

I need to pass an xml document to a SQL 2005 stored procedure as a parameter with a XML data type.  I'm using VBScript with ADODB.  I was getting the error of "Bad variable type."

The problem is the Provider.  The SQL Native Client provider uses an Xml type ( 141)  that ADODB doesn't recognize.  The OLEDB provider converts the xml type to varWChar.

 

To set things up here's a sample sql stored proc

create proc stor_PassXml @xmlVar xml
as
select @xmlVar
end

So here's the VBScript to call it

Dim conStr
Dim sqlConn
DIm sqlCmd
Dim myXml
dim bWillError

if bWillError then
conStr = "Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master;Data Source=localhost"
else
conStr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master;Data Source=localhost"
end if

myXml = "<root><someElem/><someElem/><someElem/></root>"

Set sqlConn= CreateObject("ADODB.Connection")
Set sqlCmd = CreateObject("ADODB.Command")
sqlConn.open constr

sqlCmd.ActiveConnection = sqlConn
sqlCmd.CommandText = "stor_PassXml"
sqlCmd.CommandType = 4' adCmdStoredProc
sqlCmd.NamedParameters = True
sqlCmd.Parameters.Refresh
sqlCmd.Parameters("@xmlVar").Value = "" & myXml
sqlCmd.Execute

sqlConn.close

 

Update:  I did some more testing and the xmldata can be passed by explicitly setting the parameter

Dim param
Set param = e1Cmd.CreateParameter("@xmlVar", adVarWChar, adParamInput, Len(myXml),myXml)
e1Cmd.Parameters.Append param

 

 

 

 

Print | posted on Friday, April 17, 2009 12:55 PM