请问，如何把一段以元素为中心的XML转成表（用select...FROM @sXML.nodes方式）

[zzhong2]

下面一段XML（以元素为中心的）
<row>
<NodeID>1</NodeID>
<NodeText>鼻咽部</NodeText>
<DepartmentID>1</DepartmentID>
</row>
<row>
<NodeID>2</NodeID>
<Parent>1</Parent>
<NodeText>血管</NodeText>
<DepartmentID>1</DepartmentID>
</row>
<row>
<NodeID>3</NodeID>
<Parent>1</Parent>
<NodeText>睾丸癌</NodeText>
<DepartmentID>1</DepartmentID>
</row>
要转成下面样子的表（用select...FROM @sXML.nodes方式）
NodeID Parent NodeText DepartmentID
1 NULL 鼻咽部 1
2 1 血管 1
3 1 睾丸癌 1

请问各位高人如何用select...FROM @sXML.nodes方式完成上面的转换？

[zzhong2]

多谢nwctim 大侠，要的就是这个结果

[nwctim]

DECLARE @XML XML SET @XML = '<row> <NodeID>1</NodeID> <NodeText>鼻咽部</NodeText> <DepartmentID>1</DepartmentID> </row> <row> <NodeID>2</NodeID> <Parent>1</Parent> <NodeText>血管</NodeText> <DepartmentID>1</DepartmentID> </row> <row> <NodeID>3</NodeID> <Parent>1</Parent> <NodeText>睾丸癌</NodeText> <DepartmentID>1</DepartmentID> </row>' SELECT doc.node.value('NodeID[1]', 'varchar(5)') as ID ,doc.node.value('Parent[1]', 'varchar(32)') AS Parent ,doc.node.value('NodeText[1]', 'varchar(32)') AS NodeText ,doc.node.value('DepartmentID[1]', 'varchar(32)') AS DepartmentID FROM @XML.nodes('/row') AS doc(node)