.NET datatable for SQL Server stored procedure as XML
Ok. So I'm pretty new. I have data that I want to pass to a stored procedure for further manipulation. I've read some stuff on the internet and it looks like I have to convert the data to XML and pass it to a stored procedure. What am I doing wrong? I have SQL Server 2005. Data is never passed to a stored procedure.
Sub Test()
Dim dt As New DataTable
Fill datatable code omitted. there are 150 rows in the datatable after this
Dim ds As New DataSet
ds.Tables.Add(dt)
Dim x As XmlDocument
x.LoadXml(dt.DataSet.GetXml)
Dim ta As New dsTestTableAdapters.TESTRxTableAdapter
ta.ProcessFile(x)
End Sub
The stored procedure looks like this:
ALTER PROCEDURE [dbo].[ProcessFile]
(
@x XML
)
AS
BEGIN
'DO STUFF HERE
END
source to share
OK. This is where I'm going. I don't know if this is the best, but it works for me.
Sub Test
Dim ds as new DataSet ("Testds")
Dim dt as New DataTable("Testdt")
'Fill Datatable code omitted
Dim ta as new dsTest.TestTableAdapter
'TableAdapter returns a datatable to ensure that the stored procedure worked
ta.AddDataToDB(dt,ds.GetXML)
Me.DataGridView1.DataSource=dt
End Sub
The stored procedure looks like this.
CREATE PROCEDURE [dbo].[AddDataToDB]
(
@x XML
)
AS
BEGIN
DECLARE @data TABLE (
TestCol1 VARCHAR(50),
[TestCol2] VARCHAR(50),
[TestCol3] VARCHAR(50),
)
INSERT INTO @data (
[TestCol1],
[TestCol2],
[TestCol3]
)
SELECT
xmlVals.rowvals.query('TestCol1').value('.','VARCHAR(50)'),
xmlVals.rowvals.query('TestCol2').value('.','VARCHAR(50)'),
xmlVals.rowvals.query('TestCol3').value('.','VARCHAR(50)')
FROM
@x.nodes('/Testds/Testdt') as xmlVals(rowvals)
SELECT * FROM @data
END
source to share
I've done something similar in the past, but since SQL 2000. There was no XML datatype in SQL 2000, so I had to get the output from DataSet.GetXML through the ntext parameter into the stored procedure and then process it with sp_xml_preparedocument and sp_xml_removedocument, so the process should work for you.
You may not need to load the XmlDocument with text and just pass the XML text as you would to a stored procedure.
source to share