从xml插入到sql服务器表
本文关键字:服务器 sql xml 插入 | 更新日期: 2023-09-27 18:22:14
我有以下XML文件:
<test>
<test2>
<A>206942</A>
</test2>
<test2>
<A>203405</A>
</test2>
</test>
我需要插入SQL Server表:
XmlNodeList dataNodes = xmlDoc.SelectNodes("/test/test2");
SqlConnection dbConnection = new SqlConnection(connectionString);
try
{
dbConnection.Open();
foreach (XmlNode node in dataNodes)
{
String A = (node.SelectSingleNode("A") != null) ?
node.SelectSingleNode("A").InnerText.ToString() : string.Empty;
try
{
using (SqlCommand cmd = dbConnection.CreateCommand())
{
cmd.Parameters.AddWithValue(" @A", A);
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
我尝试上面的代码,我总是得到以下错误:
Incorrect syntax near @A Must declare the scalar variable @A
如何解决此问题?
将XML传递到SQL Server并从那里进行处理可能会更简单。
您只需要一个接受xml值作为参数的存储过程:
CREATE PROCEDURE [InsertXMLValuesToMyTable]
@XML xml
AS
BEGIN...
在存储过程中,您可以操作xml来提取值并执行INSERT
,如下所示。
演示SQL Fiddle
DECLARE @XML xml =
'<test>
<test2>
<A>206942</A>
</test2>
<test2>
<A>203405</A>
</test2>
</test>';
-- this will convert the values into rows and add them to a temp table
SELECT T.r.value('.','int') IdsToInsert
INTO #TMP
FROM @XML.nodes('test/test2/A') as T(r)
-- mock object to insert into
CREATE TABLE #TMP2 (ID int)
-- insert values from the first table into your destination table
INSERT INTO #TMP2
SELECT IdsToInsert
FROM #TMP
-- show the output
SELECT *
FROM #TMP2
DROP TABLE #TMP
DROP TABLE #TMP2