如何使用SQL Server存储过程在表中保存XML数据
问题描述:
我试图使用存储过程在数据库表中存储xml数据,但数据未保存,我不知道如何才能做到这一点..如何使用SQL Server存储过程在表中保存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 2005,而不是旧的遗留OpenXML
代码的XQuery):
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
这是否对你的工作?
+0
为什么'(@ID)[1]'而不是'@ ID'? –
+0
@RubensFarias:你是对的 - 对于属性,''[1]'是甚至不需要 - 谢谢指出!更新我的答案 –
答
这里是最简单的做法是将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
它的商店* d *程序 - 如** **存储在SQL Server中 - 它有没有与“商店* ... –