sql-server xml cdata for-xml-path for-xml-explicit

Create XML in SQL (FOR XML EXPLICIT)

Need a little bit help to create some XML in SQL Server.

The resulting XML should look like this:

<?xml version="1.0" encoding="utf-8"?>
<Root Version="2.17" KFZ="XX-DD 389">
  <Head name="ExecuteAnswer">
    <Key name="AnswerStatus" value="-OK" />
    <Key name="MsgId"><![CDATA[KFHB0907896aAUH223]]></Key>
    <Section name="Command">
      <Key name="Name" value="SetNewCommand" />
      <Key name="Param0"><![CDATA[XX-DD 389]]></Key>
      <Key name="Param1"><![CDATA[]]></Key>
      <Key name="Param2"><![CDATA[0987asfdsafhdsSS]]></Key>
      <Key name="Param3"><![CDATA[0097aSSSHSDOPir0]]></Key>
    </Section>
  </Head>
  <Data></Data>
</Root>

I am stuck at adding <Section> to the XML, if I add it with [Section!3!name] I get this error:

error -> Key will be overwritten by Section

Code:

SELECT 
   1 AS Tag, 
   NULL AS Parent,
   '2.17' AS [Root!1!Version],
   'XX-DD 389' AS [Root!1!KFZ],
   NULL AS [Head!2!name!Element],
   NULL AS [Key!3!name],
   NULL AS [Key!3!value],
   NULL AS [Key!3!CDATA]

UNION ALL               

SELECT 
    2 as Tag,  
    1 as Parent,    
    NULL,
    NULL,
    'ExecuteAnswer',
    NULL,
    NULL,
    NULL

UNION ALL 

SELECT 
    3 as Tag,  
    2 as Parent,    
    NULL,           
    NULL,           
    NULL,           
    'AnswerStatus',
    '-OK',
    NULL

UNION ALL 

SELECT 
    3 as Tag,  
    2 as Parent,    
    NULL,           
    NULL,           
    NULL,           
    'MsgId',
    NULL,           
    'KFHB0907896aAUH223'
FOR XML EXPLICIT

Could anyone help me please?

Greetings Alex

Solution

As pointed out in the comment there's no reason for CDATA anymore... It is semantically absolutely identical with a properly escaped normal text() node.

However, sometimes legacy systems or third party tools demand for it.

Something you must know: There is no chance to add the xml declaration to the XML other than a cast to NVARCHAR(MAX) and manually append it.

So, if you have to go over string anyway, you might create the XML clean and easy with FOR XML PATH(), create the CDATA as <xdata>content</xdata> and do a simple REPLACE to change these placeholders to the CDATA opening and closing characters.

However: This is an approach with EXPLICIT

SELECT 
   1 AS Tag, 
   NULL AS Parent,
   '2.17' AS [Root!1!Version],
   'XX-DD 389' AS [Root!1!KFZ],
   NULL AS [Head!2!name!Element],
   NULL AS [Key!3!name],
   NULL AS [Key!3!value],
   NULL AS [Key!3!!CDATA],
   NULL AS [Section!4!name],
   NULL AS [Key!5!name],
   NULL AS [Key!5!value],
   NULL AS [Key!6!name],
   NULL AS [Key!6!!CDATA]

UNION ALL               

SELECT 
    2 as Tag,  
    1 as Parent,    
    NULL,
    NULL,
    'ExecuteAnswer',
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL

UNION ALL 

SELECT 
    3 as Tag,  
    2 as Parent,    
    NULL,           
    NULL,           
    NULL,           
    'AnswerStatus',
    '-OK',
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL

UNION ALL 

SELECT 
    3 as Tag,  
    2 as Parent,    
    NULL,           
    NULL,           
    NULL,           
    'MsgId',
    NULL,           
    'KFHB0907896aAUH223',
    NULL,
    NULL,
    NULL,
    NULL,
    NULL

    UNION ALL 

SELECT 
    4 as Tag,  
    2 as Parent,    
    NULL,           
    NULL,           
    NULL,           
    NULL,
    NULL,           
    NULL,
    'Command',
    NULL,
    NULL,
    NULL,
    NULL

    UNION ALL 

SELECT 
    5 as Tag,  
    4 as Parent,    
    NULL,           
    NULL,           
    NULL,           
    NULL,
    NULL,           
    NULL,
    NULL,
    'Name',
    'SetNewCommand',
    NULL,
    NULL

    UNION ALL 

SELECT 
    6 as Tag,  
    4 as Parent,    
    NULL,           
    NULL,           
    NULL,           
    NULL,
    NULL,           
    NULL,
    NULL,
    NULL,
    NULL,
    'Param0',
    'XX-DD 389'
FOR XML EXPLICIT