Error passing XML value to SQL Server store procedure

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQLServerCentral.com » Suggestions » Error passing XML value to SQL Server store...

Error passing XML value to SQL Server store procedure

Rate Topic

Display Mode

Topic Options

Author

Message

penphirom

Posted Saturday, December 18, 2010 6:12 AM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, December 18, 2010 6:12 AM
Points: 2, Visits: 5

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)
SELECT xmlVals.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

Post #1036872

« Prev Topic | Next Topic »

Permissions