开发者

xml to sql using linq or simple c sharp code

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&开发者_开发百科gt;
            <a>Richard Griffiths</a>
            <p>Und</p>
            <r/>
         </m>
         ... (more nodes snipped) ...
      </filmc>
   </dirfilms>
</casts>


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.


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();
        }
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜