带有 CDATA 块的 SQL Server XML 数据

2021-10-02 00:00:00 xml xpath sql-server

我将 XML 负载存储在数据类型为XML"的 SQL Server 表中.我收到的数据有一个包含在 CDATA 块中的部分.下面是一个例子:

I am storing an XML payload in a SQL Server table that has a datatype of 'XML'. The data I am receiving has a section that is enclosed in a CDATA block. Here is an example:

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

当这些数据出现在我的表中具有XML"数据类型的字段中时，CDATA"块被剥离，但随后所有的<"和 ">" 字符被转义.由于这些字符已被转义，因此对该数据字段的 XPATH 查询不再有效.除了必须在插入/转换为 XML 数据类型之前去掉 CDATA 块之外，还有什么办法可以解决这种行为?

when this data lands in my table in the field that has a data type of 'XML' the 'CDATA' block is stripped out but then all of the "<" and ">" characters are escaped. Since those characters are escaped, XPATH queries on that data field no longer work. Is there any way around this behavior short of having to strip out the CDATA block before it is inserted/converted to an XML data type?

这是插入到 XML 数据类型字段后的数据:

This is what the data looks like after being inserted into the XML datatype field:

<event>
    <projectId>123456</projectId>
    <eventTs>2018-01-04T13:07:23</eventTs>
    <eventData>
&lt;company&gt;
          &lt;companyId&gt;849&lt;/companyId&gt;
&lt;companyName&gt;My Company Name&lt;/companyName&gt;
&lt;activeFlag&gt;Y&lt;/activeFlag&gt;
&lt;timestamp&gt;27-JUL-17&lt;/timestamp&gt;
&lt;/company&gt;
</eventData>
</event>

推荐答案

在 CDATA 部分中存储 XML 是一种非常糟糕的方法.此块中的所有内容都只是文本.此特殊文本看起来像一个 XML，但您无法查询此 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)

相关文章