Bulk insert SQL query , for XML file

Question

0.00/5 (No votes)

See more:

how to bulk insert XML data's from XML file With out Repeating the insert into statement,here i am using for each loop..how to insert all data's from XML file To SQL with single execution.

What I have tried:

C#

<pre lang="c#">  protected void Button1_Click(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection(@"user id=sa;password=ssa;database=Mohan;data source=PCTH101\PCTH101");
            con.Open();
            XmlDocument doc = new XmlDocument();
            doc.Load("d:/mohan/CHG_829_RPT_XML_PRD_SPEC_FINAL_2015-08-17-0908.xml");
            XmlNodeList nodes = doc.DocumentElement.SelectNodes("/fsxml/report/object/FSFORMULADOC");
            string docid = "", funcode = "" ,description = "" ,formulacode = "",funlabel = "";
            foreach (XmlNode node in nodes)
            {
                docid = node.SelectSingleNode("DOC_ID").InnerText;
                funcode = node.SelectSingleNode("FUNCTION_CODE").InnerText;
                description = node.SelectSingleNode("DOC_DESCRIPTION").InnerText;
                formulacode = node.SelectSingleNode("FORMULA_CODE").InnerText;
                funlabel = node.SelectSingleNode("FUNCTION_CODE_LABEL").InnerText;
                SqlCommand cmd = new SqlCommand("Insert  INTO xml1(docid,funcode,description,formulacode,funlabel) values(@docid, @funcode,@description,@formulacode,@funlabel)", con);
                cmd.Parameters.AddWithValue("@docid", docid);
                cmd.Parameters.AddWithValue("@funcode", funcode);
                cmd.Parameters.AddWithValue("@description", description);
                cmd.Parameters.AddWithValue("@formulacode", formulacode);
                cmd.Parameters.AddWithValue("@funlabel", funlabel);

                cmd.ExecuteNonQuery();
                Label1.Text = "Uploaded Successfully,Check database for Inserted Data's";
                Label1.Visible = true;


            }

Posted 20-Mar-17 19:49pm

GrpSMK

Updated 21-Mar-17 20:17pm

Add a Solution

Comments

Tomas Takac 21-Mar-17 4:00am

In order to use SqlBulkCopy you need to wrap the XML as IDataReader. AFAIK there is nothing like an XmlDataReader in the framework. You would need to write yourself or use a third party component.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Member 12966779 · Answer 1 · 2017-03-21T20:17:00

Hi,

Do you specifically need to code it yourself? To this this quickly and repeatedly one can easily use a tool called Linx 5 without having to code. You can read the XML into the tool and get the whole data structure, which you can then write into any database you'd like (SQL, MySQL, Oracle, etc.).

You can check it out at Linx 5 - Welcome[^]