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();
}
精彩评论