2

I am trying to create a node in xml with CDATA in SQL server 2012. I use the code provided in https://stackoverflow.com/a/1429383/4158652.

There are two main problems 1- my data is not populated inside CDATA i.e. instrtext and hinttext. 2- extra props node in xml

The code I have been using is 

SET @instrProps =
            (
            select * from (             
              SELECT
                    1 AS Tag,
                    NULL AS Parent,                     
                NULL AS 'props!1!instrtext!cdata',
                NULL AS 'props!1!hinttext!cdata',
                NULL AS 'props!1!score!Element'
            UNION ALL
            SELECT
                1 AS Tag,
                NULL AS Parent,                 
                @instText,
                @hintText,
                @score
                ) X             
            FOR XML EXPLICIT, ROOT('props')
  )
  select @instrProps as DiplayNode

I am expecting the node be

       <props>
            <instrtext>
                <![CDATA[TExt in this node]]>
            </instrtext>
            <hinttext>
                <![CDATA[Text in thi node]]>
            </hinttext>
            <score>5.000</score>
        </props>

but it is coming like 

<props>
  <props />
  <props>
    <instrtext>TExt in this node</instrtext>
    <hinttext>TExt in this node</hinttext>
    <score>5</score>
  </props>
</props>

Please help.

Community
  • 1
  • 1
Praveen
  • 263
  • 2
  • 10
  • 1
    I tried this and I notice that the CDATA exists in the `FOR XML EXPLICIT` output, but is removed when inserted into an xml `@instrProps` variable. – Ross Presser May 24 '15 at 10:56
  • CDATA is a human readable thing. Many machine XML processors will convert CDATA to encoded XML. Unfortunately SQL server is one such system. Fortunately the resulting XML is compatible with the CDATA value (as far as computers are concerned) unfortunately it can be more difficult for humans to read. – Matthew Whited May 24 '15 at 15:46

1 Answers1

0

It appears you cannot retain CDATA when the XML data type is involved. See https://stackoverflow.com/a/9133622/864696

Community
  • 1
  • 1
Ross Presser
  • 6,027
  • 1
  • 34
  • 66