如何在SQL Server中插入XML数据 [英] How to insert XML data in SQL server

问题描述

我有一个xml文件，它有很多属性，我的问题是如何在SQL Server 2008中从xml文件中插入任何5个属性的批量数据。



我尝试过：



代码落后

I have a xml file ,it has so many attributes,my question is how to insert bulk data from xml file any 5 attribute in SQL server 2008.

What I have tried:

code behind

protected void UploadXML(object sender, EventArgs e)
     {
         string fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
         string filePath = Server.MapPath("~/Uploads/") + fileName;
         FileUpload1.SaveAs(filePath);
         string xml = File.ReadAllText(filePath);
         string constr = ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString;
         using (SqlConnection con = new SqlConnection(constr))
         {
             using (SqlCommand cmd = new SqlCommand("InsertXML"))
             {
                 cmd.Connection = con;
                 cmd.CommandType = CommandType.StoredProcedure;
                 cmd.Parameters.AddWithValue("@xml", xml);
                 con.Open();
                 cmd.ExecuteNonQuery();
                 con.Close();
             }
         }

aspx页

aspx page

  <div>
    <asp:FileUpload ID = "FileUpload1" runat = "server" /><br /><br />
<asp:Button ID="Button1" Text="Upload XML File" runat="server" OnClick="UploadXML" />
  
    </div>

存储过程

Stored proc

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[InsertXML]
@xml XML
AS
BEGIN
      SET NOCOUNT ON;
 
      INSERT INTO xml
      SELECT
      FSFORMULADOC.value('@DOC_ID','INT') AS Id, --ATTRIBUTE
      FSFORMULADOC.value('(FUNCTION_CODE/text())[1]','VARCHAR(100)') AS Function1, --TAG
      FSDATAMATRIX.value('(TABLE_CODE/text())[1]','VARCHAR(100)') AS Table1 --TAG
      FROM
      @xml.nodes('/fsxml/report/object/FSFORMULADOC')AS TEMPTABLE(Customer)
END

这是我的样本xml文件

This is my sample xml file

<fsxml>
  <report>
    <SchemaList>
      <Schema>PDU_FORMULA_DEV_001.xsd</Schema>
      <Schema>PDU_ITEM_DEV_001.xsd</Schema>
    </SchemaList>
    <object>
<FSFORMULADOC>
        <DOC_ID>101015</DOC_ID>
        <FUNCTION_CODE>PRODUCT_SUMMARY</FUNCTION_CODE>
        <TEXT_DATA />
        <ATTCH_CNT>0</ATTCH_CNT>
        <ATTCH_TYPE>0</ATTCH_TYPE>
        <DOC_ORDER>1</DOC_ORDER>
        <DOC_DESCRIPTION>Product Summary</DOC_DESCRIPTION>
        <FORMULA_CODE>701399-ML81</FORMULA_CODE>
        <VERSION>01</VERSION>
        <SRVROW_ID>1</SRVROW_ID>
        <FUNCTION_CODE_LABEL>PRODUCT_SUMMARY</FUNCTION_CODE_LABEL>
      </FSFORMULADOC>
      <FSFORMULADOC>
  <FSDATAMATRIX>
        <TABLE_CODE>GRADING_REQUIREMENTS</TABLE_CODE>
        <SYMBOL>FORMULA</SYMBOL>
        <VIEW_ID>0</VIEW_ID>
        <DESCRIPTION>Grading Requirements</DESCRIPTION>
        <ROW_ADD_IND>1</ROW_ADD_IND>
        <OWNER_CODE>FSI</OWNER_CODE>
        <GROUP_CODE>ADMIN</GROUP_CODE>
        <OWNER_SECURITY>15</OWNER_SECURITY>
        <GROUP_SECURITY>15</GROUP_SECURITY>
        <ROLE_SECURITY>15</ROLE_SECURITY>
        <DB_TABLE_NAME>FORMULA0</DB_TABLE_NAME>
        <SORT_ORDER>LINE_ID ASC</SORT_ORDER>
      </FSDATAMATRIX>
 <FSFORMULAINGR>
          <FORMULA_ID>19337</FORMULA_ID>
          <LINE_TYPE>0</LINE_TYPE>
          <LINE_ID>5</LINE_ID>
          <ITEM_CODE>925318</ITEM_CODE>
          <QUANTITY>2</QUANTITY>
          <UOM_CODE>EA</UOM_CODE>
          <ITEM_FORMULA_ID>0</ITEM_FORMULA_ID>
          <LINE_BREAK_CODE />
          <MATERIAL_PCT>0</MATERIAL_PCT>
          <SCALE_IND>0</SCALE_IND>
          <SUBFORMULA_IND>0</SUBFORMULA_IND>
          <RATIO_LOCK_IND>0</RATIO_LOCK_IND>
          <RELQTY_IND>0</RELQTY_IND>
          <RELQTY_PCT>0</RELQTY_PCT>
          <DOC_ID>0</DOC_ID>
          <SECTION_TYPE>0</SECTION_TYPE>
          <ADJUST_IND>0</ADJUST_IND>
          <DESCRIPTION>BOX BLISS END 8-9/16 IN X 25-1/4 IN</DESCRIPTION>
          <COMPONENT_IND>2</COMPONENT_IND>
          <CAS />
          <COMM_CODE />
          <CLASS>PACKAGING_COMPONENT</CLASS>
          <ALIAS_CODE1 />
          <ALIAS_CODE2 />
          <ALIAS_CODE3 />
          <ALIAS_CODE4 />
          <ALIAS_CODE5 />
          <ALIAS_CODE6 />
          <ALIAS_CODE7 />
          <ALIAS_CODE8 />
          <STATUS>300</STATUS>
          <SRVROW_ID>5</SRVROW_ID>
          <SCALE_IND_LABEL>Linear</SCALE_IND_LABEL>
          <RELQTY_IND_LABEL>No</RELQTY_IND_LABEL>
          <SECTION_TYPE_LABEL>None</SECTION_TYPE_LABEL>
          <ADJUST_IND_LABEL>No</ADJUST_IND_LABEL>
          <STATUS_LABEL>Approved</STATUS_LABEL>
        </FSFORMULAINGR>

推荐答案

bulk xml into sql server [ ^ ]

看一下这个链接：批量导入和导出XML文档（SQL Server）的示例 [ ^ ]

Take a look at this link: Examples of Bulk Import and Export of XML Documents (SQL Server)[^]

尝试将xml字符串变量转换为XmlDocument然后再将该xml对象作为参数传递给您的Sql查询。

Try to convert your xml string variable into XmlDocument and then pass that xml object to your Sql query as parameter.

protected void UploadXML(object sender, EventArgs e)
        {
            string fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
            string filePath = Server.MapPath("~/Uploads/") + fileName;
            FileUpload1.SaveAs(filePath);
            string xml = File.ReadAllText(filePath);

            XmlDocument xd = new XmlDocument();
            xd.LoadXml(xml);

            string constr = ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString;
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand("InsertXML"))
                {
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@xml", xd);
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                }
            }

这可能会有所帮助！

This may helps!

这篇关于如何在SQL Server中插入XML数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！