Querying XML Nodes with SQL

Question

I think I'm nearly there with this but have reached an impasse!

I've a relatively straightforward XML structure that I need to parse out as a SQL resultset but can't seem to get it to work.

Here is the code:

DECLARE @xml XML;
SET @xml = '
<values>
 <Nationality>
 <item>
 <key>UK</key>
 <value>def</value>
 </item>
 <item>
 <key>IE</key>
 <value>def</value>
 </item>
 </Nationality>
 <Currency>
 <item>
 <key>GBP</key>
 <value>123</value>
 </item>
 <item>
 <key>EUR</key>
 <value>345</value>
 </item>
 </Currency>
</values>';
select I.X.value('(/key/text())[1]', 'int') as ItemID,
 I.X.value('(/value/text())[1]', 'int') as Store
from @XML.nodes('/values/Nationality') as O(X)
cross apply O.X.nodes('/item') as I(X);

Essentially, I want to pull out the values from Nationality into:

Key | Value
-------------
UK | Def
IE | Def

I may want to do Currency later but I think if I get the above cracked then the rest will be simple enough.

Can you help please?

Answer 1

Base on your input data, I will do something like this:

select I.X.value('(key/text())[1]', 'varchar(50)') as ItemID,
 I.X.value('(value/text())[1]', 'varchar(50)') as Store
from @XML.nodes('values/Nationality') as O(X)
 cross apply O.X.nodes('item') as I(X);

ouptput

ItemID Store
UK def
IE def

• david: Perfect! Thank you :)
• Sabin B: Glad to help you!