如何使用SQL Server存储过程将XML数据保存到表中

本文关键字：保存 数据 XML 何使用 SQL Server 存储过程 | 更新日期: 2023-09-27 18:12:08

我正在尝试使用存储过程将xml数据存储在我的数据库表中，但数据不保存，我不知道如何做到这一点。

我的XML是

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

存储过程是

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

和这里的c#代码

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)
{
}

如何使用SQL Server存储过程将XML数据保存到表中

试试这个存储过程(使用SQL Server 2005 XQuery代替旧的OpenXML代码):

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

这个对你有用吗?

最简单的方法是将XML数据写入字节并将其保存到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
相关文章：