xml - Loading XML Documents

SQL Server 2012 - Loading XML Documents

~

<Subcategories>

  <Subcategory ProductSubCategoryID="18"  Name="Bib-Shorts">

  <Products>

    <Product>

         <ProductID>855 </ProductID>

         <Name> T-Shirt </Name>

         <ProductNumber>1234</ProductNumber>

         <ListPrice>89.99</ListPrice>

         <ModifiedDate>    </ModifiedDate>

     </Product>

      <Product>

     </Product>

 </Products>

~

example1.

DECLARE  @x  xml

SELECT  @x= P  FROM OPENROWSET ( BULK  'c:\Examples\Products.xml', SINGLE_BLOB) AS Products(P)

DECLARE @hdoc int

EXEC  sp_xml_prepareddocument @hdoc OUTPUT,@x

SELECT  * FROM OPENXML(@hdoc, ' /Subcategories/Subcategory',1)
WITH ( ProductSubCategoryID,Name)


EXEC  sp_xml_removedocument @hdoc

example2.

DECLARE  @x  xml

SELECT  @x= P  FROM OPENROWSET ( BULK  'c:\Examples\Products.xml', SINGLE_BLOB) AS Products(P)

DECLARE @hdoc int

EXEC  sp_xml_prepareddocument @hdoc OUTPUT,@x

SELECT  *  INTO  MyProducts  FROM OPENXML (@hdoc,'/Subcategories/Subcategory/Products/Product',2)
WITH
(
'../../@ProductSubcategoryID',
'../../@Name'
ProductID int,
ProductName varchar(100),
ListPrice float,
ModifiedDate datetime
)


EXEC  sp_xml_removedocument @hdoc

~------------------------------------------------------------------------------------------------------------------------

step wise:

1.

SELECT
FROM OPENROWSET ( BULK  'c:\Examples\Products.xml', SINGLE_BLOB) AS Products(P)

note:
i. it creates a table named "Products"  and a column named "P"  and the  'c:\Examples\Products.xml' is stored as binary.

2.

DECLARE @x xml

3.

DECLARE  @x  xml

SELECT  @x= P  FROM OPENROWSET ( BULK  'c:\Examples\Products.xml', SINGLE_BLOB) AS Products(P)

Now xml in sql server. It can be read using below sql.

SELECT  * FROM OPENXML(@hdoc, ' ',1) WITH ( )

Treats the xml as the table.

@hdoc - handle to the xml doc.

'  '  - xpath ( xml hierarchy u want to select data from ).

1,2  - flag

1 - attribute
2 - fields are elements

4.

<Subcategories>

  <Subcategory ProductSubCategoryID="18"  Name="Bib-Shorts">

5.

DECLARE  @x  xml

SELECT  @x= P  FROM OPENROWSET ( BULK  'c:\Examples\Products.xml', SINGLE_BLOB) AS Products(P)

DECLARE @hdoc int

EXEC  sp_xml_prepareddocument @hdoc OUTPUT,@x

SELECT  * FROM OPENXML(@hdoc, ' /Subcategories/Subcategory',1)
WITH ( ProductSubCategoryID,Name)

EXEC  sp_xml_removedocument @hdoc

6.

a.No rows will be returned.

SELECT * FROM OPENXML (@hdoc, '/Subcategories/Subcategory/Products/Product',1)

note: 1 , look for attribute not element.

WITH (

ProductID int,
Name varchar(100),
ProductNumber varchar(100),
ListPrice float,
Modifieddate datetime

)

b.Rows will be returned.

SELECT  *  FROM  OPENXML ( @hdoc, 'Subcategories/Subcategory/Products/Product',2)
WITH (
ProductID int,
Name varchar(100),
ProductNumber varchar(100),
ListPrice float,
ModifiedDate datetime
)

7.

a.

SELECT  *  INTO  MySubcategories FROM OPENXML(@hdoc, '/Subcategories/Subcategory',1)
WITH(
ProductCategoryID int,
Name varchar(100)
)

b.

SELECT  *  INTO  MyProduct FROM OPENXML(@hdoc, '/Subcategories/Subcategory/Products/Product',2)
WITH(
ProductID int,
Name varchar(100),
ProductNumber varchar(100),
ListPrice float
)

8.

SELECT  *  INTO  MyProducts  FROM OPENXML (@hdoc,'/Subcategories/Subcategory/Products/Product',2)
WITH
(
'../../@ProductSubcategoryID',
'../../@Name'
ProductID int,
ProductName varchar(100),
ListPrice float,
ModifiedDate datetime
)

note:

flag : 2 - elements , so to include attribute  each level is defined as ../

Name,ProductName is different otherwise duplicate error would have come.