CODEWITHSUNDEEP
sql-serverxmlt-sqlcdatafor-xml
kole1108
kole1108

Reputation: 117

tsql XML explicit path

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.

Upvotes: 1

Views: 183

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

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>

Upvotes: 1

hoggar
hoggar

Reputation: 3817

With this query ...

with c 
as 
( 
select 1 as CustomerId, 'Name1' as Name1, 'Name2' as Name2 
union select 2, 'Name1', 'Name2' 
) 
select 1 as Tag 
    , 0 as Parent 
    , CustomerId as "customer!1!customer_id!hide" 
    , null as "name!2!!CDATA" 
from c 
union 
select 2 as Tag 
    , 1 as Parent 
    , CustomerId 
    , Name1 + ' ' + Name2 as "name!2!!CDATA" 
from tCustomers c 
order by "customer!1!customer_id!hide", Tag 
for xml explicit, root('customers')

You get this XML ...

<customers>
  <customer>
    <name><![CDATA[Comp1Name1 Comp1Name2]]></name>
  </customer>
  <customer>
    <name><![CDATA[Comp2Name1 Comp2Name2]]></name>
  </customer>
</customers>

Inspired by this link ... https://www.experts-exchange.com/questions/26194239/CDATA-tags-appearing-incorrectly-in-XML-output-using-FOR-XML-PATH.html

Upvotes: 1

Related Questions