.NET可以作为XML将SQL数据库作为SQL Server存储过程

Ok. So I am pretty new a this. I have a datatable that I want to pass to a stored procedure for further manipulation. I have read some stuff on the web and it looks like I should be able to convert the datatable to XML and then pass that to the stored procedure. What am I doning wrong? I have SQL server 2005. The data never gets passed to the stored procedure.

好。所以我很陌生。我有一个数据表,我想传递给存储过程进行进一步操作。我已经在Web上阅读了一些内容,看起来我应该能够将数据表转换为XML,然后将其传递给存储过程。我做错了什么?我有SQL Server 2005.数据永远不会传递给存储过程。

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

2 个解决方案

#1

In the past I have done something similar but with SQL 2000. In SQL 2000 there was no XML data type so I had to receive the output from DataSet.GetXML via a ntext parameter into the stored procedure then processed it using sp_xml_preparedocument and sp_xml_removedocument, so the process should work for you.

在过去,我做过类似的事情,但是使用SQL 2000.在SQL 2000中没有XML数据类型,所以我必须通过ntext参数将DataSet.GetXML的输出接收到存储过程中,然后使用sp_xml_preparedocument和sp_xml_removedocument处理它,所以这个过程对你有用。

It may be that you do not need to load an XmlDocument with the text and can just pass the xml text as is to the stored procedure.

可能您不需要使用文本加载XmlDocument,只需将xml文本原样传递给存储过程即可。

#2

OK. This is what I go to work. I don't know if this is the best way, but this 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

#1