How to save XML data in table using SQL Server stored procedure

Question

I am trying to store xml data in my database table using a stored procedure but data is not saving, I am not getting how can I do this..

My XML is

<?xml version="1.0" encoding="utf-16"?>
<Users>
  <User ID="11005477969327">6/3/2011</User>
  <User ID="11034688201594">5/18/2011</User>
</Users>

My stored procedure is

Alter PROCEDURE [ProcessMailNotificationSentToUsers]
@User_XML XML
AS
BEGIN
DECLARE @hdoc     int
DECLARE @doc      varchar(2000)

SET @doc = ''
EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc

--OPEN XML example of inserting multiple customers into a Table.
INSERT INTO PasswordExpiryNotificationLog (UserId)
SELECT UserId FROM OPENXML (@hdoc, '/Users/User',2)
WITH(
 UserId bigint
)

EXEC sp_xml_removedocument @hdoc
END

And my c# code here

SqlParameter[] arrParam = new SqlParameter[1];
try
{
   SqlConnection objConn = new SqlConnection(GetConnection());
   string strProc = "ProcessMailNotificationSentToUsers";

   arrParam[0] = new SqlParameter("@User_XML", SqlDbType.Xml);
   arrParam[0].Value = userXML;

   SqlHelper.ExecuteNonQuery(objConn, CommandType.StoredProcedure, strProc, arrParam);
}
catch (Exception ex)
{
}

It's storeD procedure - as stored in SQL Server - it has nothng to do with a "store* ... — marc_s
– marc_s, Commented Oct 22, 2011 at 10:00

marc_s · Accepted Answer · 2011-10-22 12:19:48Z

1

Try this stored procedure instead (using SQL Server 2005 XQuery instead of the old legacy OpenXML code):

ALTER PROCEDURE dbo.ProcessMailNotificationSentToUsers
    @User_XML XML
AS
BEGIN

    INSERT INTO dbo.PasswordExpiryNotificationLog (UserId)
        SELECT 
      Tbl.Col.value('@ID', 'bigint')
        FROM
           @User_XML.nodes('/Users/User') AS Tbl(Col)
END

Does this work for you?

edited Oct 22, 2011 at 12:19

answered Oct 22, 2011 at 10:03

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rubens Farias Over a year ago

Why (@ID)[1] instead of @ID?

marc_s Over a year ago

@RubensFarias: you're right - for an attribute, that [1] is not even needed - thanks for pointing out! Updated my answer

DareDevil · Accepted Answer · 2013-11-11 12:08:46Z

Here is the Simplest Way to do is to write XML data into bytes and save them into a field in SQLServer

//One By One Function
Sub Main()
    Dim dsData As DataSet = GetDataSet()
    Dim xmlData As [String] = ConvertDataTableToXML(dsData.Tables(0))
    Dim barray() As Byte = System.Text.Encoding.ASCII.GetBytes(xmlData)
    Dim byteconstructedstring As String = System.Text.ASCIIEncoding.ASCII.GetString(barray)
    Dim xmltable As DataTable = stringxmltods(byteconstructedstring)

End Sub
//GetDataSet.....
Private Function GetDataSet() As DataSet
    Dim ds As New DataSet()
    Dim dt As New DataTable("Test")

    dt.Columns.Add("id", Type.[GetType]("System.Int64"))
    dt.Columns.Add("Name", Type.[GetType]("System.String"))
    dt.Columns.Add("Description", Type.[GetType]("System.String"))
    dt.Columns.Add("Qty", Type.[GetType]("System.Int64"))

    Dim dr As DataRow = dt.NewRow()
    dr("id") = 1
    dr("Name") = "Red Stone"
    dr("Description") = "Stones"
    dr("Qty") = "10"
    dt.Rows.Add(dr)

    dr = dt.NewRow()
    dr("id") = 2
    dr("Name") = "Blue Stone"
    dr("Description") = "Stones"
    dr("Qty") = "60"
    dt.Rows.Add(dr)

    dr = dt.NewRow()
    dr("id") = 3
    dr("Name") = "Marbell"
    dr("Description") = "Stones"
    dr("Qty") = "6"
    dt.Rows.Add(dr)

    dr = dt.NewRow()
    dr("id") = 4
    dr("Name") = "Graynite"
    dr("Description") = "Hard Stones"
    dr("Qty") = "60"
    dt.Rows.Add(dr)
    ds.Tables.Add(dt)
    Return ds
End Function
//Get Table to XML
Private Function ConvertDataTableToXML(ByVal dtData As DataTable) As String
    Dim dsData As New DataSet()
    Dim sbSQL As StringBuilder
    Dim swSQL As StringWriter
    Dim XMLformat As String
    Try
        sbSQL = New StringBuilder()
        swSQL = New StringWriter(sbSQL)
        dsData.Merge(dtData, True, MissingSchemaAction.AddWithKey)
        dsData.Tables(0).TableName = "SampleDataTable"
        For Each col As DataColumn In dsData.Tables(0).Columns
            col.ColumnMapping = MappingType.Attribute
        Next
        dsData.WriteXml(swSQL)
        XMLformat = sbSQL.ToString()
        Return XMLformat
    Catch sysException As Exception
        Throw sysException
    End Try
End Function
//stringxmltods
Public Function stringxmltods(ByVal xmlstring As String) As DataTable
    Dim theReader As New StringReader(xmlstring)
     Dim theDataSet As New DataSet()
     theDataSet.ReadXml(theReader)
     Return theDataSet.Tables(0)
 End Function

Collectives™ on Stack Overflow

How to save XML data in table using SQL Server stored procedure

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related