Thread: Select from XML datatype column

I have a hard nut to crack here. I have the following XML stored in an XML field in a table -- using SQL Server 2005:

Code:

<root>
  <relationship table="Bob" source="Z">
     <key column="Col1" />
     <key column="Col2" />
 </relationship>
 <relationship table="Ted" source="Y">
    <key column="Col3"/>
 </relationship>
</root>

Now, I'm needing a SELECT query that will return this in the following format:

Code:

Bob   Z  Col1
Bob   Z  Col2
Ted   Y  Col3

All attempts have been futile. Any T-SQL, XQuery-ers out there?

And yes, there are some good reasons why I'm storing data this way

Well, this article seems to explain the basics...but not knowing what you've tried, I'm not sure how much help it would be: 15 Seconds : SQL Server 2005 XQuery and XML-DML - Part 1

I love this site!!!! Every time I post a problem I've been noodling on all day -- I figure it out 30 minutes later. W00t!

Anyway -- the query is:

Code:

Select 
       T.c.value('@table', 'nvarchar(128)')  
    ,  T.c.value('@source', 'nvarchar(128)')    
    ,  S.c.value('@column', 'nvarchar(128)')  
From myTable
Cross Apply xmlField.nodes('/root/relationship') as T(c)
Cross Apply T.c.nodes('./key') as S(c)