具有CDATA的SQL SERVER XML [英] SQL SERVER xml with CDATA

问题描述

我的数据库中有一个表，其中包含xml列。列类型为nvarchar（max）。 xml以这种方式形成

I have a table in my database with a column containing xml. The column type is nvarchar(max). The xml is formed in this way

<root>
  <child>....</child>
  .
  .
  <special>
   <event><![CDATA[text->text]]></event>
   <event><![CDATA[text->text]]></event>
  ...
  </special>
</root>

我尚未创建数据库，无法更改信息存储在数据库中的方式，但可以检索选择它。对于提取，我使用
从表中选择cast（replace（xml，'utf-8'，'utf-16'）as xml）

I have not created the db, I cannot change the way information is stored in it but I can retrieve it with a select. For the extraction I use select cast(replace(xml,'utf-8','utf-16')as xml) from table

除了cdata以外，它都很好用，cdata在查询输出中的内容是：text->;文本

It works well except for cdata, whose content in the query output is: text -> text

是否也可以检索CDATA标记？

Is there a way to retrieve also the CDATA tags?

推荐答案

嗯，据我所知，这是正常方式下不可能的...

Well, this is - as far as I know - not possible on normal ways...

CDATA 部分的唯一原因：在 XML内包含无效字符，对于懒惰的人来说 ...

The CDATA section has one sole reason: include invalid characters within XML for lazy people...

CDATA 根本没有必要，因此普通XML方法并没有真正支持它。或换句话说：在某种程度上，它支持对内容进行正确的转义。实际上， CDATA 中正确转义的内容与未转义的内容之间没有区别！ （好吧，有一些细微的差别，例如包含 CDATA 中的]]> -section和一些更小的特色菜...）

CDATA is not seen as needed at all and therefore is not really supported by normal XML methods. Or in other words: It is supported in the way, that the content is properly escaped. There is no difference between correctly escaped content and not-escaped content within CDATA actually! (Okay, there are some minor differences like including ]]> within a CDATA-section and some more tiny specialties...)

什么是您之后尝试这样做吗？

What are you trying to do with this afterwards?

尝试一下。包含的文本按原样给出 ：

Try this. the included text is given as is:

DECLARE @xml XML = 
'<root>
  <special>
   <event><![CDATA[text->text]]></event>
   <event><![CDATA[text->text]]></event>
  </special>
</root>'

SELECT t.c.query('text()')
FROM @xml.nodes('/root/special/event') t(c);

因此：请解释一些详细信息：您真正想要什么？

So: Please explain some more details: What do you really want?

如果除了包装 CDATA 外，您真的需要什么，可以使用以下命令：

If your really need nothing more than the wrapping CDATA you might use this:

SELECT '<![CDATA[' + t.c.value('.','varchar(max)') + ']]>'
FROM @xml.nodes('/root/special/event') t(c);

更新：与过时的 FROM OPENXML

我刚刚尝试了使用 FROM OPENXML 的过时方法来处理此问题，发现，绝对没有迹象表明结果集，给出的 text 最初位于 CDATA 部分中。完全以与 CDATA 中的文本相同的方式返回此处的某些值：

Update: Same with outdated FROM OPENXML

I just tried how the outdated approach with FROM OPENXML handles this and found, that there is absolutely no indication in the resultset, that the given text was within a CDATA section originally. The "Some value here" is exactly returned in the same way as the text within CDATA:

DECLARE @doc XML = 
'<root>
  <child>Some value here </child>
  <special>
   <event><![CDATA[text->text]]></event>
   <event><![CDATA[text->text]]></event>
  </special>
</root>';

DECLARE @hnd INT;

EXEC sp_xml_preparedocument @hnd OUTPUT, @doc;  

SELECT * FROM OPENXML (@hnd, '/root',0);

EXEC sp_xml_removedocument @hnd;  

这篇关于具有CDATA的SQL SERVER XML的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！