CODEWITHSUNDEEP
sqlsql-serverxmlt-sqlxpath
Viktor Krykun
Viktor Krykun

Reputation: 253

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!

Upvotes: 2

Views: 176

Answers (4)

Sujeet Sinha
Sujeet Sinha

Reputation: 2433

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)

Upvotes: 2

JosephStyons
JosephStyons

Reputation: 58685

You can isolate the <p> tag in the nodes path and simplify your SELECT. This gives a list of all Countries and Capitals:

SELECT
  T.c.value('(Name)[1]','nvarchar(100)') AS Country
 ,T.c.value('(Val)[1]','nvarchar(100)') AS Capital
FROM
  @MyXML.nodes('/pairs/p') T(c)

The syntax is no different in the WHERE clause, so you can just apply a parameter there:

declare @param nvarchar(100) = 'England'
SELECT
  T.c.value('(Val)[1]','nvarchar(100)') AS Capital
FROM
  @MyXML.nodes('/pairs/p') T(c)
WHERE T.c.value('(Name)[1]','nvarchar(100)') = @Param

Here is a complete test script that does both.

declare @MyXML 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>
'
SELECT
  T.c.value('(Name)[1]','nvarchar(100)') AS Country
 ,T.c.value('(Val)[1]','nvarchar(100)') AS Capital
FROM
  @MyXML.nodes('/pairs/p') T(c)

declare @param nvarchar(100) = 'England'
SELECT
  T.c.value('(Val)[1]','nvarchar(100)') AS Capital
FROM
  @MyXML.nodes('/pairs/p') T(c)
WHERE T.c.value('(Name)[1]','nvarchar(100)') = @Param

Upvotes: 0

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

You try it a bit to complicated:

DECLARE  @MyXML XML = 
N'<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>';

DECLARE @param NVARCHAR(100)=N'England';

SELECT
  Tab.Col.query('(p[Name=sql:variable("@param")]/Val)[1]') AS [Capital]
FROM
  @MyXML.nodes('/pairs') Tab(Col)

Even better

SELECT
  p.value('Val[1]','nvarchar(max)') AS [Capital]
FROM
  @MyXML.nodes('/pairs/p[Name=sql:variable("@param")]') One(p)

Or as a one liner

SELECT @MyXml.value('(/pairs/p[Name=sql:variable("@param")]/Val)[1]','nvarchar(max)') AS [Capital]

Upvotes: 2

Andrey Korneyev
Andrey Korneyev

Reputation: 26846

You can do it even without complicated XPath queries like this:

select Tab.Col.value('Val[1]', 'nvarchar(max)') AS [Capital]
from @MyXML.nodes('/pairs/p') as Tab(Col)
where Tab.Col.value('Name[1]', 'nvarchar(max)') = 'England'

Upvotes: 1

Related Questions