Create XML in SQL (FOR XML EXPLICIT)

Question

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

The two reasons to use the outdated `OPTION EXPLICIT` are `CDATA` sections and some fance namespace issues. Are you aware, that `CDATA` is outdated itself? This is not even supported by SQL-Server anymore and will be omitted implicitly on storing this in native XML... Do you really need these `CDATA` sections? — Shnugo, Feb 02 '18 at 10:14
Yes the XML that Iam posted is predetermined. So I have to use CDATA Attribute. — Alexander Brühl, Feb 02 '18 at 10:40

Shnugo · Accepted Answer · 2018-02-02T16:30:58.420

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

Create XML in SQL (FOR XML EXPLICIT)

1 Answers1