Error when XML Path within a parameter

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