DECLARE @xml XML
SELECT @xml =
'<dept bldg="101">
            <employee id="901">
		<name>
			<first>John</first>
			<last>Doe</last>
		</name>
		<office>344</office>
		<salary currency="USD">55000</salary>
	</employee>
	<employee id="902">
		<name>
			<first>Peter</first>
			<last>Pan</last>
		</name>
		<office>216</office>
		<phone>905-416-5004</phone>
	</employee>
</dept>
'

SELECT xml_data.value('@id[1]','int') empID,
           name_info.value('first[1]','varchar(max)') first_name,
           name_info.value('last[1]','varchar(max)') last_name
FROM @xml.nodes ('dept/employee') xml1(xml_data)
CROSS APPLY xml_data.nodes ('name') name_list(name_info)

SELECT X.* 
FROM emp, 
XMLTABLE ('$d/dept/employee' passing doc as "d" 
   COLUMNS 
   empID 	INTEGER 	PATH '@id',
   firstname 	VARCHAR(20) 	PATH 'name/first',
   lastname 	VARCHAR(25) 	PATH 'name/last') AS X