How to tell T-SQL query not to escape the given string/query result

This is a sample query

select *
from tablename
where tableid in (<sub query>);

The <sub query> here returns null or a string of pattern 'id1','id2','id3'

  • Kerberos sql server datasource in Wildfly 8.2
  • SQL Server trigger doesn't fire when a record is inserted from ASP.NET web page
  • echo image according to a condition
  • Query in PHP fails to see temp table in SQL Server if parameter binding is used
  • SQL Server : UPDATE not updating database Using C#
  • How tempDB works?
  • My <sub query> is something like:

    select xml_data.value('(/Node/SubNode)[1]', 'varchar(max)'))
    from tablename
    where tableid = '9944169f-95a6-4570-89d7-b57a3fe1b693'

    The problem :
    My sub query returns proper data ('id1','id2','id3') but the parent query considers the complete result as one single string and hence returns 0 rows always.

    How can I tell SQL Server not to escape single quotes present in the result of my sub-query?

    One Solution collect form web for “How to tell T-SQL query not to escape the given string/query result”

    It’s not clear if your subquery is applying to the same table as the first query, but this should show you the general direction

    declare @x xml = '<Node><SubNode>t1</SubNode><SubNode>t2</SubNode></Node>'
    declare @t  table (v varchar(20))
    insert @t values ('t1'),('t3')
    select table1.*
        @t table1
            inner join 
        @x.nodes('/Node/SubNode') t(x)
            on table1.v = t.x.value('.','varchar(100)')
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.