求解析一个xml
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

*/