Error when XML Path within a parameter

Transact-SQL
1

Hello,

I am querying 2 identical xml files but one with the header (Product2) and one without header (Product).

XML file with header (Product2)

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE product-content SYSTEM "product-content">
<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>

XML file with no header (Product):

<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>

Here is my sql query using XML file without header (Product):

DECLARE @xml XML,@hdoc INT 

	CREATE TABLE XmlImportTest
	(
		xmlFileName VARCHAR(300),
		xml_data xml
	)
	
DECLARE @xmlFileName VARCHAR(300)

SELECT  @xmlFileName = 'C:\Users\Melinda\Desktop\Product\Product.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  * --@xml = CONVERT(XML,XMLDATA,2)
				FROM    OPENROWSET (BULK ''' + @xmlFileName + ''' , SINGLE_BLOB) AS XMLDATA
			) AS FileImport (XMLDATA)
		 ')

SELECT * FROM XmlImportTest

DROP TABLE XmlImportTest

The query is working and returning result set with xml that has no header, but returning error when I use the xml file that has the header (Product2).

SELECT @xmlFileName = 'C:\Users\Melinda\Desktop\Product2\Product.xml'

Here is the error msg:

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

I know the error is because of the header:

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

I think I need to convert the OPENROWSET to 2 but not sure how to fix this and make the query running using the xml file that has header as above, I am new and still learning the XML.

SELECT CONVERT (XML, BulkColumn,2) or SELECT CONVERT (XML, BulkColumn,1)

Anyone can help me to resolve this issue? Thank you all

2

have you tried your solution?

3

I am trying but still no idea how to make it work, yosiasz.

4

you have to be a bit more curious and try things

SELECT *
FROM    OPENROWSET (BULK 'C:\XML\Products.xml' , SINGLE_BLOB) AS XMLDATA

gives you
0x3C3F786D6C2076657273696F6E3D22312E302220656E636F64696E673D225.....
hence it cannot insert that into an xml column data

so you have to convert it to xml

EXEC	('
			INSERT INTO XmlImportTest(xmlFileName, xml_data)
			SELECT ''' + @xmlFileName + ''', xmlData 
			FROM
			(			
			    SELECT CONVERT (XML, BulkColumn,2)	
				--SELECT  * --@xml = CONVERT(XML,XMLDATA,2)
				FROM    OPENROWSET (BULK ''' + @xmlFileName + ''' , SINGLE_BLOB) AS XMLDATA
			) AS FileImport (XMLDATA)
		 ')
1 Like
5

Ah, ic...I used the SELECT CONVERT (XML, XMLDATA,2) instead CONVERT (XML, BulkColumn,2)

It is working yosiasz. I do really appreciated for all your help and time. You are definitely good in sql.

Thanks again

6

no. i am just good at reading the documentation.

1 Like