StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>We do something similar with database imports received in XML format, and all I do is pass the XML directly to a stored procedure and then shred the XML using XQuery and OPENXML. </p> <p>Both of those technologies allow you to query XML in SQL as if it was a table in your database. Taking that approach, you can just pass your XML to a script or stored procedure, query it in SQL, and insert the results wherever you need them. Anecdotally, <a href="http://beyondrelational.com/quiz/sqlserver/general/2010/questions/sqlserver-quiz-general-2010-jacob-sebastian-openxml-or-xquery-when-and-why.aspx" rel="nofollow">OPENXML is better for processing large XML files</a>, but you could try both and see how they work for you. Below is an example using OPENXML and a simple merge statement.</p> <pre><code>create procedure ImportXml ( @importXml xml ) as --with OPENXML you have to prepare the document before you use it --this is unecessary with XQuery DECLARE @idoc int EXEC sp_xml_preparedocument @idoc OUTPUT, @importXml; --this is just a typical Merge statement that will update data if it exists --and insert it if it does not merge NormalDataTable using ( --here is where you are querying the XML document directly. You can --see, it works just like a SQL statement, with a special syntax for --specifying where to get data out of the XML document and how to map --it to a table structure select * from openxml(@idoc, '/Root/Element') with ( ElementID int '@ElementID', ElementValueName varchar(50) '@ElementValueName' ) ) source on NormalDataTable.ElementID = source.ElementID when not matched then insert ... when matched then update ... exec sp_xml_removedocument @idoc </code></pre>

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