SQL Server 2008 query to select data from XML with parameters

Question

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!

Sujeet Sinha · Accepted Answer · 2016-06-28 14:56:26Z

2

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)

edited Jun 28, 2016 at 14:56

answered Jun 28, 2016 at 14:44

Sujeet Sinha

2,4332 gold badges20 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Viktor Krykun Over a year ago

This works! Thanks! And how can I get just London , not the <Val>London</Val> ?

Gottfried Lesigang Over a year ago

@ViktorUnderoath as pointed out in several answers you should use .value() instead of .query(). The second will return a (part of the) XML tree, the first returns just the value. If you are interested in one particular value only, there's no need for .nodes() at all...

Sujeet Sinha Over a year ago

@ViktorUnderoath check the edit.. if it works, plz upvote and mark as answer

Gottfried Lesigang Over a year ago

@SujeetSinha, your answer works, but please allow me some hints. As pairs is the root element, there is no need for .nodes() Please have a look at my one-liner. If there might be more than one p with the Name of "England", you must use .nodes(/pairs/p). Because your [1] would return only the first in any case. And if you know, that there's only one, there's no need for .nodes()...

Gottfried Lesigang Over a year ago

@SujeetSinha same here :-) When trying to be the first one is sometimes to fast. My first query did also just correct the OP's query, same flaws there... Thx and happy coding!

|

Gottfried Lesigang · Accepted Answer · 2016-06-28 14:46:27Z

2

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]

edited Jun 28, 2016 at 14:46

answered Jun 28, 2016 at 14:39

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Comments

Andrey Korneyev · Accepted Answer · 2016-06-28 14:40:59Z

1

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'

answered Jun 28, 2016 at 14:40

Andrey Korneyev

26.9k16 gold badges73 silver badges72 bronze badges

1 Comment

Gottfried Lesigang Over a year ago

If you read all nodes and do the filter in a WHERE clause, this will lead to huge extra work. With a big XML (or with many entries in one go) the performance would be very bad probably. The complicated XPath queries allow to return only the enlements needed...

JosephStyons · Accepted Answer · 2016-06-28 14:47:51Z

0

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

answered Jun 28, 2016 at 14:47

JosephStyons

59.1k64 gold badges169 silver badges240 bronze badges

7 Comments

Gottfried Lesigang Over a year ago

I would not apply the filter in a WHERE clause... If you read all nodes and do the filter at the end, it will lead to huge extra work. With a big XML (or with many entries in one go) the performance would be very bad probably.

JosephStyons Over a year ago

@Shnugo I would suggest that when working with large XML datasets, the best approach is to drop the flattened dataset into a temp table, work on it there, then transform back into XML at the end. For sets with < 10k rows this approach has been virtually instant for me.

Gottfried Lesigang Over a year ago

I agree, if the XML is easy to be flattened out and if you are going to do several steps at once. But I disagree if you just want to read a value once. Just imagine a table with thousands of XMLs and I want to pick those where the Name is "England". You'd have to flatten all of them just to filter some tiny information...

JosephStyons Over a year ago

@Shnugo agreed, if you have a large dataset and you just need to pick off one value, then this isn't the most efficient way. But there are only about 200 countries in the world, so for this example I think it's going to work fine.

Gottfried Lesigang Over a year ago

Just imagine a tabel with millions of rows. One XML column includes Country element and you want to filter for those from "England". Still working fine :-) ?

|

Collectives™ on Stack Overflow

SQL Server 2008 query to select data from XML with parameters

4 Answers 4

6 Comments

Comments

1 Comment

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

Comments

1 Comment

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related