CODEWITHSUNDEEP
sqlsql-serverxmlxpathsqlxml
Axarydax
Axarydax

Reputation: 16603

SQL select join on xml field with xpath expression

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.

Upvotes: 1

Views: 970

Answers (1)

roman
roman

Reputation: 117380

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'

Upvotes: 2

Related Questions