How to get Data from Dataset into Sqlserver Table by using OPENXML Method?

Some time we need data from Dataset into Sqlserver for insertion or updation in the Sqlserver Tables.

For example if you need to insert an image then you can use this method to insert the data in Sqlserver tables.

Today, I will explain how to get data from Dataset into Sqlserver Table.

— Code for .Net

Dim ds as new Dataset

Dim dt as new Table

dt.TableName = “MyTable”

ds.Tables.add(dt)

Dim lStrWriter As New StringWriter

Dim lStr As String

ds.WriteXML(lStrWriter)

lstr = lStrWriter.Tostring

Pass this lstr variable to the procedure as parameter – @xmldoc.

Now write the code procedure to get the data from Dataset.

— Code in Sqlserver Store Procedure

CREATE PROCEDURE [dbo].[TestXML]

@xmldoc text — Give Text datatype for getting xml from dataset

as

———Read the Datset  From XML —————–

— call sp_xml_preparedocument

DECLARE @idoc int

EXEC sp_xml_preparedocument @idoc OUTPUT, @xmldoc

SELECT * into #TestTable

FROM OPENXML (@idoc, ‘/NewDataSet/MyTable’,2)

WITH (pk_Userid  BigInt,

UserName  Varchar(10))

EXEC sp_xml_removedocument @idoc

———- End Read ———-

END

BY the use of this procedure you get all the data from dataset in to the “#Testtable” and we can use this as Sqlserver table in procedure.

I hope this article will be useful to you, Please give feedback on this article.