Handle XML to SQL output

Hello sql expert,

I am working to extract below xml file using sql query. I can extract them out if the element is single but the issue I am having is I have 2 Products' element which is now return the same value.

Here is my XML file that I am using:

Note: below is the xml in text format:

<Subcategories>
	<Subcategory ProductSubcategoryID = "18" Name="Bib-Shorts">
	<Products>
		<Product>
			<ProductID type="1">855</ProductID>
			<ProductID type="2">676</ProductID>
			<Name>Men's Bib-Short, s</Name>
			<ProductNumber>SB-M891-S</ProductNumber>
			<ListPrice>89.9900</ListPrice>
			<ModifiedDate>2008-03-11T10:01:36.827</ModifiedDate>
		</Product>
	</Products>
	</Subcategory>
</Subcategories>

and here is my sql query with single element of Product:

DECLARE @xml XML

SELECT @xml = CONVERT(XML,Product,2)
FROM OPENROWSET (BULK 'C:\Users\Melinda\Desktop\XML test of Product\Product.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,
Name VARCHAR(100),
ProductNumber VARCHAR(100),
ListPrice FLOAT,
ModifiedDate DATETIME)

and here is the result set from above query:

and here is the query with second Product I am trying to get but it giving me a NULL value.

How do I handle this issue?

Before answering your question, the bigger question is why does one product have 2 ProductID ?

Hi yosiasz, I updated the Product elements that has two different attribute. So, those two attribute are the reason why the two ProductID appears twice. Thank you yosiasz.

please post the xml as text instead of picture? we cant scrape data from pictures. we would have to type all of that xml to find a solution for you.

1 Like

Just uploaded the xml file as text, thank you yosiasz.

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

I tried and still returning NULL on both Products, yosiasz

really? It is returning both values for me. what version of sql server do you have?

1 Like

I am using sql server 2014, yosias. What version are you using, yosiasz.

yosiasz, it is working. I was using old product old xml files. Thank you for your help, yosiasz. You are smart!!

not really, I just read documentation :slight_smile:

1 Like

Hi yosiasz,

Quick question, on below section,

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

If I want to create the 'C:\Users\Melinda\Desktop\XML test of Product\Product.xml' as a parameter.

So, I am thinking like this:

SELECT @xml = CONVERT(XML,Product,2)
FROM OPENROWSET (BULK @path,SINGLE_BLOB) AS Products(Product)

Do you know how to replace the string of 'C:\Users\Melinda\Desktop\XML test of Product\Product.xml' with @parameter?

http://weblogs.sqlteam.com/mladenp/archive/2007/06/18/60235.aspx

1 Like
DECLARE @xml XML, @xmlFileName varchar(25) = 'C:\xml\Products.xml', @hdoc INT

create TABLE #XmlImportTest(xmlFileName varchar(50),  xml_data xml);

-- dynamic sql is just so we can use @xmlFileName variable in OPENROWSET
EXEC('
INSERT INTO #XmlImportTest(xmlFileName, xml_data)

SELECT ''' + @xmlFileName + ''', xmlData 
FROM
(
    SELECT  * 
    FROM    OPENROWSET (BULK ''' + @xmlFileName + ''' , SINGLE_BLOB) AS XMLDATA
) AS FileImport (XMLDATA)
')

select @xml = xml_data FROM #XmlImportTest;

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)


DROP TABLE #XmlImportTest
1 Like

Thank you so much for all your help, yosiasz!

Hello yosiasz,

some of the xml file has the header of:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE product-content SYSTEM "product-content">

So, some of the Product xml files work with above query but giving me an error of:

Parsing XML with internal subset DTDs not allowed. Use CONVERT with style option 2 to enable limited internal subset DTD support.

I know this has something to do with the SELECT CONVERT (XML, BulkColumn,2) or SELECT CONVERT (XML, BulkColumn,1), how do I handle this issue when XML file has the header like:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE product-content SYSTEM "product-content">

Appreciated for all your help, thank you yosiasz