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:

SET @xml = '

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?

1 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);


ItemID  Store
UK  def
IE  def
  • Perfect! Thank you :)
    – david
    Jan 10, 2020 at 10:20
  • Glad to help you!
    – Sabin B
    Jan 10, 2020 at 10:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.