sql-server - SQL 插入中存在非法 xml 字符

Question

我正在尝试将以下字符串插入到 sql xml 字段中

<?xml version="1.0" encoding="UTF-8"?>
<Response> 
    <Ip>x.x.x.x</Ip>
    <CountryCode>CA</CountryCode> 
    <CountryName>Canada</CountryName>
    <RegionCode>QC</RegionCode> 
    <RegionName>Québec</RegionName> 
    <City>Dorval</City> 
    <ZipCode>h9p1j3</ZipCode> 
    <Latitude>45.45000076293945</Latitude> 
    <Longitude>-73.75</Longitude> 
    <MetroCode></MetroCode> 
    <AreaCode></AreaCode> 
</Response>

插入代码如下所示:

INSERT 
    INTO Traffic(... , xmlGeoLocation, ...)
    VALUES (
        ...
        <!--- 
        <cfqueryparam CFSQLType="cf_sql_varchar" value="#xmlGeoLocation#">, 
        --->
        '#xmlGeoLocation#',

        ...
        )

发生了两件坏事:

1. 魁北克变成了魁北克

2. 我收到一条错误消息:[Macromedia][SQLServer JDBC Driver][SQLServer]XML parsing: line 8, character 16, invalid xml character

更新:

传入的测试流大部分是单字节字符。

é 是一个两字节字符。特别是C3A9

而且我无法控制传入的 xml 流

Answer 1

我要去掉标题...

我对一个有趣的小撇号也有同样的问题。我认为问题在于，当字符串转换为 XML 时，它不再是 UTF-8，但 sql server 正在尝试使用 header 对其进行解码。如果是 VARCHAR，则采用客户端编码。如果是 NVARCHAR，则为 UTF-16。以下是我测试过的一些变体:

SQL(varchar、UTF-8):

SELECT CONVERT(XML,'<?xml version="1.0" encoding="UTF-8"?><t>We’re sorry</t>')

错误:

XML parsing: line 1, character 44, illegal xml character

SQL(nvarchar、UTF-8):

SELECT CONVERT(XML,N'<?xml version="1.0" encoding="UTF-8"?><t>We’re sorry</t>')

错误: XML解析:第1行，第38个字符，无法切换编码

SQL(varchar、UTF-16)

SELECT CONVERT(XML,'<?xml version="1.0" encoding="UTF-16"?><t>We’re sorry</t>')

错误:

XML parsing: line 1, character 39, unable to switch the encoding

SQL(nvarchar、UTF-16)

SELECT CONVERT(XML,N'<?xml version="1.0" encoding="UTF-16"?><t>We’re sorry</t>')

成功了!