如何将 XML 从 C# 传递到 SQL Server 2008 中的存储过程? [英] How to pass XML from C# to a stored procedure in SQL Server 2008?
问题描述
我想将xml文档传递给sql server存储过程,例如:
I want to pass xml document to sql server stored procedure such as this:
CREATE PROCEDURE BookDetails_Insert (@xml xml)
我想将一些字段数据与其他表数据进行比较,如果匹配,则必须将记录插入到表中.
I want compare some field data with other table data and if it is matching that records has to inserted in to the table.
要求:
如何将 XML 传递给存储过程?我试过了,但它不起作用:[工作]
command.Parameters.Add(
new SqlParameter("@xml", SqlDbType.Xml)
{
Value = new SqlXml(new XmlTextReader(xmlToSave.InnerXml,
XmlNodeType.Document, null))
});
如何访问存储过程中的 XML 数据?
How do I access the XML data within the stored procedure?
[工作]
String sql = "BookDetails_Insert";
XmlDocument xmlToSave = new XmlDocument();
xmlToSave.Load("C:\Documents and Settings\Desktop\XML_Report\Books_1.xml");
SqlConnection sqlCon = new SqlConnection("...");
using (DbCommand command = sqlCon.CreateCommand())
{
**command.CommandType = CommandType.StoredProcedure;**
command.CommandText = sql;
command.Parameters.Add(
new SqlParameter("@xml", SqlDbType.Xml)
{
Value = new SqlXml(new XmlTextReader(xmlToSave.InnerXml
, XmlNodeType.Document, null))
});
sqlCon.Open();
DbTransaction trans = sqlCon.BeginTransaction();
command.Transaction = trans;
try
{
command.ExecuteNonQuery();
trans.Commit();
sqlCon.Close();
}
catch (Exception)
{
trans.Rollback();
sqlCon.Close();
throw;
}
编辑 2:如何创建选择查询来选择页面,根据某些条件进行描述.
Edit 2: How to create a select query to select pages, description based on some conditions.
<booksdetail> <isn_13>700001048</isbn_13> <isn_10>01048B</isbn_10>
<Image_URL>http://www.landt.com/Books/large/00/7010000048.jpg</Image_URL>
<title>QUICK AND FLUPKE</title> <Description> PRANKS AND JOKES QUICK AND FLUPKE </Description> </booksdetail>
推荐答案
对于您问题的第 2 部分,请参阅我对 存储过程:传递 XML 作为参数和 INSERT(键/值对)在存储过程中使用 XML.
For part 2 of your question, see my answer to Stored procedure: pass XML as an argument and INSERT (key/value pairs) for an example of how to use XML within a stored procedure.
编辑:下面的示例代码基于评论中给出的具体示例.
EDIT: Sample code below is based on the specific example given in the comments.
declare @MyXML xml
set @MyXML = '<booksdetail>
<isbn_13>700001048</isbn_13>
<isbn_10>01048B</isbn_10>
<Image_URL>http://www.landt.com/Books/large/00/70100048.jpg</Image_URL>
<title>QUICK AND FLUPKE</title>
<Description> PRANKS AND JOKES QUICK AND FLUPKE - CATASTROPHE QUICK AND FLUPKE </Description>
</booksdetail>'
select Book.detail.value('(isbn_13/text())[1]','varchar(100)') as isbn_13,
Book.detail.value('(isbn_10/text())[1]','varchar(100)') as isbn_10,
Book.detail.value('(Image_URL/text())[1]','varchar(100)') as Image_URL,
Book.detail.value('(title/text())[1]','varchar(100)') as title,
Book.detail.value('(Description/text())[1]','varchar(100)') as Description
from @MyXML.nodes('/booksdetail') as Book(detail)
这篇关于如何将 XML 从 C# 传递到 SQL Server 2008 中的存储过程?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!