sql - 从存储为 nvarchar 的 xml 中提取数据

Question

我必须提取电子邮件 Subject来自 XML存储为 nvarchar .

我正在使用这个查询:

SELECT rtrim((SELECT CAST(
                    SUBSTRING(
                        [XML]
                        ,patindex('%<SUBJECT>%', [XML])
                        ,patindex('%</SUBJECT>%', [XML])-patindex('%<SUBJECT>%', [XML])+len('</SUBJECT>')
                    ) as XML).value('(/SUBJECT/OPTION)[1]','nvarchar(2000)')
            FROM   dbo.Mails 
)) as SUBJECT

此查询首先提取包含电子邮件主题 ( <SUBJECT>....</SUBJECT> ) 的字符串，我将其转换为 XML，然后使用 value 获取主题值XML 函数。

这工作正常，但在某些情况下，XML 格式不正确并且解析失败。示例:

DECLARE @XMLData XML =  '<SUBJECT>
      <OPTION CONSTRAINT="MASTER.IN_TITLE = ''  OR  MASTER.IN_LASTNAME = ''"><![CDATA[Découvrez nos offres de location]]>
      </OPTION>
      <OPTION CONSTRAINT="IN_TITLE <> '' AND MASTER.IN_LASTNAME <> ''"><![CDATA[~IN_TITLE~ ~IN_LASTNAME~, découvrez nos offres de location]]>
      </OPTION>
   </SUBJECT>'

select rtrim((@XMLData).value('(/SUBJECT/OPTION)[1]','nvarchar(2000)') )

在 Option 中属性 Constraint我有特殊性格< ，如果我尝试转义这个字符，它会转义所有其他字符，并且我丢失了 XML 结构。那么如何逃脱呢？

另一个例子是这样的:

DECLARE @XMLData XML =  '<SUBJECT>
      <OPTION NAME="DEFAULT"><![CDATA[~(IF((IN_TITLE<>'' AND IN_LASTNAME<>''),IN_TITLE&' '&IN_LASTNAME&',',''))~ nos plus belles réalisations de 2015]]>
      </OPTION>
   </SUBJECT>'

select rtrim((@XMLData).value('(/SUBJECT/OPTION)[1]','nvarchar(2000)') )

这里我在 &IN_LASTNAME& 附近出错但我认为我们不需要转义 CDATA 中的特殊字符!

有没有人有解决方案来避免这些解析错误？

Answer 1

这些 XML 是如何生成的？这在你的控制之下吗？邪恶的三个字符“<、>和&必须特殊对待，要么CDATA，要么转义。如果 XML 生成正确，它应该不可能将它们放在禁止的地方...

这里有两个工作示例。第二个与 Rhys Jones 相同......在第一个示例中，我将“<>”替换为 <>。

顺便说一句:当您显然要处理其他特殊字符时，您应该用 N'string' 标记您的字符串以将其读取为 unicode。

DECLARE @XMLData XML =  REPLACE(N'<SUBJECT>
      <OPTION CONSTRAINT="MASTER.IN_TITLE = ''  OR  MASTER.IN_LASTNAME = ''"><![CDATA[Découvrez nos offres de location]]>
      </OPTION>
      <OPTION CONSTRAINT="IN_TITLE <> '' AND MASTER.IN_LASTNAME <> ''"><![CDATA[~IN_TITLE~ ~IN_LASTNAME~, découvrez nos offres de location]]>
      </OPTION>
   </SUBJECT>','<>','&lt;&gt;');

select rtrim((@XMLData).value('(/SUBJECT/OPTION)[1]','nvarchar(2000)') );
GO

DECLARE @XMLData XML =  N'<SUBJECT>
      <OPTION NAME="DEFAULT"><![CDATA[~(IF((IN_TITLE<>'''' AND IN_LASTNAME<>''''),IN_TITLE&'' ''&IN_LASTNAME&'','',''''))~ nos plus belles réalisations de 2015]]>
      </OPTION>
   </SUBJECT>'

select rtrim((@XMLData).value('(/SUBJECT/OPTION)[1]','nvarchar(2000)') )