如何在SQL Server中插入XML数据

本文介绍了如何在SQL Server中插入XML数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个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!