使用CDATA的SQL Server XML输出 [英] SQL Server XML output with CDATA

问题描述

有没有办法让SQL Server XML返回使用CDATA？我有SQL Server这样返回的XML：

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>

推荐答案

查看 FOR XML EXPLICIT （参数指令）。它提供了更大程度的控制，您也可以指定CDATA。这是一个不错的教程。

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

这篇关于使用CDATA的SQL Server XML输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！