Click here to Skip to main content
15,899,679 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
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 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








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>
Posted
Updated 29-Mar-17 2:51am
v2
Comments
Graeme_Grant 16-Mar-17 7:09am    
What have you tried so far?
GrpSMK 16-Mar-17 7:11am    
yea created file upload,and stored procedure to insert data but showing error
Graeme_Grant 16-Mar-17 7:12am    
What is the error?
GrpSMK 17-Mar-17 1:33am    
WHat is the root node of my xml file?
Graeme_Grant 17-Mar-17 2:44am    
<fsxml>
Was that a trick question???

 
Share this answer
 
 
Share this answer
 
Comments
GrpSMK 17-Mar-17 1:32am    
can u say what is the root node of my xml file?
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!
 
Share this answer
 
Comments
CHill60 30-Mar-17 7:26am    
Did you actually try this out?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900