問題描述

我有以下查詢，它有效:

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 &奶酪，&l??t;test1>，<test2>

你唯一需要改變的是使用 (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"

這篇關于預期 XML 解析分號 - 僅在替換 & 時與 &amp的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！