Handle XML to SQL output

Transact-SQL
6

This will not be good for you in the future when you will have more ProductId with different types. I would reconsider this design of your xml if at all possible. otherwise you will need to do something more dynamic if the future you will have product type 1, 2, 3, N



DECLARE @xml XML

SELECT @xml = CONVERT(XML,Product,2)
FROM OPENROWSET (BULK 'C:\xml\Products.xml',SINGLE_BLOB) AS Products(Product)

DECLARE @hdoc INT

Exec sp_xml_preparedocument @hdoc OUTPUT, @xml

SELECT *
FROM OPENXML (@hdoc,'/Subcategories/Subcategory/Products/Product',2)
WITH (	ProductID INT 'ProductID[@type=("1")]',
ProductID2 INT 'ProductID[@type=("2")]',
Name VARCHAR(100),
ProductNumber VARCHAR(100),
ListPrice FLOAT,
ModifiedDate DATETIME)
1 Like