Choosing XML Nodes Using TSQL

Question

My SQL Server 2008 database table has an XML field. I would like to select nodes from the field along with other fields. For example, consider the following table:

DECLARE @TestTable AS TABLE ([Id] VARCHAR(20), [Name] XML ) INSERT INTO @TestTable SELECT '001', '<Name><First>Ross</First><Last>Geller</Last></Name>' UNION ALL SELECT '002', '<Name><First>Rachel</First><Last>Green</Last></Name>' 

I want the result to be:

 001 | Ross | Geller 002 | Rachel | Green 

Is it possible? Thanks,

Answer 1

This should do it:

 DECLARE @TestTable AS TABLE ( [Id] VARCHAR(20), [Name] XML ) INSERT INTO @TestTable SELECT '001', '<Name><First>Ross</First><Last>Geller</Last></Name>' UNION ALL SELECT '002', '<Name><First>Rachel</First><Last>Green</Last></Name>' SELECT Id, x.value('(/Name/First)[1]', 'varchar(20)') AS [First], x.value('(/Name/Last)[1]', 'varchar(20)') AS [Last] FROM @TestTable t CROSS APPLY [Name].nodes('/Name') AS tbl ( x )