如何将XML从c#传递到SQL Server 2008中的存储过程?

I want to pass xml document to sql server stored procedure such as this:

我想将xml文档传递给sql server存储过程如下:

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.

我想将一些字段数据与其他表数据进行比较，如果匹配，则必须将记录插入表中。

Requirements:

要求:

How do I pass XML to the stored procedure? I tried this, but it doesn’t work:[Working]

如何将XML传递给存储过程?我试过了，但没用:[工作]

command.Parameters.Add(
    new SqlParameter("@xml", SqlDbType.Xml)
    {
        Value = new SqlXml(new XmlTextReader(xmlToSave.InnerXml,
                           XmlNodeType.Document, null))
    });

How do I access the XML data within the stored procedure?

如何在存储过程中访问XML数据?

Edit: [Working]

编辑(工作):

 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;
            }

Edit 2: How to create a select query to select pages, description based on some conditions.

编辑2:如何创建一个select查询来选择页面，描述基于一些条件。

  <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>

4 个解决方案

#1

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.

对于问题的第2部分，请参阅我对存储过程的回答:将XML作为参数传递，并插入(键/值对)一个如何在存储过程中使用XML的示例。

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)

#2

As stated in http://support.microsoft.com/kb/555266, you need to pass xml data as NText.

如http://support.microsoft.com/kb/555266所述，需要将xml数据作为NText传递。

You can query an XML variable as follows:

您可以查询一个XML变量如下:

DECLARE @PeopleXml XML
    SET @PeopleXml = '<People>
    <Person>
    <Name>James</Name>
    <Age>28</Age>
    </Person>
    <Person>
    <Name>Jane</Name>
    <Age>24</Age>
    </Person>
    </People>'
--  put [1] at the end to ensure the path expression returns a singleton.
SELECT p.c.value('Person[1]/Name[1]', 'varchar(50)')
FROM @PeopleXml.nodes('People') p(c) -- table and column aliases

#3

public static string UpdateStaticCertificateFormateNo1Data(StaticCertificateFormatNo1LogicLayer StaticFormat1Detail)
{
    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
    con.Open();
    string strXMLRegistrationDetails, strXMLQutPut = "<root></root>";
    System.Xml.Serialization.XmlSerializer x = new System.Xml.Serialization.XmlSerializer(StaticFormat1Detail.GetType());
    System.IO.MemoryStream stream = new System.IO.MemoryStream();
    x.Serialize(stream, StaticFormat1Detail);
    stream.Position = 0;
    XmlDocument xd = new XmlDocument();
    xd.Load(stream);
    strXMLRegistrationDetails = xd.InnerXml;
    SqlTransaction trn = con.BeginTransaction();
    try
    {
        SqlParameter[] paramsToStore = new SqlParameter[2];
        paramsToStore[0] = ControllersHelper.GetSqlParameter("@StaticFormat1Detail", strXMLRegistrationDetails, SqlDbType.VarChar);
        paramsToStore[1] = ControllersHelper.GetSqlParameter("@OutPut", strXMLQutPut, SqlDbType.VarChar);
        SqlHelper.ExecuteNonQuery(trn, CommandType.StoredProcedure, "UPS_UpdateStaticCertificateFormateNo1Detail", paramsToStore);
        trn.Commit();
    }
    catch (Exception ex)
    {
        trn.Rollback();
        con.Close();
        if (ex.Message.Contains("UNIQUE KEY constrastring"))
        { return "Details already in  List"; }
        else { return ex.Message; }
    }
    con.Close();
    return "Details successfully Added...";
}

#4

You will be using xPath and XQuery mostly to query and modify XML data.

您将主要使用xPath和XQuery查询和修改XML数据。

Here is a good starting point http://msdn.microsoft.com/en-us/library/ms190798.aspx.

这里有一个很好的起点http://msdn.microsoft.com/en-us/library/ms190798.aspx。

I can't really get any more specific because your question is extremely vague. Please ask specific questions on how to do something if you want help with how to use XPath and XQuery.

我实在说不清楚，因为你的问题非常模糊。如果您希望得到关于如何使用XPath和XQuery的帮助，请询问关于如何做某事的具体问题。

#1