What's the best approach for reading in XML data and building a query to insert the values into a SQL Server DB?

Question

What's the best approach for reading in XML data and building a query to insert the values into a SQL Server DB?

Anyone have a best approach for this type of scenario?

Answer 1

Personally, I use a DataSet.ReadXML(). Then it places all the data into DataTables in the DataSet and then is much easier to loop through and manipulate the data.

Ron

Answer 2

SQL 2005 has very good xml capabilities. This is one technique using XML Parameters

Answer 3

Are you aware of the XML features of MS SQL Server 2005/8 ? You can insert XML strong typed directly as a column into SQL, and then enforce that the XML is valid against the specified schema.

You can even index columns within the XML and run Xquery against it.

Could we have some more information about what you want to do?

Answer 4

If you are after fairly simple data (i.e. the first-level children), then an option I've used successfully in the past is to use an XmlReader to read the xml (on child at a time), presenting the data in a lightweight IDataReader that SqlBulkCopy can consume to pump the data in. Like so. If the data is more complex, use sqlxml / bulk load.

Answer 5

Write a database schema with a similar structure to the xml, then write a Linq To Xml and Linq To Sql statement to shove in the data.

Answer 6

It depends a lot on how big the XML files are. There is a big difference on how to do it with a 50kb and 50mb file.

Answer 7

Pass the data to a stored procedure as an XML document to an variable with the XML data type. Then parse the document within the procedure and load the data into a table.

I give examples here.

Answer 8

Your question is not clear on the details:

If you have arbitrary XML files you want to insert into a db table, declare an XML column (SQL2005+) and simply insert the data as string.

If you have XML files adhering to an XML schema, you can declare an XML SCHEMA COLLECTION using the XSD, bind the XML column to that xml schema collection, and insert the data. SQL Server will validate every inserted XML value against that XSD.

If you want to manually parse the XML data, use XPath or XPathNavigator or XMLDocument.SelectNodes to extract the data, and insert them record by becord into your db tables.

You probably want to be more specific in your question which of the alternatives apply to your case.