SQL select join on xml field with xpath expression

Question

I have a following query that can return a result from an xml:

declare @xml xml

select @xml = data from files where id = 1234

select
    children.p.value('./speed[1]','float')
from @xml.nodes('root/children') as children(p)
where
    children.p.value('./name[1]','nvarchar(max)') = 'something'

This in my case returns a single value, for example 3141

However, I'd like to do multiple selects like this from multiple XMLs.

I can select the xml data as

select id, cast(data as xml) as xml
from files
where id in (1005,51,968,991,992,993,969,970) --for example

I imagine there must be some kind of JOIN that will apply my expression and return a single item for each xml variable in the table, but I am not sure how.

Answer 1

Use apply:

select
    f.id, children.p.value('./speed[1]','float')
from files as f
    outer apply (select cast(f.data as xml) as xml) as x
    outer apply x.xml.nodes('root/children') as children(p)
where
     f.id in (1005,51,968,991,992,993,969,970) and
     children.p.value('./name[1]','nvarchar(max)') = 'something'