1

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?

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
| improve this answer | |
  • Perfect! Thank you :) – david Jan 10 at 10:20
  • Glad to help you! – Sabin Bio Jan 10 at 10:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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