Here it is :
DECLARE @GroupXML AS XML
SET @GroupXML=
'<GroupA>
<ParantNode Id="001">
<ChildNode Name ="AA01"> </ChildNode>
<ChildNode Name ="AA02"> </ChildNode>
</ParantNode>
<ParantNode Id="002">
<ChildNode Name ="BB"> </ChildNode>
</ParantNode>
</GroupA>'
SELECT cast( GroupXML.GX.value('../@Id', 'NVARCHAR(50)') as int) ParentIndex, GroupXML.GX.value('@Name', 'NVARCHAR(50)') ChildName
FROM @GroupXML.nodes('/GroupA/ParantNode/ChildNode') AS GroupXML(GX)
Have look at these pages :
http://msdn.microsoft.com/en-us/library/ms178030%28v=sql.100%29.aspx[
^]
http://www.w3schools.com/xpath/xpath_syntax.asp[
^]
BTW, I'm not in western World ;)
Hope it helps.