Best way to port xml to SQL Server?

Question

I heard the podcast blog for a while, hope I don't break it. The question is this: I need to insert xml into the database. This will be for already defined tables and fields. So what is the best way to achieve this? While I'm leaning towards software. I saw varios options, one is data transfer objects (DTOs), SQL Server has a sp_xml_preparedocument file that is used to pass XML data to an object and pass-through code.

I use CSharp and SQL Server 2005. Fields are not XML fields, they are regular SQL data types.

Answer 1

In an attempt to try to help us, some clarification may be needed. Perhaps by reformulating the problem, you can tell us if this is what you are asking:

How to import existing xml into a SQL 2005 database without relying on the built-in xml type?

A fairly straightforward solution that you already mentioned is sp_xml_preparedocument, combined with openxml.

Hopefully the following example illustrates the correct use. For a more complete example, check the MSDN docs for Using OPENXML .

declare @XmlDocumentHandle int declare @XmlDocument nvarchar(1000) set @XmlDocument = N'<ROOT> <Customer> <FirstName>Will</FirstName> <LastName>Smith</LastName> </Customer> </ROOT>' -- Create temp table to insert data into create table #Customer ( FirstName varchar(20), LastName varchar(20) ) -- Create an internal representation of the XML document. exec sp_xml_preparedocument @XmlDocumentHandle output, @XmlDocument -- Insert using openxml allows us to read the structure insert into #Customer select FirstName = XmlFirstName, LastName = XmlLastName from openxml ( @XmlDocumentHandle, '/ROOT/Customer',2 ) with ( XmlFirstName varchar(20) 'FirstName', XmlLastName varchar(20) 'LastName' ) where ( XmlFirstName = 'Will' and XmlLastName = 'Smith' ) -- Cleanup xml document exec sp_xml_removedocument @XmlDocumentHandle -- Show the data select * from #Customer -- Drop tmp table drop table #Customer 

If you have an xml file and you use C #, then defining a stored procedure that does something similar above, and then transferring the contents of the entire xml file to the stored procedure as a string, should give you a pretty simple way to import xml into existing tables .

Answer 2

If your XML conforms to a specific XSD schema, you can examine the command-line tool "xsd.exe" to create C # object classes to which you can bind XML, and then form your insert statements using the properties of these objects: MSDN XSD Doc

Answer 3

Browse through this document and it will provide you with the following options:

MSDN: XML Options in Microsoft SQL Server 2005

Answer 4

You can use XSLT to transfer XML to SQL queries ... i.e.

 <xml type="user"> <data>1</data> <data>2</data> <xml> 

Then XSLT will look like

 <xsl:template match="xml"> INSERT INTO <xsl:value-of select="@type" /> (data1, data2) VALUES ( '<xsl:value-of select="data[1]" />', '<xsl:value-of select="data[2]" />'); </xsl:template> 

A conformance statement will most likely not be the root node, but hopefully you get this idea. You may also need to wrap the non xsl: value from parts in xsl: text to prevent extra characters from being added to the request. And you have to make sure the XSLT output is text. In this case, you can get a list of SQL statements that you could run through the database. or you can use XSLT to output a T-SQL statement that you could load as a stored procedure.