Hitting a brick wall with XQuery

[nscheaffer]

I need to get at the ReasonCode which is in and XML column in SQL Server 2008 R2 database.

'<InitialResponse transactionKey="33">

  <AssesmentComplete>

    <ReasonCode>02</ReasonCode>

    <ReasonCode>05</ReasonCode>

    <ReasonCode>36</ReasonCode>

    <ReasonCode>4F</ReasonCode>

    <Score>0</Score>

    <MatchAssesment>0</MatchAssesment>

    <FraudIndicator>W</FraudIndicator>

    <AtomicScores />

  </AssesmentComplete>

</InitialResponse>'

The closest I seem to be able to get is a string with all of the reason codes concatenated together.

If the xml string given above is in a column called myXMLData the following statement...

myXMLData.query('distinct-values( data(/InitialResponse/AssesmentComplete/ReasonCode))')

...gives me this...

02 05 36 4F

Rather than 1 row, I want four rows in this case like this...

02

05

36

4F

I have worked up and example for your review on SQL Fiddle.  Here is a screen shot it you aren't too sure about clicking that link.

Thanks,

Noel

[nscheaffer]

By using the nodes() method as described in this example in conjunction with CROSS APPLY discussed here I came up with this...

SELECT ID, t.ArrayOfString.value('.','varchar(max)')
FROM XMLSample
CROSS APPLY myXMLData.nodes('/InitialResponse/AssesmentComplete/ReasonCode') as t(ArrayOfString)

I haven't applied this logic to my universe yet, but it works in SQL Fiddle.  I think I may also try using a common table expression as shown in this post.

I'll report back my findings.

Noel

[nscheaffer]

It worked!

On a side note, I really like SQL Fiddle for being able to try things out and share them.

Noel