XQuery在选择AS [data()]时使用空格分隔符

Question

我使用了shnugo对从XML列中选择所有XML节点的回答的改编，这很有效。然而，生成的列表在条目之间插入了一个空格。

是否有一种方法可以防止在输出中包含空格分隔符，或者指定分隔符为逗号?

我在旅行中看到过它的参考资料，但再也找不到链接了。

以下(根据初始反馈更新):

declare @table TABLE(ID int, IsColA bit, IsColB bit, IsColC bit, IsColD bit, IsColE bit)
insert @table select 1, 1, 1, 1, 1, 0
SELECT * FROM (
SELECT TagName +',' AS [data()] FROM (
SELECT ID AS ID
, Nodes.value('local-name(.)','varchar(32)') AS TagName
, Nodes.value('text()[1]','bit') AS TagValue
FROM (
SELECT ID, CONVERT(XML, (SELECT pbInner.* FOR XML PATH('row'))) as Rows
FROM @table pbInner
WHERE pbInner.ID = 1
) t
CROSS APPLY t.Rows.nodes('/row/*') A(Nodes)
) cols
WHERE cols.TagName LIKE 'is%' and cols.TagName NOT IN ('IsToBeExcluded', 'IsAlsoToBeExcluded')
AND TagValue = 1
FOR XML PATH('')
) inds(indlist)

输出IsColA, IsColB, IsColC, IsColD,，理想情况下输出IsColA,IsColB,IsColC,IsColD,

我意识到我可以做Replace(indlist, ' ', '')，但想知道是否有办法在XQuery内做到这一点。

我很高兴听到有更好的方法来做上面的事情，但请注意数据库是在兼容模式100 (Sql Server 2008)，我认为这排除了使用FOR JSON。

Answer 1

请尝试以下操作

/p>

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, IsColA bit, IsColB bit, IsColC bit, IsColD bit, IsColE bit);
INSERT @tbl (IsColA, IsColB, IsColC, IsColD, IsColE) VALUES 
(1, 1, 1, 1, 0);
-- DDL and sample data population, end
SELECT p.*
, REPLACE(STUFF(c.query('for $r in /root/*[not(local-name()=("ID","IsToBeExcluded","IsAlsoToBeExcluded"))]
[text()="1"]
return concat(",", local-name($r)[1])').value('text()[1]', 'VARCHAR(MAX)')
, 1, 1, NULL)
, SPACE(1), '') AS Result
FROM @tbl AS p
CROSS APPLY (SELECT * FROM @tbl AS c WHERE p.ID = c.ID
FOR XML PATH(''), TYPE, ROOT('root')) AS t(c);

+----+--------+--------+--------+--------+--------+-----------------------------+
| ID | IsColA | IsColB | IsColC | IsColD | IsColE |           Result            |
+----+--------+--------+--------+--------+--------+-----------------------------+
|  1 |      1 |      1 |      1 |      1 |      0 | IsColA,IsColB,IsColC,IsColD |
+----+--------+--------+--------+--------+--------+-----------------------------+