XQuery uses a space separator when selecting AS [data()]

Question

I've used an adaptation of shnugo's answer to Select all XML nodes from XML column , and this works a treat. However, the generated list has a space inserted between items.

Is there a way to prevent the space separator from being included in the output, or to specify the separator to be a comma?

I've seen a reference to it somewhere in my travels but can't locate the link any more.

The following (updated following initial feedback):

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)

Gives output IsColA, IsColB, IsColC, IsColD, when ideally it would output IsColA,IsColB,IsColC,IsColD,

I realise that I can do Replace(indlist, ' ', '') but would like to know if there's a way to do it within the XQuery.

I'm happy to hear of a better way of doing the above, but note that the database is at compatibility mode 100 (Sql Server 2008) which I believe precludes the use of FOR JSON.

Can you provide a full [mre] here please? Shungo's answer doesn't provide a delimited list, it provides a dataset. — Thom A, Jun 24 '22 at 11:09
Please show your current table and sample data *as text* preferably `CREATE TABLE` and `INSERT` statements — Charlieface, Jun 24 '22 at 11:17
Are you, perhaps, trying to achieve the functionality of `STRING_AGG` in SQL Server 2008 here? — Thom A, Jun 24 '22 at 11:22
Thank you for the feedback, I've updated the code snippet. For more context, the aim is to handle a table with large numbers of bit columns where at any one time only a small subset will be set to 1. I'm looking to identify which of those columns are set to 1. — lukep, Jun 24 '22 at 11:37
@lptr that worked perfectly thank you, I wasn't expecting it to be so simple! If you make it an answer I can upvote. I'll have a look for some documentation on ```text()```, unless you have a link handy? — lukep, Jun 24 '22 at 11:41
There is a huge amount of unnecessary code, see this fiddle for different syntaxes https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=cdd9ae3d67f2c992a0acc11b76589e39. Also in newer versions you can just use `STRING_AGG` — Charlieface, Jun 24 '22 at 12:49
Also why do you need to select all the rows from the table? You just want the column names so you could do `SELECT TOP 1` — Charlieface, Jun 24 '22 at 13:01
Thanks @Charlieface. The table I'm starting with doesn't contain xml, so the starting point is a bit different from the dbfiddles you've linked to. The table has a large number of bit columns, so although I'm sure my solution could be cleaner, part of it it is to get an XML representation of the table. In addition I need to select only columns which begin with a specific prefix and have a value of 1, which your dbfiddles don't handle. The code I've posted is extracted from a larger query within which this XM jiggery pokery is OUTER APPLY'd — lukep, Jun 24 '22 at 16:35
I don't understand: you seem to only want the column names not the data? You want something like this maybe https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=46f8f5899473d7279157e6867f040009 — Charlieface, Jun 24 '22 at 17:42
Yes you have it pretty much - I want to get column names where the value in that column is 1 *and* the column name starts with "Is" (but can also exclude specific column names that also start with "Is"). Your second dbfiddle is much closer to what I'm after so I'll use your and Yitzhak's suggestions to improve my clumsy original solution. Thank you :-) — lukep, Jun 24 '22 at 19:11
Sounds like you need something like this with XQuery predicates https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=a3d3e78d445a511847ad84b950e2b091 — Charlieface, Jun 26 '22 at 15:46

score 0 · Answer 1 · answered Jun 24 '22 at 14:21

Please try the following.

SQL

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

Output

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

Thanks Yitzhak, I'm pondering which of the two solutions is most readable/comprehensible to other devs, as @lptr's tweak solved my problem. — lukep, Jun 24 '22 at 16:40
Just a small thing Yitzhak, although your SQL executes as expected, I get red warning squiggles under ```c.query``` ("Cannot find either column c ... or the name is ambiguous") and ```t(c)``` ("t has more columns than specified in the column list)") - something to do with compatibility mode perhaps? — lukep, Jun 24 '22 at 16:58
You can merely ignore it. In some cases SSMS is not smart enough to recognize legit T-SQL. — Yitzhak Khabinsky, Jun 24 '22 at 17:44

XQuery uses a space separator when selecting AS [data()]

1 Answers1