Skip to content

Updating a SQL table with XML generated from a datatable in a .NET application.

December 23, 2011
    '###################
    ' VB
    '###################
    'Datatable must have Test in the example
    Function SendXML(ByVal Field1 As String, ByRef dt As DataTable) As Boolean
        Try
            Using ds As New DataSet("ROOT")
                ds.Tables.Add(dt)
                Using sw As New IO.StringWriter()
                    ds.WriteXml(sw)
                    Using cmd As New SqlClient.SqlCommand("spNetToXML", New SqlClient.SqlConnection(My.Settings.MyConn)) With {.CommandType = CommandType.StoredProcedure}
                        cmd.Parameters.AddWithValue("@Field1", Field1)
                        cmd.Parameters.AddWithValue("@XML", sw.ToString.Replace("", String.Empty))
                        cmd.Connection.Open()
                        cmd.ExecuteNonQuery()
                        cmd.Connection.Close()
                    End Using
                End Using
            End Using
            SendXML = True
        Catch ex As Exception
        End Try
    End Function
--###################
-- SQL
--###################
CREATE PROCEDURE [dbo].Spnettoxml
    @Field1 AS VARCHAR(50) ,
    @XML AS VARCHAR(MAX)
AS 
    DECLARE @HDOC INT 

    EXEC Sp_xml_preparedocument @HDOC OUTPUT, @XML 

    SELECT  Field1, Field2, Field3
    INTO    #temp
    FROM    OPENXML (@HDOC, '/ROOT/Test', 2) 
              WITH ( Field1 VARCHAR(10), 
                     Field2 BIGINT, 
                     Field3 INT ) XMLFILEDETAILS 

    IF EXISTS ( SELECT  *
                FROM    Table1
                WHERE   Field1 = @Field1 ) 
        BEGIN 
            UPDATE  Table1
            SET     Field2 = #temp.Field2, Field3 = #temp.Field3
            FROM    Table1
                    INNER JOIN #temp ON #temp.Field1 = Table1.Field1 
        END 
    ELSE 
        BEGIN 
            INSERT  INTO Table1 ( Field1, Field2, Field3 )
                    SELECT  Field1, Field2, Field3
                    FROM    #temp 
        END 

    DROP TABLE #temp 

    EXEC Sp_xml_removedocument @HDOC
Advertisements

From → .Net, SQL

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: