<p>I have been hearing the podcast blog for a while, I hope I dont break this. 
The question is this: I have to insert an xml to a database. This will be for already defined tables and fields. So what is the best way to accomplish this? So far I am leaning toward programatic. I have been seeing varios options, one is Data Transfer Objects (DTO), in the SQL Server there is the sp_xml_preparedocument that is used to get transfer XMLs to an object and throught code. </p>

<p>I am using CSharp and SQL Server 2005. The fields are not XML fields, they are the usual SQL datatypes. </p>

[{'answer_id': 59882, 'author': 'HigherAbstraction', 'author_id': 5945, 'author_profile': 'https://Stackoverflow.com/users/5945', 'pm_score': 0, 'selected': False, 'text': '<p>If your XML conforms to a particular XSD schema, you can look into using the "xsd.exe" command line tool to generate C# object classes that you can bind the XML to, and then form your insert statements using the properties of those objects: <a href="http://msdn.microsoft.com/en-us/library/x6c1kb0s(VS.71).aspx" rel="nofollow noreferrer">MSDN XSD Doc</a></p>\n'}, {'answer_id': 59917, 'author': 'J Healy', 'author_id': 5946, 'author_profile': 'https://Stackoverflow.com/users/5946', 'pm_score': 0, 'selected': False, 'text': '<p>Peruse this document and it will give you the options:</p>\n\n<p>MSDN: <a href="http://msdn.microsoft.com/en-us/library/ms345110(SQL.90).aspx" rel="nofollow noreferrer">XML Options in Microsoft SQL Server 2005</a></p>\n'}, {'answer_id': 60139, 'author': 'Scott Saad', 'author_id': 4916, 'author_profile': 'https://Stackoverflow.com/users/4916', 'pm_score': 2, 'selected': False, 'text': '<p>In an attempt to try and help, we may need some clarification. Maybe by restating the problem you can let us know if this is what you\'re asking:</p>\n\n<p><strong>How can one import existing xml into a SQL 2005 database, without relying on the built-in xml type?</strong></p>\n\n<p>A fairly straight forward solution that you already mentioned is the <em>sp_xml_preparedocument</em>, combined with <em>openxml</em>. </p>\n\n<p>Hopefully the following example illustrates the correct usage. For a more complete example checkout the MSDN docs on <a href="http://msdn.microsoft.com/en-us/library/ms187897(SQL.90).aspx" rel="nofollow noreferrer">Using OPENXML</a>.</p>\n\n<pre><code>declare @XmlDocumentHandle int\ndeclare @XmlDocument nvarchar(1000)\nset @XmlDocument = N\'&lt;ROOT&gt;\n&lt;Customer&gt;\n   &lt;FirstName&gt;Will&lt;/FirstName&gt;\n   &lt;LastName&gt;Smith&lt;/LastName&gt;\n&lt;/Customer&gt;\n&lt;/ROOT&gt;\'\n\n-- Create temp table to insert data into\ncreate table #Customer \n( \n    FirstName varchar(20),\n    LastName varchar(20) \n)\n-- Create an internal representation of the XML document.\nexec sp_xml_preparedocument @XmlDocumentHandle output, @XmlDocument\n\n-- Insert using openxml allows us to read the structure\ninsert into #Customer\nselect \n    FirstName = XmlFirstName,\n    LastName = XmlLastName\nfrom openxml ( @XmlDocumentHandle, \'/ROOT/Customer\',2 )\nwith \n(\n    XmlFirstName  varchar(20) \'FirstName\',\n    XmlLastName varchar(20) \'LastName\'\n)\nwhere ( XmlFirstName = \'Will\' and XmlLastName = \'Smith\' )\n\n-- Cleanup xml document\nexec sp_xml_removedocument @XmlDocumentHandle\n\n-- Show the data\nselect * \nfrom #Customer\n\n-- Drop tmp table\ndrop table #Customer\n</code></pre>\n\n<p>If you have an xml file and are using C#, then defining a stored procedure that does something like the above and then passing the entire xml file contents to the stored procedure as a <em>string</em> should give you a fairly straight forward way of importing xml into your existing table(s).</p>\n'}, {'answer_id': 87072, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 0, 'selected': False, 'text': '<p>You may want to use XSLT to transfer your XML into SQL statements... ie</p>\n\n<pre><code>&lt;xml type="user"&gt;\n    &lt;data&gt;1&lt;/data&gt;\n    &lt;data&gt;2&lt;/data&gt;\n&lt;xml&gt;\n</code></pre>\n\n<p>Then the XSLT would look like</p>\n\n<pre><code>&lt;xsl:template match="xml"&gt;\n    INSERT INTO &lt;xsl:value-of select="@type" /&gt; (data1, data2) VALUES (\n       \'&lt;xsl:value-of select="data[1]" /&gt;\',\n       \'&lt;xsl:value-of select="data[2]" /&gt;\');\n&lt;/xsl:template&gt;\n</code></pre>\n\n<p>The match statement most likely won\'t be the root node, but hopefully you get the idea. You may also need to wrap the non xsl:value-of parts in xsl:text to prevent extra characters from being dumped into the query. And you\'d have to make sure the output of the XSLT was text. That said you could get a list of SQL statements that you could run through the DB. or you could use XSLT to output a T-SQL statement that you could load as a stored procedure.</p>\n'}]
['https://Stackoverflow.com/questions/59790', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/-1/']