有没有办法让sql Server XML返回使用CDATA？我有sql Server返回的XML,如下所示：
<locations>
<site id="124">
<sitename>Texas A & M</sitename>
</site>
</locations>
当我需要这个：
<locations>
<site id="124">
<sitename><![CDATA[Texas A & M]]></sitename>
</site>
</locations>
解决方法
查看
FOR XML EXPLICIT(参数指令)的选项.它提供了更大程度的控制,您还可以指定CDATA.这是
a good 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
