数据库教程: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,  ('https://……?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,  ('https://……?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)