Here is sample for Books online that uses XQuery methods

DECLARE @x xml

-- Replace "!" with " ", as the code formatting eats up the XML

SET @x='!Root>

!row id="1">!name>Larry!/name>!oflw>some text!/oflw>!/row>

!row id="2">!name>moe!/name>!/row>

!row id="3" /!

!/Root>'

SELECT T.c.query('.') AS result

FROM @x.nodes('/Root/row') T(c)

SELECT T.c.value('name[1]', 'VARCHAR(100)' ) AS result,

T.c.value('oflw[1]', 'VARCHAR(100)' ) AS result

FROM @x.nodes('/Root/row') T(c)

Edit:

Replaced the existing XML to non XML