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 )

---------- TEST 2 -----------

DECLARE @x XML

SET @x = '
<Root>
<row code="1979" CodeName="Luna" ><name>Remus</name><oflw>Patronum</oflw></row>
<row code="2004"><name>Viktor</name></row>
<row code="2003" />
</Root>
<Root>
<row code="700000" CodeName="Albus" ><name>harry</name><oflw>riddikulus</oflw></row>
<row code="500000"><name>Ron</name></row>
<row code="4195" CodeName="Petunia" ><name>Snape</name><oflw>Patronum</oflw><item><book>Fire</book><book>Stone</book></item></row>
</Root>'

SELECT T.c.query('.') AS result
FROM @x.nodes('/Root') T(c)

SELECT T.c.query('.') AS result
FROM @x.nodes('/Root/row') T(c)

SELECT T.c.value('./name[1]', 'nvarchar(max)') AS NAME
,T.c.value('./oflw[1]', 'nvarchar(max)') AS oflw
,T.c.value('@code', 'nvarchar(max)') AS code
,T.c.value('@CodeName', 'nvarchar(max)') AS CodeName
FROM @x.nodes('/Root/row') T(c)

SELECT T.c.value('./name[1]', 'nvarchar(max)') AS NAME
,T.c.value('./oflw[1]', 'nvarchar(max)') AS oflw
,T.c.value('@code', 'nvarchar(max)') AS code
,T.c.value('@CodeName', 'nvarchar(max)') AS CodeName
,T.c.value('./item[1]/book[1]', 'nvarchar(max)') AS book
FROM @x.nodes('/Root/row') T(c)

SELECT T.c.value('./../name[1]', 'nvarchar(max)') AS NAME
,T.c.value('./../oflw[1]', 'nvarchar(max)') AS oflw
,T.c.value('../@code', 'nvarchar(max)') AS code
,T.c.value('../@CodeName', 'nvarchar(max)') AS CodeName
,T.c.value('./book[1]', 'nvarchar(max)') AS book
,T.c.value('../name[1]', 'nvarchar(max)') AS name2
,T.c.value('../oflw[1]', 'nvarchar(max)') AS oflw2
FROM @x.nodes('/Root/row/item') T(c)