代码之家 › 专栏 › 技术社区 › elle0087

SQL Server FOR XML路径删除标记(如果是双标记)

xml sql-server sql

elle0087 · 技术社区 · 8 年前

我尝试了以下查询:

select 
    *,
    (select ''as br for xml path(''),elements,type)r1,
    (select ''as br,''as br for xml path(''),elements,type)r2,
    (select ''as br1,''as br2 for xml path(''),elements,type)r3
from
    (select 'v1' c1) t

结果是:

c1  r1          r2      r3
v1  <br></br>   <br />  <br1></br1><br2></br2>

为什么 r2 只有一个结束标记?

我想应该是  

我解决了在别名中使用双br的问题,如 'br/br'

然后:

select 
    *,
    (select ''as br for xml path(''),elements,type)r1,
    (select ''as br,''as br for xml path(''),elements,type)r2,
    (select ''as 'br/br' for xml path(''),elements,type)r2_bis,
    (select ''as br1,''as br2 for xml path(''),elements,type)r3
from
    (select 'v1' c1) t

和结果:

c1  r1          r2      r2_bis              r3
v1  <br></br>   <br />  <br><br></br></br>  <br1></br1><br2></br2>

1 回复 | 直到 8 年前

Thom A 8 年前

SQL Server压缩了这两个节点,因为它们具有相同的名称。你需要把它们分开。一种方法是使用空字符串,例如:

SELECT *,
       (SELECT '' AS br FOR XML PATH(''), ELEMENTS, TYPE) AS r1,
       (SELECT '' AS br, '', '' AS br FOR XML PATH(''), ELEMENTS, TYPE) AS r2,
       (SELECT '' AS br1, '' AS br2 FOR XML PATH(''), ELEMENTS, TYPE) AS r3
FROM (SELECT 'v1' AS c1) t;

它返回  