FOR XML:条件空节点

Question

有一个SQL查询：

WITH q1 AS (
SELECT 1 AS c1, 2 AS c2, 'three' AS c3, 'orange' AS row_type
UNION ALL SELECT 11, 22, 'thirty three', 'banana'
UNION ALL SELECT 111, 222, 'three hundred thirty three', 'apple'
) SELECT q1.c1
, q1.c2
, q1.c3
, q1.row_type FROM q1 FOR XML PATH('row'),ROOT('doc'),type

它返回：

<doc>
<row>
<c1>1</c1>
<c2>2</c2>
<c3>three</c3>
<row_type>orange</row_type>
</row>
<row>
<c1>11</c1>
<c2>22</c2>
<c3>thirty three</c3>
<row_type>banana</row_type>
</row>
<row>
<c1>111</c1>
<c2>222</c2>
<c3>three hundred thirty three</c3>
<row_type>apple</row_type>
</row>
</doc>

我需要它返回：

<doc>
<row>
<c1>1</c1>
<c2>2</c2>
<c3>three</c3>
<row_type>
<orange />
</row_type>
</row>
<row>
<c1>11</c1>
<c2>22</c2>
<c3>thirty three</c3>
<row_type>
<banana />
</row_type>
</row>
<row>
<c1>111</c1>
<c2>222</c2>
<c3>three hundred thirty three</c3>
<row_type>
<apple />
</row_type>
</row>
</doc>

也就是说，我想为每个<row>创建一个有条件的空节点，节点的名称从当前记录的另一个属性中删除。

有什么提示吗？

SQL Server 2012。

Answer 1

一般的答案是：不要

让元素的名称成为数据总是一个非常糟糕的主意。好多了

<row_type type="apple" />

但你可能会以某种方式欺骗它：

--当您提前知道所有需要的值时，这个查询就可以工作了
--神奇的是，XML默认情况下会省略NULL值

WITH q1 AS (
SELECT 1 AS c1, 2 AS c2, 'three' AS c3, 'orange' AS row_type
UNION ALL SELECT 11, 22, 'thirty three', 'banana'
UNION ALL SELECT 111, 222, 'three hundred thirty three', 'apple'
) 
SELECT q1.c1
, q1.c2
, q1.c3
, CASE WHEN q1.row_type='orange' THEN '' END AS [row_type/orange] 
, CASE WHEN q1.row_type='banana' THEN '' END AS [row_type/banana] 
, CASE WHEN q1.row_type='apple' THEN '' END AS [row_type/apple] 
FROM q1 
FOR XML PATH('row'),ROOT('doc'),type;

--此查询使用一些字符串串联和CAST

WITH q1 AS (
SELECT 1 AS c1, 2 AS c2, 'three' AS c3, 'orange' AS row_type
UNION ALL SELECT 11, 22, 'thirty three', 'banana'
UNION ALL SELECT 111, 222, 'three hundred thirty three', 'apple'
) 
SELECT q1.c1
, q1.c2
, q1.c3
--careful, if your "row_type" might not be a valid element name
, CAST(CONCAT('<',q1.row_type,'/>') AS XML) AS [row_type] 
FROM q1 
FOR XML PATH('row'),ROOT('doc'),type;