StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<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> <p><strong>How can one import existing xml into a SQL 2005 database, without relying on the built-in xml type?</strong></p> <p>A fairly straight forward solution that you already mentioned is the <em>sp_xml_preparedocument</em>, combined with <em>openxml</em>. </p> <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> <pre><code>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 </code></pre> <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>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload