tsql XML explicit path

Question

I have query:

select 1 as Tag , 0 as Parent , '<P>' + 'Name' + ' ' + 'SurName' + '</P>' as "name!1!!CDATA" from tPA_SysParamSys for xml explicit, root('customers')

which output is:

<customers> <name><![CDATA[<P>Name SurName</P>]]></name> </customers>

But instead I wanna:

<customer> <customers> <name><![CDATA[<P>Name SurName</P>]]></name> </customers> </customer>

Without EXPLICIT and CDATA but with PATH I can do that, but can't do with CDATA.

Answer 1

I'm not quite sure, what you really want. I assume, that your root node should be <customers> (plural), while each row is one <customer>. I assume, that Name and SurName are columns living within your table.

Might be, it's this you are looking for:

DECLARE @mockupTable TABLE([Name] VARCHAR(100),SurName VARCHAR(100));
INSERT INTO @mockupTable VALUES('Smith','Tim'),('Fonda','Jane');

select 1 as Tag , 
0 as Parent ,
'<P>' + Name + ' ' + SurName + '</P>' as "customer!1!name!CDATA" 
from @mockupTable 
for xml explicit, root('customers');

The result

<customers>
  <customer>
    <name><![CDATA[<P>Smith Tim</P>]]></name>
  </customer>
  <customer>
    <name><![CDATA[<P>Fonda Jane</P>]]></name>
  </customer>
</customers>

But please allow me the question: Why?

There is absolutely no need for CDATA sections. A properly escaped normal text() node is semantically identical. SQL-Server developers decided not even to support this anymore...

If you store an XML including CDATA sections, they are translated implicitly. Try it out:

DECLARE @xml XML=
N'<customers>
  <customer>
    <name><![CDATA[<P>Smith Tim</P>]]></name>
  </customer>
  <customer>
    <name><![CDATA[<P>Fonda Jane</P>]]></name>
  </customer>
</customers>';
SELECT @xml;

You get this:

<customers>
  <customer>
    <name>&lt;P&gt;Smith Tim&lt;/P&gt;</name>
  </customer>
  <customer>
    <name>&lt;P&gt;Fonda Jane&lt;/P&gt;</name>
  </customer>
</customers>