問題描述

我有一個(gè)有效的 XML 文檔(已使用多個(gè) XML 驗(yàn)證器進(jìn)行確認(rèn)，包括在線驗(yàn)證器和 Sublime Text XML 驗(yàn)證器插件).

嘗試使用名為 ImportNXML 的存儲(chǔ)過程將 XML 文檔導(dǎo)入 MSSQL 2008 時(shí)收到以下錯(cuò)誤(命令:exec [dbo].[ImportNXML];)

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

我已經(jīng)確認(rèn) XML 文檔中沒有非法字符，第 17 行，字符 35 只是數(shù)字 1.我嘗試修改這一行，用字母替換整行，用單個(gè)數(shù)字替換整行，在此行之前用字母/數(shù)字填充文檔中的其他行，但我收到完全相同的錯(cuò)誤，抱怨完全相同的位置.

如果我打開 ImportNXML 存儲(chǔ)過程并運(yùn)行查詢內(nèi)容，我根本不會(huì)收到任何錯(cuò)誤.

什么可能導(dǎo)致存儲(chǔ)過程在使用 'exec' 命令執(zhí)行時(shí)失敗，但在過程內(nèi)容作為擴(kuò)展查詢執(zhí)行時(shí)會(huì)成功?

前17行的mock數(shù)據(jù)如下:

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>

導(dǎo)入 XML 的存儲(chǔ)過程的部分如下:

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

推薦答案

純猜測(cè):

• 該文件是 utf-8 編碼的(或任何其他編碼，SQL-Server 2008 無(wú)法本地讀取).
  • 您必須知道，SQL-Server 的文件編碼相當(dāng)有限.CHAR(或VARCHAR)是擴(kuò)展的ASCII 1字節(jié)編碼和NCHAR(或NVARCHAR)code>) 是 UCS-2 2 字節(jié)編碼(與 UTF-16 幾乎相同).
  • 在 SQL-Server 2016(以及 v2014 的 SP2)中引入了一些進(jìn)一步的支持，尤其是對(duì) utf-8 的支持.
  • 嘗試使用適當(dāng)?shù)木庉嬈?例如記事本++)打開您的 XML 并嘗試找出文件的編碼.嘗試將其保存為unicode/UCS-2/utf-16"并重試導(dǎo)入.
  • 嘗試使用 CLOB 而不是 BLOB 的導(dǎo)入.以二進(jìn)制LargeObject 形式讀取文件將一個(gè)接一個(gè)地讀取字節(jié).SQL-Server 將嘗試將這些字節(jié)讀取為每個(gè)字符固定大小的字符串.字符 LOB 可能在特殊情況下起作用.
  • 檢查BOM(字節(jié)順序標(biāo)記)的前兩個(gè)字節(jié)
  • The file is utf-8 encoded (or any other encoding, SQL-Server 2008 cannot read natively).
    • You must know, that SQL-Server is rather limited with file encodings. 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).
    • With SQL-Server 2016 (and SP2 for v2014) some further support was introduced, especially for utf-8.
    • Try to open your XML with an appropriate editor (e.g. notepad++) and try to find out the file's encoding. Try to save this as "unicode / UCS-2 / utf-16" and retry the import.
    • Try to use your import with 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.
    • Check the first two bytes for a BOM (byte order mark)
    • 使用十六進(jìn)制編輯器打開文件并嘗試查找奇怪的代碼
    • 在這種情況下，有時(shí)您會(huì)遇到截?cái)嗷驍嘈幸?hào)
    • 如果您導(dǎo)入數(shù)據(jù)并且預(yù)計(jì)會(huì)出現(xiàn)問題，強(qiáng)烈建議使用兩步法
    • 將您的文件讀入一個(gè)容忍臨時(shí)表(使用NVARCHAR(MAX) 甚至VARBIANRY(MAX) 目標(biāo)列)并嘗試?yán)^續(xù)這個(gè).
    • 在導(dǎo)入之前可能需要使用其他工具來更改您的文件.
    • If you import data and you expect issues it is highly recommended to use a 2-step-approach
    • Read your file into a tolerant staging table (with NVARCHAR(MAX) or even VARBIANRY(MAX) target columns) and try to continue with this.
    • It might be necessary to use another tool to change your file before the import.

    這篇關(guān)于XML Parsing - Illegal XML Character(在執(zhí)行存儲(chǔ)過程時(shí)，運(yùn)行過程查詢不會(huì)導(dǎo)致錯(cuò)誤)的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！

    【網(wǎng)站聲明】本站部分內(nèi)容來源于互聯(lián)網(wǎng),旨在幫助大家更快的解決問題，如果有圖片或者內(nèi)容侵犯了您的權(quán)益，請(qǐng)聯(lián)系我們刪除處理，感謝您的支持！