带有 CDATA 块的 SQL Server XML 数据

It is a very bad approach to store XML within a CDATA section. Everything within this block is just text. This special text looks like an XML, but you cannot query this XML to return the <companyName>.

试试这个:

DECLARE @xml XML=
N'<event>
  <projectId>123456</projectId>
  <eventTs>2018-01-04T13:07:23</eventTs>
  <eventData>
    <![CDATA[
    <company>
      <companyId>849</companyId>
      <companyName>My Company Name</companyName>
      <activeFlag>Y</activeFlag>
      <timestamp>27-JUL-17</timestamp>
    </company>
    ]]>
  </eventData>
</event>';

SQL Server 的开发人员甚至决定不再支持 CDATA.它会被隐含地带走，而它的内容仍然被正确地转义.但是你可以毫无问题地阅读内容:

SQL Server's developer decided not even to support CDATA anymore. It will implicitly be taken away, while its content remains properly escaped. But you can read the content without problems:

SELECT @xml.value('(/event/eventData)[1]','nvarchar(max)');  

重点是:这个结果看起来像一个 XML，但是 - 为了使用它像一个 XML - 它必须被强制转换.

The point is: This result looks like an XML, but - in order to use it like an XML - it must be casted.

你可以这样做来解决这个问题:

This you could do to solve this:

DECLARE @innerXML XML=(SELECT CAST('<eventData>' + @xml.value('(/event/eventData)[1]','nvarchar(max)') + '</eventData>' AS XML));
SET @xml.modify('delete /event/eventData[1]');
SET @xml.modify('insert sql:variable("@innerXML") as last into /event[1]');
SELECT @xml;

简单易行:

只要传入的 XML 是一个字符串(在您尝试将其转换为 XML 之前)，您就可以丢弃 CDATA 标记:

DECLARE @xmlString NVARCHAR(MAX)=
N'<event>
  <projectId>123456</projectId>
  <eventTs>2018-01-04T13:07:23</eventTs>
  <eventData>
    <![CDATA[
    <company>
      <companyId>849</companyId>
      <companyName>My Company Name</companyName>
      <activeFlag>Y</activeFlag>
      <timestamp>27-JUL-17</timestamp>
    </company>
    ]]>
  </eventData>
</event>';

SELECT CAST(REPLACE(REPLACE(@xmlString,' <![CDATA[',''),']]>','') AS XML)

这篇关于带有 CDATA 块的 SQL Server XML 数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持html5模板网！