Hi,

Normally you can simply use the nodes() method to shred the xml and this will return you a nice dataset. However, with your example XML, I see that the data seems to be at the same level and that there is no easy grouping. This has made it a bit trickier, but it is still possible.. Basically I have come up with the following:

DECLARE @t XML

SET @t = '<RESPONSE>

<EXPR>CHF</EXPR>

<EXCH>USD</EXCH>

<AMOUNT>1</AMOUNT>

<NPRICES>1</NPRICES>

<CONVERSION>

<DATE>Fri, 01 Jun 2012 22:50:00 GMT</DATE>

<ASK>1.7956</ASK>

<BID>1.7946</BID>

</CONVERSION>

<EXPR>EUR</EXPR>

<EXCH>USD</EXCH>

<AMOUNT>1</AMOUNT>

<NPRICES>1</NPRICES>

<CONVERSION>

<DATE>Sat, 02 Jun 2012 22:50:00 GMT</DATE>

<ASK>1.1794</ASK>

<BID>1.1787</BID>

</CONVERSION>

</RESPONSE>'

SELECT x.y.value('(for $x in (//EXPR)

return

if ($x << . and $x) then $x else ()

)[last()]','VARCHAR(3)') AS 'EXPR'

, x.y.value('(for $x in (//EXCH)

return

if ($x << . and $x) then $x else ()

)[last()]','VARCHAR(3)') AS 'EXCH'

, x.y.value('(DATE/text())[1]','VARCHAR(30)') AS 'DATE'

, x.y.value('(ASK/text())[1]','FLOAT') AS 'ASK'

, x.y.value('(BID/text())[1]','FLOAT') AS 'BID'

FROM @t.nodes('/RESPONSE') T(c)

CROSS APPLY T.c.nodes('CONVERSION') AS x(y)

What the code is doing is it is using the nodes() method to get the XML and then using the CROSS APPLY operator, it is essentially creating a record for each <CONVERSION> node.

For nodes under <CONVERSION> it is really easy to get those values as shown in the query. However, to get the other values, I had to use a flwor statement to make sure that i retrieved the nodes immediately above the <CONVERSION> section that is in scope. What the flwor statements are doing is that they are getting a sequence of //EXPR nodes (or //EXCH nodes) that are positioned before the <CONVERSION> node in the XML. That is achieved by using the << comparison. Then with that sequence of nodes, it returns the last() one of that sequence. The last one would be the one that is nearest to the <CONVERSION> node.

Hope this helps.