SQLTeam.com | Weblogs | Forums

Error when XML Path within a parameter

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

have you tried your solution?

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

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

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

no. i am just good at reading the documentation.

1 Like