DECLARE @DocHandle int
declare @x as xml
set @x='<customers>
<employee emp="7725342193">
<name>William</name>
</employee>
<customer cust="256-5879">
<id>1</id>
<info>
<name>William</name>
<desc>dev</desc>
</info>
<numbers>
<home>152-456-5632</home>
<mobile>158-896-7547</mobile>
<fax>854-569-4726</fax>
</numbers>
</customer>
<customer cust="256-5880">
<id>2</id>
<info>
<name>Patricio</name>
<desc>enf</desc>
</info>
<numbers>
<home>589-573-3516</home>
<mobile>358-972-1597</mobile>
<fax></fax>
</numbers>
</customer>
<customer cust="256-6057">
<id>3</id>
<info>
<name>pedro</name>
<desc>worker</desc>
</info>
<numbers>
<home>582-647-5297</home>
<mobile>325-125-4568</mobile>
<fax>879-698-4785</fax>
</numbers>
</customer>
</customers>'
结果
id customerID Customer Name desc Home Number employee name employee id
1 256-5879 William dev 152-456-5632 William 7725342193
------解决方案--------------------
参考：http://www.cnblogs.com/MR_ke/archive/2010/08/23/1806460.html
------解决方案--------------------
DECLARE @DocHandle int
declare @x as xml
set @x='<customers><employee emp="7725342193"><name>William</name></employee><customer cust="256-5879"><id>1</id><info><name>William</name><desc>dev</desc></info><numbers><home>152-456-5632</home><mobile>158-896-7547</mobile><fax>854-569-4726</fax></numbers></customer><customer cust="256-5880"><id>2</id><info><name>Patricio</name><desc>enf</desc></info><numbers><home>589-573-3516</home><mobile>358-972-1597</mobile><fax /></numbers></customer><customer cust="256-6057"><id>3</id><info><name>pedro</name><desc>worker</desc></info><numbers><home>582-647-5297</home><mobile>325-125-4568</mobile><fax>879-698-4785</fax></numbers></customer></customers>'
select * from
(
select
col.value('@cust[1]','varchar(10)') as customerID,
col.value('(info/name)[1]','varchar(50)') as [Customer Name],
col.value('(info/desc)[1]','varchar(50)') as [desc],
col.value('(numbers/home)[1]','varchar(50)') as [Home Number]
from @x.nodes('/customers/customer')T(col)
) a
join
(
select
col.value('@emp[1]','varchar(10)') as [employee id],
col.value('(name)[1]','varchar(50)') as [employee name]
from @x.nodes('/customers/employee')T(col)
) b
on a.[Customer Name]=b.[employee name]
/*
customerID Customer Name desc Home Number employee id employee name
256-5879 William dev 152-456-5632 7725342193 William
*/