sql-server - tsql XML 显式路径

Question

我有疑问：

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

输出是：

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

但相反，我想：

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

没有 EXPLICIT 和 CDATA 但使用 PATH 我可以做到这一点，但不能使用 CDATA。

标签： sql-serverxmltsqlcdatafor-xml

Answer 1

有了这个查询...

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') 

你得到这个 XML ...

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

受此链接的启发... https://www.experts-exchange.com/questions/26194239/CDATA-tags-appearing-incorrectly-in-XML-output-using-FOR-XML-PATH.html