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? — Gottfried Lesigang
– Gottfried Lesigang, Commented Feb 2, 2018 at 10:14
Yes the XML that Iam posted is predetermined. So I have to use CDATA Attribute. — Alexander Brühl
– Alexander Brühl, Commented Feb 2, 2018 at 10:40

Gottfried Lesigang · Accepted Answer · 2018-02-02 16:30:58Z

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

Collectives™ on Stack Overflow

Create XML in SQL (FOR XML EXPLICIT)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related