SQL - Query using XML Path

Question

I have the following query:

SELECT     ara.req_id
FROM        tbl_ara ara
WHERE       id in (
                        SELECT STUFF((
                        SELECT ',' + cast(id as varchar(8000))
                        FROM contact
                        WHERE ct_id = 2
                        FOR XML PATH('')
                        ), 1, 1, ''))

So, theres is table with name contact that I want to get all the ids when they are type of 2. Then I am creating a list with Stuff. Then I want to use another table and get all the records that their id is on that list. You can see the logic in the query. The problem is that SQL doesn't recognise it as list and I get the following error:

Msg 245, Level 16, State 1, Line 11 Conversion failed when converting the nvarchar value '6019,49111,49112' to data type int.

Onkel Toob · Accepted Answer · 2016-07-01 09:13:09Z

3

STUFF in your case returns character data. From the documentation (https://msdn.microsoft.com/en-us/library/ms188043.aspx):

Returns character data if character_expression is one of the supported character data types. Returns binary data if character_expression is one of the supported binary data types.

So you do not get a list of IDs but rather a string containing numbers and commas.

Is there a reason for not using the IDs directly?

SELECT 
    [ara].[req_id]
FROM [tbl_ara] [ara]
WHERE [id] IN
             (
              SELECT 
                  [id]
              FROM [contact]
              WHERE [ct_id] = 2
             )

EDIT: Of course the JOIN solution by @gofr1 is a pretty good option, too.

edited Jul 1, 2016 at 9:13

answered Jul 1, 2016 at 8:58

Onkel Toob

2,2222 gold badges18 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

BlackM Over a year ago

Yes there is a reason. Its been a while since the last time I used SQL :) thank you

gofr1 · Accepted Answer · 2016-07-01 08:58:49Z

The simplest way is JOIN:

SELECT ara.req_id
FROM tbl_ara ara
INNER JOIN contact c
    ON ara.id = c.id
WHERE c.ct_id = 2

If you want to run using STUFF then you should use dynamic SQL. At first prepare statement, then execute it:

DECLARE @sql nvarchar(max), @inpart nvarchar(max)

SELECT @inpart = STUFF((
                    SELECT ',' + cast(id as varchar(8000))
                    FROM contact
                    WHERE ct_id = 2
                    FOR XML PATH('')
                    ), 1, 1, '')


SELECT @sql = '
SELECT     ara.req_id
FROM        tbl_ara ara
WHERE       id in (' + @inpart+ ')'


PRINT @sql

--EXEC sp_executesql @sql

PRINT would get you this:

SELECT     ara.req_id
FROM        tbl_ara ara
WHERE       id in (6019,49111,49112)

Uncomment EXEC sp_executesql @sql to execute query.

Mihir Shah · Accepted Answer · 2016-07-01 09:03:42Z

1

Why don't you try this :

SELECT  ara.req_id
FROM    tbl_ara As ara
WHERE   Exists( Select  1
                From    contact As c
                Where   c.ct_id = 2
                        And c.id = ara.id
              )

As exists clause only check the existence of single match and return that row when match found.

answered Jul 1, 2016 at 9:03

Mihir Shah

94010 silver badges20 bronze badges

Collectives™ on Stack Overflow

SQL - Query using XML Path

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related