Error passing XML value to SQL Server store procedure

• penphirom

  Grasshopper

  Points: 18

  More actions

  December 18, 2010 at 6:12 am

  #232545

  When I pass the xmlDataValue to SQL server 2005 the error message show me that "No mapping exists from object type System.Xml.XmlDataDocument to a known managed provider native type"

  This is my store procedure:

  CREATE PROC SaveToT1 @xml xml

  AS

  INSERT INTO dbo.T1(F1,F2)

  SELECTxmlVals.rowvals.query('StaffID').value('.','nchar(3)'),

  xmlVals.rowvals.query('StaffName').value('.','nvarchar(30)')

  FROM @xml.nodes('/R1/dbo.StaffInformation') as xmlVals(rowvals)

  My VB.NET Code is:

  Public Sub SaveToT1()

  Dim ds As New DataSet("R1")

  Dim adp As New OdbcDataAdapter

  Dim cmd As New OdbcCommand

  lasspCmd.Connection = lasspConn

  lasspCmd.CommandText = "SELECT * FROM dbo.StaffInformation"

  adp.SelectCommand = lasspCmd

  adp.Fill(ds, "dbo.StaffInformation")

  Dim doc As New XmlDataDocument(ds)

  cmd.Connection = lasspConn

  cmd.CommandText = "{CALL SaveToT1(?)}"

  cmd.CommandType = CommandType.StoredProcedure

  cmd.Parameters.AddWithValue("@xml", doc)

  cmd.ExecuteNonQuery()

  lasspCmd.Dispose()

  cmd.Dispose()

  End Sub

  How do I solve this Error?

  Note: My connection I use odbcConnection with DSN Name