You can load documents into SQL Server using the OPENROWSET option. This will load the XML file into one large rowset, into a single row and a single column. This rowset can then be queried using the OPENXML function. The OPENXML function allows an XML document to be treated like a table.
The following video contains a tutorial on how to load and read an XML document using OPENROWSET and OPENXML.
Below are SQL statements used in the video:
DECLARE @x xml
SELECT @x = P
FROM OPENROWSET (BULK 'C:\Examples\Products.xml', SINGLE_BLOB) AS Products(P)
--SELECT @x
DECLARE @hdoc int
EXEC sp_xml_preparedocument @hdoc OUTPUT, @x
SELECT *
--INTO #tmp_MySubcategories
FROM OPENXML (@hdoc, '/Subcategories/Subcategory', 1)
WITH (ProductSubcategoryID int, Name varchar(100))
SELECT *
--INTO #tmp_MyProducts
FROM OPENXML (@hdoc, '/Subcategories/Subcategory/Products/Product', 2)
WITH (ProductID int, Name varchar(100), ProductNumber varchar(50), ListPrice float, ModifiedDate datetime)
SELECT *
FROM OPENXML (@hdoc, '/Subcategories/Subcategory/Products/Product', 2)
WITH (
ProductSubcategoryID int '../../@ProductSubcategoryID',
ProductSubcategoryName varchar(100) '../../@Name',
ProductID int,
Name varchar(100),
ProductNumber varchar(50),
ListPrice float,
ModifiedDate datetime)
EXEC sp_xml_removedocument @hdoc
SELECT * FROM #tmp_MySubcategories
SELECT * FROM #tmp_MyProducts
DROP TABLE #tmp_MySubcategories
DROP TABLE #tmp_MyProducts
Please check this:
ReplyDeletehttp://forums.asp.net/t/1843973.aspx/1?Consolidating+XML+Files+for+processing+and+uploading+to+Database+
I still need to know how to generate result set by joining data of Products stored in multiple XML Files, where each file has data for one Product. I need this badly.
Your tutorial was great!
Tarek.
With OPENXML, you can treat the XML files like tables. So if you have multiple XML files, and there is a common element/attribute in those files that you can join with, then once you bring it into SQL with OPENXML, you can join on that field. For example, say I have 2 product xml files. The first file contains the Name for product 100. The second file contains ProductNumber and ListPrice for product 100. I can join these two files together using the ProductID field to get all the information.
ReplyDeleteBelow is an example. Notice that @hdoc2 is the handle to the other xml file.
SELECT *
FROM OPENXML (@hdoc, '/Subcategories/Subcategory/Products/Product', 2)
WITH (
ProductID int,
Name varchar(100)) AS P1
INNER JOIN OPENXML (@hdoc2, '/Subcategories/Subcategory/Products/Product', 2)
WITH (
ProductID int,
ProductNumber varchar(50),
ListPrice float) AS P2
ON P1.ProductID = P2.ProductID