SQL 联合查询与XML解析实例详解

sql 联合查询与xml解析实例

          这里举例说明如何实现该功能：

(select a.ebillno,
a.empname,
a.applydate,
b.hs_name,
replace(replace(a.summary,char(10), ''),char(13),'') as summary,
cast(c.xmldata as xml).value('(/list/item/no/text())[1]','nvarchar(300)') as no,
cast(c.xmldata as xml).value('(/list/item/zje/text())[1]','nvarchar(300)') as zje,
cast(c.xmldata as xml).value('(/list/item/yfje/text())[1]','nvarchar(300)') as yfje,
cast(c.xmldata as xml).value('(/list/item/bcje/text())[1]','nvarchar(300)') as bcje,
cast(c.xmldata as xml).value('(/list/item/url/text())[1]','nvarchar(300)') as url,
cast(c.xmldata as xml).value('(/list/item/remark/text())[1]','nvarchar(300)') as bz,
cast(p.xmldata as xml).value('(/newdataset/table1/username/text())[1]','nvarchar(500)') as skrxm,
('http://……?sid=3&mid=7281&pid='+a.pid) as bxdljdz
from ex_bill as a 
left join ex_system_cfg as b on(a.billsystemid=b.hs_id and a.datasystemid=b.system_name)
left join (select * from [10.2.3.39].aspireworkflow.dbo.repeaingtable) as c on (c.keyword='url' and c.processid=a.pid)
left join (select * from [10.2.3.39].aspireworkflow.dbo.repeaingtable) as d on (d.keyword='fkxx_new' and d.processid=a.pid or d.keyword='fkxx' and d.processid=a.pid)
left join (select * from ex_billextension) as p on a.billno=p.bill_no

where applyempid='zhongxun' and a.ebillno is not null
and status>5 and status not in(200,100,7000)
and a.applydate>'2011-01-01'
and a.ht='是'
and cast(d.xmldata as xml).value('(/list/item/skrxm/text())[1]','nvarchar(300)') is null) 
union
(select e.ebillno,
e.empname,
e.applydate,
f.hs_name,
replace(replace(e.summary,char(10), ''),char(13),'') as summary,
cast(g.xmldata as xml).value('(/list/item/no/text())[1]','nvarchar(300)') as no,
cast(g.xmldata as xml).value('(/list/item/zje/text())[1]','nvarchar(300)') as zje,
cast(g.xmldata as xml).value('(/list/item/yfje/text())[1]','nvarchar(300)') as yfje,
cast(g.xmldata as xml).value('(/list/item/bcje/text())[1]','nvarchar(300)') as bcje,
cast(g.xmldata as xml).value('(/list/item/url/text())[1]','nvarchar(300)') as url,
cast(g.xmldata as xml).value('(/list/item/remark/text())[1]','nvarchar(300)') as bz,
cast(h.xmldata as xml).value('(/list/item/skrxm/text())[1]','nvarchar(300)') as skrxm,
('http://……?sid=3&mid=7281&pid='+e.pid) as bxdljdz
from ex_bill as e 
left join ex_system_cfg as f on(e.billsystemid=f.hs_id and e.datasystemid=f.system_name)
left join (select * from [10.2.3.39].aspireworkflow.dbo.repeaingtable) as g on (g.keyword='url' and g.processid=e.pid)
left join (select * from [10.2.3.39].aspireworkflow.dbo.repeaingtable) as h on (h.keyword='fkxx_new' and h.processid=e.pid or h.keyword='fkxx' and h.processid=e.pid)

where applyempid='zhongxun' and e.ebillno is not null
and status>5 and status not in(200,100,7000)
and e.applydate>'2011-01-01'
and e.ht='是'
and cast(h.xmldata as xml).value('(/list/item/skrxm/text())[1]','nvarchar(300)') is not null)

在写sql的时候，难点不在于sql本身，而在于逻辑上，当写出这个sql以后，发现逻辑也没有那么难了。

就是采用union把两组都查询出来的表放到一个里面

感谢阅读，希望能帮助到大家，谢谢大家对本站的支持！