SQL Server XML output with CDATA

Question

Is there a way to have an SQL Server XML return use CDATA? I have XML being returned by SQL Server like this:

<locations>
    <site id="124">
        <sitename>Texas A &amp; M</sitename>
    </site>
</locations>

When I am required to have this:

<locations>
    <site id="124">
        <sitename><![CDATA[Texas A & M]]></sitename>
    </site>
</locations>

Answer 1

Look at the options of FOR XML EXPLICIT (parameter Directive). It gives the greater degree of control and you can also specify CDATA. Here is a good tutorial.

And the code addapted from that tutorial:

declare @agent table
(    
    AgentID int,
    Fname varchar(5),
    SSN varchar(11)
)

insert into @agent
select 1, 'Vimal', '123-23-4521' union all
select 2, 'Jacob', '321-52-4562' union all
select 3, 'Tom', '252-52-4563'

SELECT
    1 AS Tag,
    NULL AS Parent,
    NULL AS 'Agents!1!',
    NULL AS 'Agent!2!AgentID',
    NULL AS 'Agent!2!Fname!Element',
    NULL AS 'Agent!2!SSN!cdata'
UNION ALL
SELECT
    2 AS Tag,
    1 AS Parent,
    NULL, 
    AgentID,
    Fname,
    SSN
FROM @agent
FOR XML EXPLICIT