CODEWITHSUNDEEP
sqlsql-server-2008-r2xquery
Rob L
Rob L

Reputation: 2372

SQL SELECT ... IN(xQuery)

My first xQuery, may be a bit basic, but it's the only time I need to use it at the moment) 

DECLARE @IdList XML;

SET @IdList =
'<Ids>
    <Id>6faf5db8-b434-437f-99c8-70299f82dab4</Id>
    <Id>5b3ddaf1-3412-471a-a6cf-71f8e1c31168</Id>
    <Id>1da6136d-2ff5-44cc-8510-4713451aac4d</Id>
</Ids>';

What I want to do is:

SELECT * FROM [MyTable] WHERE [Id] IN ( /* This list of Id's in the XML */ );

What is the desired way to do this?

Note: The format of the XML (passed into a Stored Procedure from C#) is also under my control, so if there is a better structure (for performance), then please include details.

Also, there could be 1000's of Id's in the real list if that makes a difference..

Thanks Rob

Upvotes: 2

Views: 78

Answers (1)

SqlACID
SqlACID

Reputation: 4014

If you're passing 1000's of Id's, I don't think this will be a stellar performer, but give it a try: 

    DECLARE @IdList XML;

    SET @IdList =
    '<Ids>
        <Id>6faf5db8-b434-437f-99c8-70299f82dab4</Id>
        <Id>5b3ddaf1-3412-471a-a6cf-71f8e1c31168</Id>
        <Id>1da6136d-2ff5-44cc-8510-4713451aac4d</Id>       
    </Ids>';

    select * from mytable where id in
    (
        select cast(T.c.query('text()') as varchar(36)) as result  from @idlist.nodes('/Ids/Id') as T(c)
)

You might look at table valued parameters as a better way, unless you already have your data in XML.

Upvotes: 2

Related Questions