sql - 在 SQL Server 中转换为具有特殊字符名称的 XML

Question

在 sql 表中数据类型为 VARCHAR(MAX) 的列中存在以下格式的 XML

<?xml version="1.0" encoding="UTF-8"?>
<APIDATA xmlns="mynamespace"> 
<TRADE Action="Insert" CompanyID="33" Trader="Aleš Holubec" 
</TRADE>
</APIDATA>

我需要获取 Action 和 CompanyID 的属性值。即插入和 33

在 SQL 中，我使用了下面的查询

;WITH XMLNAMESPACES(DEFAULT 'mynamespace')

SELECT CONVERT(XML,column_name).value ('(APIDATA/TRADE/@Action)[1]', 'varchar(100)')

但是我得到以下错误

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

原因是 Aleš Holubec 是我们无法更改的 XML 中的交易者名称。请帮忙解决这个问题

Answer 1

您可以通过将字符串转换为 nvarchar(max) 然后将编码替换为 UTF-16 来解决此问题。

;WITH XMLNAMESPACES(DEFAULT 'mynamespace')
SELECT CONVERT(XML, replace(cast(column_name as nvarchar(max)), '<?xml version="1.0" encoding="UTF-8"?>', '<?xml version="1.0" encoding="UTF-16"?>')).value ('(APIDATA/TRADE/@Action)[1]', 'varchar(100)')

或者您可以只删除 XML 声明。

;WITH XMLNAMESPACES(DEFAULT 'mynamespace')
SELECT CONVERT(XML, replace(column_name, '<?xml version="1.0" encoding="UTF-8"?>', '')).value ('(APIDATA/TRADE/@Action)[1]', 'varchar(100)')