How to use CDATA in SQL XML

Question

Could someone please help me with an XML output template please. I have been requested by a client to create an xml output file. This file will then feed in to Client's CRM. So that's why, it has to be an exact match of Client's requested template. I have managed to match it perfectly apart from CDATA for couple of fields.

below is the query you can use for testing purposes. I need CDATA wrap for fields Client and Area. I have also attached the output i am getting by running below code

            If OBJECT_ID('tempdb..#Temp') is Not NULL
            Drop table #Temp

            CREATE TABLE #Temp
            (
                [ShiftDate] [date] NULL,
                [Ref_Num] [varchar](20) NULL,
                [Agency_Worker_Name] [varchar](100) NULL,
                [Client] [varchar](100) NULL,
                [Area] [VarChar] (100) Null,
                [Assignment] [varchar](20) NULL,
                [Contract_Start] [varchar](30) NULL,
                [Contract_End] [varchar](30) NULL,
                [Contract_BreakInMinutes] [varchar](10) NULL,
                [Contract_Total] [varchar](30) NULL,
                [Actual_Start] [varchar](30) NULL,
                [Actual_End] [varchar](30) NULL,
                [Actual_BreakInMinutes] [varchar](10) NULL,
                [Actual_Total] [varchar](30) NULL,
                [Commission] [decimal](18, 2) NULL,
                [Total_Cost] [decimal](18, 2) NULL,
                [Rate] [varchar](20) NULL,
                [OverallCost] [decimal](18, 2) NULL,
                [AgencybackingReport] [int] NULL,
                [AccountCode] [varchar](20) NULL
            )

            Insert Into #Temp
            Values 
            ('2018-07-24',
             '83076641',
             'ABCD',
             'ABCD',
             'ABCD',
             'CPA00',
             '09:00',
             '17:00',
             '30',
             '07:30',
             '10:30',
             '17:00',
             '30',
             '05:30',
             '28.49',
             '159.01',
             'Basic',
             '221.59',
             '1220883',
            ' ABCD')



            Declare @xml Int=(Select max(AgencyBackingReport)  From #Temp)

            select @xml As [@AgencyBackingReport],(Select 
            [Ref_Num] As [@reference],
            [ShiftDate] as [@startdate],
            Case When [AccountCode] is NULL Then 'Unknown' Else [AccountCode] End as [@accountcode],
            Contract_Start As 'PlannedShift/Start',
            Contract_End As 'PlannedShift/End',
            Contract_BreakInMinutes As 'PlannedShift/BreakinMinutes',
            Actual_Start As 'ActualShift/Start',
            Actual_End As 'ActualShift/End',
            Actual_BreakInMinutes As 'ActualShift/BreakinMinutes',
            OverallCost As [OverallCost],
            Agency_Worker_Name As 'AdditionalInformation/WorkerName',
            Client  As 'AdditionalInformation/Client',
            Area As 'AdditionalInformation/Area',
            -- ( select 
            --         1 as Tag ,
            --        0 as Parent ,
            --            (Select 
            --         Area 
            --        From #Temp M2
            --   Where M1.Ref_Num = m2.Ref_Num)
            --   As [Area!1!!CDATA]
            --   for xml explicit 
            --)   As 'AdditionalInformation/Area',
            Assignment As 'AdditionalInformation/Assignment',
            Commission As 'AdditionalInformation/Commission',
            Total_Cost As 'AdditionalInformation/TotalCost',
            Rate As 'AdditionalInformation/Rate'
            From #Temp M1
            for xml path('Shift'),Type)
            for XML Path('Shifts'),Type

Answer 1

You seem to know that CDATA is rather outdated... If you want to read something about this you might follow this link and the links in this answer.

Sometimes we have to stick with it... Especially if poorly done third party tools demand for it. However...

The only way to include CDATA sections is to use FOR XML EXPLICIT, but this is rather clumsy.

One problem you must be aware of: You cannot store this in XML type!

Whenever you convert an XML including a CDATA section from string type to native XML your CDATA will get lost and will be a properly escaped normal text() node.

Try this

DECLARE @tbl TABLE(XmlAsString NVARCHAR(MAX), NativeXml XML);
INSERT INTO @tbl 
SELECT (
        SELECT 1      AS Tag
              ,NULL   AS Parent
              ,'test <&>' AS [SomeNode!1!!cdata]
        FOR XML EXPLICIT
        )
       ,(
        SELECT 1      AS Tag
              ,NULL   AS Parent
              ,'test <&>' AS [SomeNode!1!!cdata]
        FOR XML EXPLICIT
        );

SELECT * FROM @tbl

The result

<SomeNode><![CDATA[test <&>]]></SomeNode>   
<SomeNode>test &lt;&amp;&gt;</SomeNode>

In short: Keeping CDATA sections forces you to remain in string type. Might be a big draw back...

Back to your issue

If I see this correctly, you want to get everything like above, but

<Area><![CDATA[ABCD]]></Area>

... instead of

<Area>ABCD</Area>

Approach 1 (ugly):

Create the XML as you do it above, then read the content of <Area> and use a REPLACE on string level to change this node entirly. But you must not convert this ever back to XML...

Approach 2 (clumsy):

This is your XML with the CDATA section, not absolutely complete, but you see the principles:

SELECT   1          AS Tag
        ,NULL       AS Parent
        ,1220883    AS [Shifts!1!AgencyBackingReport]
        ,NULL       AS [Shift!2!reference]
        ,NULL       AS [Shift!2!startdate]
        ,NULL       AS [Shift!2!accountcode]
        ,NULL       AS [PlannedShift!3!Start!Element]
        ,NULL       AS [PlannedShift!3!End!Element]
        ,NULL       AS [PlannedShift!3!BreakingMinutes!Element]
        ,NULL       AS [ActualShift!4!dummy!Element] --just a dummy
        ,NULL       AS [OverallCost!5]
        ,NULL       AS [AdditionalInformation!6!WorkerName!Element]
        ,NULL       AS [AdditionalInformation!6!Area!CDATA]
        ,NULL       AS [AdditionalInformation!6!Assignment!Element]
UNION ALL
SELECT 2
      ,1
      ,NULL
      ,83076641
      ,'2018-07-24'
      ,'ABCD'
      ,NULL
      ,NULL
      ,NULL
      ,NULL
      ,NULL
      ,NULL
      ,NULL
      ,NULL
UNION ALL
SELECT 3
      ,2
      ,NULL
      ,NULL
      ,NULL
      ,NULL
      ,'09:00'
      ,'17:00'
      ,30
      ,NULL
      ,NULL
      ,NULL
      ,NULL
      ,NULL
UNION ALL
SELECT 4 --just a dummy
      ,2
      ,NULL
      ,NULL
      ,NULL
      ,NULL
      ,NULL
      ,NULL
      ,NULL
      ,'dummy'
      ,NULL
      ,NULL
      ,NULL
      ,NULL
UNION ALL
SELECT 5 
      ,2
      ,NULL
      ,NULL
      ,NULL
      ,NULL
      ,NULL
      ,NULL
      ,NULL
      ,NULL
      ,211.59
      ,NULL
      ,NULL
      ,NULL
UNION ALL
SELECT 6
      ,2
      ,NULL
      ,NULL
      ,NULL
      ,NULL
      ,NULL
      ,NULL
      ,NULL
      ,NULL
      ,NULL
      ,'ABCD'
      ,'ABCD'
      ,'CPA00'
FOR XML EXPLICIT

The result

<Shifts AgencyBackingReport="1220883">
  <Shift reference="83076641" startdate="2018-07-24" accountcode="ABCD">
    <PlannedShift>
      <Start>09:00</Start>
      <End>17:00</End>
      <BreakingMinutes>30</BreakingMinutes>
    </PlannedShift>
    <ActualShift>
      <dummy>dummy</dummy>
    </ActualShift>
    <OverallCost>211.59</OverallCost>
    <AdditionalInformation>
      <WorkerName>ABCD</WorkerName>
      <Area><![CDATA[ABCD]]></Area>
      <Assignment>CPA00</Assignment>
    </AdditionalInformation>
  </Shift>
</Shifts>