预期 XML 解析分号 - 仅在替换 & 时与 &amp

2021-10-02 00:00:00 xml-parsing sql-server

我有以下查询，它有效:

I have the following query, which works:

DECLARE @Combined VARCHAR(MAX)

SET @Combined = 'mac cheese';

DECLARE @KeyTable TABLE (Keyword VARCHAR(MAX))
INSERT INTO @KeyTable
SELECT @Combined

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
    DROP TABLE #Temp

SELECT LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS Keyword
    INTO #Temp
FROM
(
SELECT CAST('<XMLRoot><RowData>' + REPLACE(Keyword,',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
FROM @KeyTable
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n);

SELECT * FROM #Temp

当我尝试向 @Combined 添加一个&符号时，它给出了一个特殊字符错误:

When I try to add an ampersand to the @Combined it gives a special character error:

DECLARE @Combined VARCHAR(MAX)

SET @Combined = 'mac & cheese';

DECLARE @KeyTable TABLE (Keyword VARCHAR(MAX))
INSERT INTO @KeyTable
SELECT @Combined

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
    DROP TABLE #Temp

SELECT LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS Keyword
    INTO #Temp
FROM
(
SELECT CAST('<XMLRoot><RowData>' + REPLACE(Keyword,',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
FROM @KeyTable
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n);

SELECT * FROM #Temp

所以我添加了一些代码来用 &amp 替换 & 号，但我得到了一个分号错误:

So I added some code to replace the ampersand with &amp and I get a semicolon error:

DECLARE @Combined VARCHAR(MAX)

SET @Combined = 'mac & cheese'

SET @Combined = REPLACE(@Combined, '&', '&amp')

DECLARE @KeyTable TABLE (Keyword VARCHAR(MAX))

INSERT INTO @KeyTable
SELECT @Combined;

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
    DROP TABLE #Temp

SELECT LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS Keyword
    INTO #Temp
FROM
(
SELECT CAST('<XMLRoot><RowData>' + REPLACE(Keyword,',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
FROM @KeyTable
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n);

SELECT * FROM #Temp

我尝试将分号移动到不同的位置，甚至添加更多，但它不起作用.任何人都可以提供的任何帮助将不胜感激！

I've attempted moving the semicolon to various locations and even adding more but it just won't work. Any help anyone can provide would be appreciated!

推荐答案

不，你不应该开始自己替换特殊字符！

No, you should not start to replace special characters on your own!

下次您的字符串包含 < 或 -> 并且会再次中断.

Next time your string contains a < or a -> and will break again.

让 FOR XML PATH() 为您完成繁重的工作:

Let FOR XML PATH() do the hard work for you:

SELECT 'This is pure text: ' + 'mac & cheese,<test1>,"test2"';
SELECT 'This is encoded: ' + (SELECT 'mac & cheese,<test1>,"test2"' AS [*] FOR XML PATH(''))

第二个结果:这是编码:mac &amp;奶酪，&l​​t;test1>，&lt;test2&gt;

你唯一需要改变的是使用 (SELECT ... FOR XML PATH()) 而不是 naked Keyword>

The only thing you have to change, is to use (SELECT ... FOR XML PATH()) instead of the naked Keyword

DECLARE @KeyTable TABLE (Keyword VARCHAR(MAX))
INSERT INTO @KeyTable VALUES('mac & cheese,<test1>,"test2"');

SELECT LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS Keyword
FROM
(
SELECT CAST('<XMLRoot><RowData>' + REPLACE((SELECT Keyword AS [*] FOR XML PATH('')),',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
FROM @KeyTable
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n);

这将隐式替换所有禁用字符...

This will replace all forbidden characters implicitly...

结果

mac & cheese
<test1>
"test2"

相关文章