xml to sql using linq or simple c sharp code

Question

I need to save into sql server the data from a xml file with output like: with root casts:

<casts>
   <dirfilms>
      <dirid>AaB</dirid>
      <is>A.Bennett</is>
      <castnote>Actors and notes.</castnote>
      <filmc>
         <m>
            <f>AaB10</f>
            <t>A Private Function</t>
            <a>Michael Palin</a>
            <p>Und</p>
            <r>podiatrist</r>
         </m>
         <m>
            <f>AaB10</f>
            <t>A Private Function</t>
            <a>Maggie Smith</a>
            <p>Und</p>
            <r>ambitious wife</r>
            <awards>
               <award>BFA</award>
            </awards>
         </m>
         <m>
            <f>AaB10</f>
            <t>A Private Function</t>
            <a>Denholm Elliott</a>
            <p>Und</p>
            <r/>
            <awards>
               <award>BFA</award>
            </awards>
         </m>
         <m>
            <f>AaB10</f>
            <t>A Private Function</t>
            <a>Richard Griffiths</a>
            <p>Und</p>
            <r/>
         </m>
         ... (more nodes snipped) ...
      </filmc>
   </dirfilms>
</casts>

Do you want to save the whole XML as one piece, or are you intending to "shred" that information into relational tables for storage? — marc_s
– marc_s, Commented Dec 29, 2010 at 16:39
Yes Marc I would like to save them into relational tables for storage for the case mentioned above, such us: For each director a header entry is provided with {code, name, note} and for each film we have another table let say tblMovie {filmcode, title, actor, followed by dep. info, role-type, role-description, and optional role-name, role-specs}...something similar... — gaponte69
– gaponte69, Commented Dec 30, 2010 at 7:35

user432219 · Accepted Answer · 2010-12-29 15:18:46Z

1

My suggestion for SQL Server 2008:

XDocument xmlDoc = XDocument.Load(@"C:\myXmlFile.xml");

using (SqlConnection conn = new SqlConnection("Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;"))
{
    conn.Open();

    using (SqlCommand cmd = conn.CreateCommand())
    {
        cmd.CommandText = "INSERT INTO MyTable (MyXmlColumn) VALUES (@XmlColumnParam);";
        cmd.Parameters.AddWithValue("@XmlColumnParam", xmlDoc.ToString());

        cmd.ExecuteNonQuery();
    }
}

The column MyXmlColumn should be nvarchar(MAX) or better XML to keep sure that data that are inserted as valid XML.

answered Dec 29, 2010 at 15:18

user432219

Sign up to request clarification or add additional context in comments.

Comments

Hans van Dodewaard · Accepted Answer · 2010-12-29 20:18:06Z

1

Use XML as the column type in the database. Make a linq2sql mapping (.dbml file) and use something like:

        XElement xml = XElement.Load(@"c:\myXml.xml");
        using (var context = new MyDataContext(connectionStr))
        {
            var entity = new MyTable{XML = xml};
            context.MyTables.InsertOnSubmit(entity);
            context.SubmitChanges();
        }

edited Dec 29, 2010 at 20:18

answered Dec 29, 2010 at 16:52

Hans van Dodewaard

7135 silver badges8 bronze badges

1 Comment

gaponte69 Over a year ago

Dear Hans, your suggestions are very clever, but I am not able to link 2 sql to different tables, like I had explained to Marc above, "I would like to save them into relational tables for storage for the case mentioned above, such us: For each director a header entry is provided with {code, name, note} and for each film we have another table let say tblMovie {filmcode, title, actor, followed by dep. info, role-type, role-description, and optional role-name, role-specs}...something similar" ... your answer is appreciated...thank you once again...

Collectives™ on Stack Overflow

xml to sql using linq or simple c sharp code

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related