save the information from an xml in sql

[paulcc84]

hi all. I need to save the mass information from an xml in
sql database from c # is not how. thanks

 

[Arne Vajhøj]

hi all. I need to save the mass information from an xml in
sql database from c # is not how. thanks

Do you want to parse the XML on the C# and insert
normal database types or do you want to have SQLServer
handle the XML?

Arne

 

[paulcc84]

want to parse the XML on the C# and insert
normal database type

 

[Arne Vajhøj]

want to parse the XML on the C# and insert
normal database type

Load the XML into a XmlDocument amd walk through it via
the good old GetElementsByTagName or the more modern
XPath SelectNodes assign the extracted values to
parameter values for SqlCommand INSERT statements.

(you could also use LINQ, but I doubt there will be
any benefits in this case)

If gave an example of XML then I may even provide a
small example of code.

Arne

 

[Andy O'Neill]

want to parse the XML on the C# and insert
normal database type

I'm working with xml at the moment.
Here's a snippet, I've fiddled with it a bit to try and make it clearer what
it does.
I'm inserting into a datatable but you could stick whatever object there or
foreach a list or whatever.
My XML file is as flat as a file you'd ever find.


XDocument xd = XDocument.Load("file.xml");
var q = from c in xd.Descendants("xmlroot")
orderby (Int32)c.Element("ColID")
select c;
DataTable dt = ds.Tables[0];
foreach (var c in q)
{
DataRow row = dt.NewRow();
row["ColID"] = (Int32)c.Element("ColID");
row["FieldName"] = (String)c.Element("FieldName");
row["SeeInGrid"] = (Boolean)c.Element("SeeInGrid");
row["ColOrder"] = (Int32)c.Element("ColOrder");
row["DataType"] = (String)c.Element("DataType");

row["ColHeading"] = (String)c.Element("ColHeading");
row["ColWidth"] = (String)c.Element("ColWidth");
dt.Rows.Add(row);
}

 

[sloan]

If you want to "shred" the xml............here is a link:
http://pratchev.blogspot.com/2007/06/shredding-xml-in-sql-server-2005.html

You can pass down the xml via the "XML" datatype (in tsql)....or if you're
at Sql Server 2000, the "text" datatype. Note, Sql Server 2000 has an older
OPENXML function.

See
http://support.microsoft.com/kb/315968
for a VERY OLD (and showing its age) example.

Do NOT use OPENXML with 2005,2008 or later.