将文本转换为xml错误非法限定名称字符

how to fix error illegal qualified name character in this sample :

如何修复此示例中的错误非法限定名称字符:

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

Error :

Msg 9455, Level 16, State 1, Line 5
XML parsing: line 1, character 6, illegal qualified name character

消息9455,级别16,状态1,行5 XML解析:第1行,字符6,非法限定名称字符

2 个解决方案

#1

What is this extra " ? .

什么是这个额外的“?

Remove the " and it will work.

删除“它会工作。

Additional information :

附加信息 :

To prevent future errors for needed encoded characters like & , < , use the appropriate replacement :

为防止将来出现所需编码字符错误,例如&,<,请使用适当的替换:

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

Will yield :

将产量:

Msg 9421, Level 16, State 1, Line 3 XML parsing: line 1, character 7
illegal name character

消息9421,级别16,状态1,行3 XML解析:第1行,字符7非法名称字符

While changing < to < :

在改变 <>

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

Will be Ok.

会好的。

#2

Note: this is not an answer but an extended comment.

注意:这不是答案,而是扩展评论。

Bellow string

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

it isn't a valid XML because of " from "ReceiptStockHNo (name of first attribute). This might happen if the XML data is built using string concatenation and not using dedicated XML API/functionality.

由于“来自”ReceiptStockHNo(第一个属性的名称),它不是有效的XML。如果使用字符串连接构建XML数据而不使用专用的XML API /功能,则可能会发生这种情况。

For example, if (1) attribute name is stored in a table.column as

例如,如果(1)属性名称存储在table.column中

"ReceiptStockHNo

and (2) to build the XML document/fragment is used string concatenation we might get an invalid XML. One solution might be FOR XML. See next example and the end note:

(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]

Output:

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="" />

When I use FOR XML to generate the XML data I get different results because FOR XML encode reserved XML characters (including those from qualified names). In this case, "was encoded as _x0022_:

当我使用FOR XML生成XML数据时,我得到不同的结果,因为FOR XML编码保留的XML字符(包括来自限定名称的字符)。在这种情况下,“被编码为_x0022_:

"ReceiptStockHNo vs. _x0022_ReceiptStockHNo

“ReceiptStockHNo vs. _x0022_ReceiptStockHNo

#1