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'

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?

Answers


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.*
from
    @t table1
        inner join 
    @x.nodes('/Node/SubNode') t(x)
        on table1.v = t.x.value('.','varchar(100)')

Need Your Help

Making two CGRects stay together while one of them moves

ios objective-c cocoa-touch geometry

I have a game where a ball bounces from platform to platform. I am looking to change it so that when the ball hits the platform, it does not automatically bounce, but instead it sticks to the platf...

Convert Html or RTF to Markdown or Wiki Compatible syntax?

c# .net api markdown

Is there a .net api that can do this? I saw Pandoc has a standalone exe that I could wrap but I'd rather not if there is something already out there. Any suggestions?

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.