The best described in MSDN documentation below
The nodes() method is useful when you want to shred an xml data type instance into relational data. It allows you to identify nodes that will be mapped into a new row.
Every xml data type instance has an implicitly provided context node. For the XML instance stored in a column or variable, this is the document node. The document node is the implicit node at the top of every xml data type instance.
The result of the nodes() method is a rowset that contains logical copies of the original XML instances. In these logical copies, the context node of every row instance is set to one of the nodes identified with the query expression, so that subsequent queries can navigate relative to these context nodes.
You can retrieve multiple values from the rowset. For example, you can apply the value() method to the rowset returned by nodes() and retrieve multiple values from the original XML instance. Note that the value() method, when applied to the XML instance, returns only one value.
http://msdn.microsoft.com/en-us/library/ms188282.aspx[
^]
On a link you will find number of demos and examples on this topic.
UPD
on a example below
DECLARE @TEMP TABLE (Id int,Name nvarchar(50),Family nvarchar(50),IDNumber int)
INSERT @TEMP
SELECT T.Item.value('Id[1]' , 'varchar(20)'),
T.Item.value('Name[1]' , 'varchar(20)'),
T.Item.value('Family[1]' , 'varchar(20)'),
T.Item.value('IDNumber[1]' , 'varchar(20)')
FROM Person CROSS APPLY X.nodes('/Person') AS T(Item)
procedure actually does nothing.
It creates temporary table (ID, Name, Family, IDNumber) and populates with values 6, AABBc, AABBc, 120000 number of times.
Number of times is equal to number of columns in Person table