首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 数据库 > SQL Server >

求解析一个xml,该怎么解决

2012-11-04 
求解析一个xmlDECLARE @DocHandle intdeclare @x as xmlset @xcustomersemployee emp7725342193

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


结果

idcustomerIDCustomer NamedescHome Numberemployee nameemployee id
1256-5879Williamdev152-456-5632William7725342193

[解决办法]
参考:http://www.cnblogs.com/MR_ke/archive/2010/08/23/1806460.html
[解决办法]

SQL code
DECLARE @DocHandle int declare @x as xmlset @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)) ajoin(    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)) bon a.[Customer Name]=b.[employee name]/*customerID    Customer Name    desc    Home Number    employee id    employee name256-5879    William    dev    152-456-5632    7725342193    William*/ 

热点排行