Try to use FOR XML Path with an except

Question

I'm trying to print a result in one line instead of multiple lines. I have a query looks like :

SELECT DISTINCT  RTRIM(LTRIM(MACHNAME))
  FROM MACHS
  WHERE MACHID <> 0

EXCEPT 

SELECT DISTINCT RTRIM(LTRIM(MACHNAME))
                 FROM GROUPS
                WHERE GROUPS.GROUPTYPE = 'M'

By using the Except query, I have :

lineNumber MachName

1 WinMach2

2 WinMach6

And I would like to have :

WinMach2, WinMach6

So, to print the result in one line, I tried :

select stuff((SELECT DISTINCT ', ' + RTRIM(LTRIM(MACHNAME))
  FROM MACHS
  WHERE MACHID <> 0

EXCEPT 

SELECT DISTINCT RTRIM(LTRIM(MACHNAME))
                 FROM GROUPS
                WHERE GROUPS.GROUPTYPE = 'M' 
                for xml path ('')
             ), 1, 2, '') as machineName
              FROM GROUPS

But, The error is :

The FOR XML clause is invalid in views, inline functions, derived tables, and subqueries when they contain a set operator. To work around, wrap the SELECT containing a set operator using derived table syntax and apply FOR XML on top of it.

It's working if I do :

select stuff((SELECT DISTINCT ', ' + RTRIM(LTRIM(MACHNAME)) FROM MACHS WHERE MACHID <> 0
for xml path ('')), 1, 2, '') as machineName
FROM MACHS

The result is:

WinMach1, WinMach2, WinMach3, WinMach4, WinMach5, WinMach6, WinMach7, WinMach8, WinMach9

I checked this : SQL UNION FOR XML name output column. But, I wasn't able to understand the fix.

And I tried : Using UNION ALL in STUFF / XML Path But, when I did the first part:

SELECT * FROM (SELECT DISTINCT RTRIM(LTRIM(MACHNAME))
  FROM MACHS
  WHERE MACHID <> 0

EXCEPT 

SELECT DISTINCT RTRIM(LTRIM(MACHNAME))
                 FROM GROUPS
                WHERE GROUPS.GROUPTYPE = 'M') AS K

The error is 'No column name was specified for column 1 of 'K'.

Could you help me?

Thanks

score 1 · Accepted Answer · answered Jul 30 '18 at 10:05

You have FROM clause. So, you need to specify data source - in your case it is AS K, but it should be DS ([k]) meaning alias of data source DS with column(s) - K:

SELECT * 
FROM 
(
    SELECT DISTINCT RTRIM(LTRIM(MACHNAME))
    FROM MACHS
    WHERE MACHID <> 0
    EXCEPT 
    SELECT DISTINCT RTRIM(LTRIM(MACHNAME))
    FROM GROUPS
    WHERE GROUPS.GROUPTYPE = 'M'
) DS (K);

and the final like this:

select stuff
(
    (
        SELECT DISTINCT ', ' + RTRIM(LTRIM(MACHNAME)) 
        FROM 
        (
            SELECT DISTINCT RTRIM(LTRIM(MACHNAME))
            FROM MACHS
            WHERE MACHID <> 0
            EXCEPT 
            SELECT DISTINCT RTRIM(LTRIM(MACHNAME))
            FROM GROUPS
            WHERE GROUPS.GROUPTYPE = 'M'
        ) DS (MACHNAME)
        for xml path ('')
    )
    ,1
    ,2
    , ''
) as machineName

score 1 · Answer 2 · answered Jul 30 '18 at 10:07

I would use NOT EXISTS instead :

select stuff( (select distinct ',' +rtrim(ltrim(m.MACHNAME))
               from MACHS m
               where MACHID <> 0 and
                     not exists (select 1 
                                 from GROUPS g 
                                 where g.MACHNAME = m.MACHNAME and 
                                       g.GROUPTYPE = 'M'
                                ) for xml path ('')
              ), 1, 1, ''
            ) as MachName

Try to use FOR XML Path with an except

2 Answers2