Reputation: 227
I have a valid XML document (this has been confirmed using multiple XML validators including online validators and the Sublime Text XML validator plugin).
I receive the following error when attempting to import the XML document into MSSQL 2008 using a stored procedure named ImportNXML (command: exec [dbo].[ImportNXML];)
Msg 9420, Level 16, State 1, Line 2
XML parsing: line 17, character 35, illegal xml character
I have confirmed no illegal characters are in the XML document and line 17, character 35 is just the number 1. I've tried modifying this line, replacing the entire line with letters, replacing the entire line with a single number, padding other lines in the document before this line with letters/numbers, but i receive exactly the same error complaining about the exact same location.
If i open the ImportNXML stored procedure and run the query contents, i receive no errors at all.
What could be causing the stored procedure to fail when being executed using the 'exec' command but succeed when the procedure contents are executed as an expanded query?
Mock data for the first 17 lines is as follows:
<?xml version="1.0" ?>
<ClientData>
<Policy><policyName>The Policy Name</policyName>
<Preferences><ServerPreferences><preference><name>Sessions</name>
<value>3</value>
</preference>
<preference><name>Detection</name>
<value>yes</value>
</preference>
<preference><name>Mac</name>
<value>no</value>
</preference>
<preference><name>Plugin</name>
<value>108478;84316;32809;93635;36080;87560;61117;35292;75260;83156;61271;103773;12899;82513;56376;77796;85655;60338;56763;79951;</value>
</preference>
<preference><name>TARGET</name>
<value>123.123.123.123,234.234.234.234</value>
The portion of the stored proc that imports the XML is as follows:
EXEC(' INSERT INTO XmlImportTest(xmlFileName, xml_data) SELECT ''' + @importPath + ''', xmlData FROM ( SELECT * FROM OPENROWSET (BULK ''' + @importPath + ''' , SINGLE_BLOB) AS XMLDATA ) AS FileImport (XMLDATA) ')
Upvotes: 1
Views: 5866
Reputation: 67341
Pure guessing:
utf-8
encoded (or any other encoding, SQL-Server 2008 cannot read natively).
CHAR
(or VARCHAR
) is extended ASCII 1-byte encoding
and NCHAR
(or NVARCHAR
) is UCS-2 2-byte encoding
(which is almost identical with UTF-16
).utf-8
.CLOB
instead of BLOB
. Reading the file as binary LargeObject will take the bytes one after the next. SQL-Server will try to read these bytes as string with fixed size per character. A character LOB might work under special circumstances.BOM
(byte order mark)NVARCHAR(MAX)
or even VARBIANRY(MAX)
target columns) and try to continue with this.Upvotes: 2