Viktor Underoath Viktor Underoath - 5 months ago 17
SQL Question

SQL Server 2008 query to select data from XML with parameters

I have next XML:

SET @MyXML =
'
<pairs>
<p>
<Name>France</Name>
<Val>Paris</Val>
</p>
<p>
<Name>England</Name>
<Val>London</Val>
</p>
<p>
<Name>Spain</Name>
<Val>Madrid</Val>
</p>
</pairs>
'


I need to get data from this XML in this way - I've set parameter "England" and get "London". My code is next, but it's not working:

SELECT
Tab.Col.query('p/.[(Val)[1] cast as xs:string? = "England"]') AS [Capital]
FROM
@MyXML.nodes('//pairs') Tab(Col)


What I miss? Thanks!

Answer

Your XQuery object is incorrectly constructed. This will get the <p> which has the <name> as "England" and then the return the <Val> tag.

SELECT
  Tab.Col.value('((p[Name="England"]/Val/text())[1])', 'varchar(max)') AS [Capital]
FROM
  @MyXML.nodes('//pairs') Tab(Col)
Comments