Pouring text into xml error illegal qualified character name

Question

how to fix an illegal qualified name error in this example:

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 character name

Answer 1

What is this extra? ".

Remove " and it will work.

Additional Information:

To prevent future errors for the desired encoded characters, such as & , < , 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 symbol

When changing < to < :

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

Will be good.

Answer 2

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

Bottom row

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

it is not valid XML due to " from "ReceiptStockHNo (name of the first attribute). This can happen if XML data is created using string concatenation and does not use dedicated XML API / functionality.

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

 "ReceiptStockHNo 

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

 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 XML data, I get different results because FOR XML encodes reserved XML characters (including from qualified names ). In this case, " was encoded as _x0022_ :

"ReceiptStockHNo vs. _x0022_ReceiptStockHNo