时间戳

首页 > 解决方案 > XML 解析：第 21 行，字符 67，非法限定名字符转换错误？

sql - XML 解析：第 21 行，字符 67，非法限定名字符转换错误？

问题描述

我正在使用 SQL Server 2014，当我运行以下查询时，我收到以下错误消息：

XML解析：第21行，字符67，非法限定名字符

这可能是铸造错误吗？我的问题是我不知道问题出在哪里，我已经阅读了一些关于类似问题的问题，但仍然对为什么会发生这种情况感到困惑，任何人都可以帮助我理解错误消息。

上下文：我有一个名为tasks商店p_num（产品编号）的表，descript其中存储了日期和人员更新产品订单的注释。我想检索日期和人名。但是问题是每个 p_num 有多个注释，并且所有注释都存储在每个 p_num 的单个单元格中，并且它们仅由空格分隔。这种方法似乎可以帮助我得到我的结果，但我收到了这个错误消息。

  Sample of text field(Descript):
   11/6/2018 2:04pm Standard pacific time - m.doe I need help with this issue & I might need help with this stupid issue 11/5/2018 1:05pm standard pacific time - m.doe I looked at the issue and I found it difficult to understand 11/4/2018 8:00am standard pacific time - j.moe issue has been registered
   end of all the note.

输出：

P_num    Date        Name
100      11/6/2018   m.doe
100      11/5/2018   m.doe
100      11/4/2018   j.moe

代码：

 DECLARE @tmp13 TABLE
                (
                   [p_Num] INTEGER NOT NULL,
                   [DESCRIPT] NVARCHAR(MAX)
                   PRIMARY KEY([p_Num])
                );

DECLARE @tmp13Parse TABLE
                    (
                        [Id] INT,
                        [Position] BIGINT,
                        [Value] NVARCHAR(500)
                        unique clustered ([Id], [Position])
                    );


 --insert test data
 INSERT INTO @tmp13 
  SELECT p_NUM,DESCRIPT FROM TASKS WHERE p_NUM >250000;



INSERT INTO @tmp13Parse (
                        [Id]
                      , [Position]
                      , [Value]
                    )
        SELECT      [a].[p_Num]
                  , [ss].[Position]
                  , [ss].[Value]
        FROM        @tmp13 [a]
        CROSS APPLY (
                        SELECT      ROW_NUMBER() OVER ( ORDER BY (SELECT 1)) 
 AS [Position]
                                  , [y].[i].[value]('(./text())[1]', 'nvarchar(max)') AS [Value]
                        FROM        (
                                        SELECT [x] = CONVERT(XML, '<i>'+ REPLACE([a].[DESCRIPT], ' ', '</i><i>')+ '</i>').[query]('.')
                                    ) AS [a]
                        CROSS APPLY [x].[nodes]('i') AS [y]([i])
                    ) AS [ss];



 SELECT      [a].[Id]
      , [a].[Value] AS [Date]
      , [ccc].[Value] AS [Name]
 FROM        @tmp13Parse [a]

 CROSS APPLY (
            SELECT MIN([aa].[Position]) + 1 AS [nameAnchorPosition]
            FROM   @tmp13Parse [aa]
            WHERE  [aa].[Id] = [a].[Id]
                   AND [aa].[Value] = '-'
                   AND [aa].[Position] > [a].[Position]
        ) AS [bb]

  CROSS APPLY (
            SELECT [cc].[Value]
            FROM   @tmp13Parse [cc]
            WHERE  [cc].[Id] = [a].[Id]
                   AND [cc].[Position] = [bb].[nameAnchorPosition]
                 )  AS [ccc]
   WHERE       TRY_CONVERT(DATE, [a].[Value]) > '1900-01-01';

标签： sqlsql-serverxml

解决方案

改变这一点可能就足够了

SELECT [x] = CONVERT(XML, '<i>'+ REPLACE([a].[DESCRIPT], ' ', '</i><i>')+ '</i>')

对此

SELECT [x] = CONVERT(XML, '<i>'+ REPLACE((SELECT [a].[DESCRIPT] AS [*] FOR XML PATH('')), ' ', '</i><i>')+ '</i>')

一些解释：

如果你"this & that"用标签连接一个字符串，你会得到类似的东西

<SomeTag>this & that</SomeTag>

但这会失败，因为与号必须用&amp;. 幸运的是，隐式机制会隐式地完成所有转义。试试看嘛：

SELECT (SELECT 'See how &, < and > are escaped?' AS [*] FOR XML PATH(''))

使用这个技巧，您可以将带有禁止字符的字符串放入 XML：

SELECT CAST('<x>' + (SELECT 'See how &, < and > are escaped?' AS [*] FOR XML PATH('')) + '</x>' AS XML);

结果

<x>See how &amp;, &lt; and &gt; are escaped?</x>

推荐阅读