T-sql – Querying XML Nodes with SQL

t-sqlxml

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?

Best Answer

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
Related Question