铸文字XML错误非法限定名称字符

问题描述：

如何修正错误非法限定名称的字符，该样品中：铸文字XML错误非法限定名称字符

Declare @Str As nvarchar(256) 
Set @Str = N'<Log "ReceiptStockHNo="2" ReceiptStockHDate="Feb 4 2" Comment="" />' 
Select Cast(@Str As xml)

错误：

消息9455，级别16，状态1，5号线
XML分析：行1，字符6，非法限定名称字符

答

这是什么额外"？。

取出"，它会工作。

其他信息：

为了防止未来的错误对需要编码的字符，如&，<，使用适当的替换：

Declare @Str As nvarchar(256) 
Set @Str = '<tag>&</tag>' 
Select Cast(@Str As xml)

将产生：

消息9421，级别16 ，状态1，行3 XML解析：行1，字符7
非法名字字符

当改变<到<：

Declare @Str As nvarchar(256) 
Set @Str = '<tag>&lt;</tag>' 
Select Cast(@Str As xml)

都会好的。

，感谢的答案，如何删除“字符与T-SQL – Iraj

@Iraj我相信这是一个新的问题，因此问它作为一个？新的问题。（但在问之前选择）:-) –

答

注意：这不是一个答案，而是一个扩展的评论。

娄串

N'<Log "ReceiptStockHNo="2" ReceiptStockHDate="Feb 4 2" Comment="" />'

它不是一个有效的XML，因为"从"ReceiptStockHNo（第一属性的名称）。如果XML数据是内置的使用字符串连接并且不使用专用的XML API /功能，则可能会发生这种情况。

例如，如果（1）属性名称被存储在作为TABLE.COLUMN

"ReceiptStockHNo

和（2）来构建XML文档/使用片段字符串连接我们可能得到一个无效的XML。一种解决方案可能是FOR XML。见下面的例子和尾注：

DECLARE @Table1 TABLE(
    ID INT NOT NULL PRIMARY KEY, 
    RowType TINYINT NOT NULL, -- 1 = Log [record] 
    Attribute1 NVARCHAR(100) NOT NULL, 
    Attribute1_Value INT, 
    Attribute2 NVARCHAR(100) NOT NULL, 
    Attribute2_Value DATETIME 
) 
INSERT @Table1 
VALUES (123, 1, N'"ReceiptStockHNo', 2, N'ReceiptStockHDate', '2014-02-04 00:00:00.000') 

-- Get data as XML: method #1 (wrong) 
DECLARE @x NVARCHAR(256) 
SELECT @x = N'<Log ' + t.Attribute1 + '="' + CONVERT(VARCHAR(11), t.Attribute1_Value) + '" ' + t.Attribute2 + '="' + CONVERT(VARCHAR(25), t.Attribute2_Value) + '" Comment="" />' 
FROM @Table1 t 
WHERE t.ID = 123 
AND  t.RowType = 1 -- Log [record] 

SELECT @x AS [Get data as XML: method #1 (wrong)] 
SELECT 'Convert to XML' AS [Message] 
BEGIN TRY 
    SELECT CONVERT(XML, @x) AS [Convert to XML result] 
END TRY 
BEGIN CATCH 
    SELECT ERROR_MESSAGE() AS [Get data as XML: method #1 (wrong) - ERROR_MESSAGE] 
END CATCH 

-- Get data as XML: method #2 (ok) 
SET @x = N'' 
SET @x = 
(
    SELECT t.Attribute1_Value AS '"ReceiptStockHNo', 
      t.Attribute2_Value AS 'ReceiptStockHDate', 
      '' AS Comment 
    FROM @Table1 t 
    WHERE t.ID = 123 
    AND  t.RowType = 1 -- Log [record] 
    FOR XML RAW('Log') 
) 

SELECT @x AS [Get data as XML: method #2 (ok)] 
SELECT 'Convert to XML' AS [Message] 
SELECT CONVERT(XML, @x) AS [Convert to XML result]

输出：

Get data as XML: method #1 (wrong) 
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
<Log "ReceiptStockHNo="2" ReceiptStockHDate="Feb 4 2014 12:00AM" Comment="" /> 

Message 
-------------- 
Convert to XML 

Convert to XML result 
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 

Get data as XML: method #1 (wrong) - ERROR_MESSAGE 
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
XML parsing: line 1, character 6, illegal qualified name character 

Get data as XML: method #2 (ok) 
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
<Log _x0022_ReceiptStockHNo="2" ReceiptStockHDate="2014-02-04T00:00:00" Comment=""/> 

Message 
-------------- 
Convert to XML 

Convert to XML result 
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
<Log _x0022_ReceiptStockHNo="2" ReceiptStockHDate="2014-02-04T00:00:00" Comment="" />

当我使用FOR XML生成的XML数据我得到不同的结果，因为FOR XML编码保留XML字符（包括那些从qualified names）。在这种情况下，"被编码为_x0022_：

"ReceiptStockHNo与_x0022_ReceiptStockHNo

铸文字XML错误非法限定名称字符

相关推荐