What is the best way to save XML data to SQL Server?

Question

Is there a direct route that is pretty straight forward? (i.e. can SQL Server read XML)

Or, is it best to parse the XML and just transfer it in the usual way via ADO.Net either as individual rows or perhaps a batch update?

I realize there may be solutions that involve large complex stored procs--while I'm not entirely opposed to this, I tend to prefer to have most of my business logic in the C# code. I have seen a solution using SQLXMLBulkLoad, but it seemed to require fairly complex SQL code.

For reference, I'll be working with about 100 rows at a time with about 50 small pieces of data for each (strings and ints). This will eventually become a daily batch job.

Any code snippets you can provide would be very much appreciated.

Answer 1

You can use the function OPENXML and stored procedure sp_xml_preparedocument to easily convert your XML into rowsets.

Answer 2

SQL Server 2005 and up have a datatype called "XML" which you can store XML in - untyped or typed with a XSD schema.

You can basically fill columns of type XML from an XML literal string, so you can easily just use a normal INSERT statement and fill the XML contents into that field.

Marc

Answer 3

If you are using SQL Server 2008 (or 2005), it has an xml native datatype. You can associate an XSD schema with xml variables, and Insert directly into columns of type xml.

Answer 4

Yes, SQL Server 2005 and above can parse XML out of the box.

You use the nodes, value and query methods to break it down how you want, whether values or attributes

Some shameless plugging:

• Importing XML into SQL Server
• Search XML Column in SQL