How do I create an odbcparameter for a sql server xml datatype for an odbccommand object?

Question

So I've got a situation where I need to pass a bunch of data to the server to create a bunch of records. So I read this article and chose the XML method. So I've got the stored procedure all created in sql server 2005. But now I need to call it from asp.net code. Only all I've got for a database connection is an odbcconnection. I see the sqlclient seems to have a native sqldbtype.xml. What do I do if I'm using odbc? Am i SOL?

Answer 1

I think you should use SQLConnection & related classes.

Having said that, you can use VS.net Server Explorer, make a connection to SQL Server using ODBC, expand the tree with stored procedures and see your stored procedure with XML parameter.

It should show the ODBC data type for it when you do right click, properties on the XML parameter.

This is purely based on my memory. I think, this should work.

EDIT: See if this link helps. See for DataTypeCompatibility word on this link.

Answer 2

Turns out I can use the implicit varchar/ntext to XML conversion. I tried both Varchar and NText - they both worked. The conversion the other way doesn't support implicit conversion, you have to do explicit conversion according to the docs. Anyway, here is an example function and the stored procedure it is calling.

ADO.Net Code

Private Sub cmdXMLTest_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdXMLTest.Click

   Dim objConnection As System.Data.Odbc.OdbcConnection
   Dim cmdTest As System.Data.Odbc.OdbcCommand
   Dim drTest As System.Data.Odbc.OdbcDataReader
   Dim intCount As System.Int32
   Dim strMatter As System.String = ""
   Dim strXML As System.String = "<Root><Matter mmatter=""11415.09951"" /><Matter mmatter=""06625.06771"" /><Matter mmatter=""00931.09846"" /></Root>"

   ' build odbc command for getting parties
   cmdTest = New System.Data.Odbc.OdbcCommand
   cmdTest.CommandType = CommandType.StoredProcedure
   cmdTest.CommandTimeout = 60
   cmdTest.CommandText = "{CALL uspXMLTest (?)}"
   cmdTest.Parameters.Add("@list", System.Data.Odbc.OdbcType.NText).Value = strXML

   objConnection = New System.Data.Odbc.OdbcConnection("Driver={SQL Server};Server=mysqlserver;Database=son_db;Uid=sa;Pwd=mypassword;")
   objConnection.Open()
   cmdTest.Connection = objConnection
   drTest = cmdTest.ExecuteReader(CommandBehavior.CloseConnection)

   Do While drTest.Read()

      strMatter = drTest.GetString(0)
      intCount = intCount + 1

   Loop

   MsgBox("Found " & intCount.ToString() & " Matters - Last One = " & strMatter)

End Sub

Stored Procedure

CREATE PROCEDURE uspXMLTest
    @list xml
AS

SET NOCOUNT ON

SELECT matter.mmatter, matter.mdesc1
FROM matter
     INNER JOIN @list.nodes('/Root/Matter') AS ml(mmatter) ON (matter.mmatter = ml.mmatter.value('@mmatter', 'varchar(15)'))

GO

Answer 3

For those using XMLTextWriter to create the XML you need to use UTF-16 encoding (Unicode in .net). So here is the code for creating the XML.

  ' create xml file for folders
  strmFolderList = New System.IO.MemoryStream()
  wrtFolderList = New System.Xml.XmlTextWriter(strmFolderList, System.Text.Encoding.Unicode)

  ' start document and add root element
  wrtFolderList.WriteStartDocument()
  wrtFolderList.WriteStartElement("Root")

  ' cycle through folders
  For intIndex = 0 To m_intAdditionalFolderQuantity - 1

     ' figure out folder description
     strFolderDesc = m_arrFolderDesc(intIndex)

     ' add element to xml
     wrtFolderList.WriteStartElement("Folder")
     wrtFolderList.WriteAttributeString("folderdesc", strFolderDesc)
     wrtFolderList.WriteAttributeString("insertfolder", ((intIndex + 1) * -1).ToString())
     wrtFolderList.WriteEndElement()

  Next

  ' close root element and document
  wrtFolderList.WriteEndElement()
  wrtFolderList.WriteEndDocument()
  wrtFolderList.Close()
  'System.Text.Encoding.Unicode.GetString(strmFolderList.ToArray())

And here is the code to call the stored procedure

  cmdAddRequest = New System.Data.Odbc.OdbcCommand
  cmdAddRequest.CommandType = CommandType.StoredProcedure
  cmdAddRequest.CommandTimeout = 60
  cmdAddRequest.CommandText = "{CALL uspAddRequest ( ?, ?, ?, ?, ?, ?, ?, ?, ?)}"

  ' add parameters to odbc command
  cmdAddRequest.Parameters.Add("@ruserid", OdbcType.VarChar, 8).Value = SafeODBCParamString(m_strUID)
  cmdAddRequest.Parameters.Add("@rmatter", OdbcType.VarChar, 15).Value = SafeODBCParamString(m_strMatterNumber)
  cmdAddRequest.Parameters.Add("@req_tkinit", OdbcType.VarChar, 8).Value = SafeODBCParamString(m_strRequesting)
  cmdAddRequest.Parameters.Add("@ret_tkinit", OdbcType.VarChar, 8).Value = SafeODBCParamString(m_strReturnNumber)
  cmdAddRequest.Parameters.Add("@requestor", OdbcType.VarChar, 20).Value = SafeODBCParamString(m_strRequestor)
  cmdAddRequest.Parameters.Add("@labels_only", OdbcType.Bit).Value = m_blnLabelsOnly
  cmdAddRequest.Parameters.Add("@folder_quantity", OdbcType.SmallInt).Value = m_intAdditionalFolderQuantity
  cmdAddRequest.Parameters.Add("@folder_list", OdbcType.NText).Value = System.Text.Encoding.Unicode.GetString(strmFolderList.ToArray())
  cmdAddRequest.Parameters.Add("@insert_list", OdbcType.NText).Value = System.Text.Encoding.Unicode.GetString(strmInsertList.ToArray())