SQL SERVER xml with CDATA

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>

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

  • How can I decode SQL Server traffic with wireshark?
  • How to escape simple SQL queries in C# for SqlServer
  • Audit log in asp.net core and dapper
  • Get the smallest date from table with unique records in sql server
  • Split values by hyphen that exist in the same row and integrate with a INNER JOIN?
  • select where x in y with varying lengths
  • It works well except for cdata, whose content in the query output is: text -> text

    Is there a way to retrieve also the CDATA tags?

    One Solution collect form web for “SQL SERVER xml with CDATA”

    Well, this is – as far as I know – not possible on normal ways…

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

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

    The big question is: Why?

    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?

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

    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;  
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.