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
