best way to import hierarchical xml?

Category: sql server xml

Question

allenYGFKM on Thu, 07 Jun 2012 08:28:31


I have several xsd schemas defined that map out a large dataset.  I also have some hierarchical xml files with actual data.  Starting from a blank database without tables, whats my best approach to both creating the tables and loading that data into them?

Replies

wBob on Thu, 07 Jun 2012 10:22:07


XML Bulk Load component is good for this.  You can use the .SchemaGen = True option to draft out an initial copy of the tables, then script them out from Management Studio for example.  Add in foreign keys, other constraints then empty the tables, set .SchemaGen to false and load them up.

Examples of Bulk Importing and Exporting XML Documents
http://msdn.microsoft.com/en-us/library/ms191184.aspx

How to import XML into SQL Server with the XML Bulk Load component 
http://support.microsoft.com/kb/316005

Wild.Bill.Work on Wed, 01 Nov 2017 14:15:42


Is there any way to do this using a stored procedure that accepts the xml as an input parameter?  All of the example I've seen only process files.

Russ Loski on Wed, 01 Nov 2017 19:35:08


Good question.  I would start a new thread and ask.