欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-03-10 07:57:18
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把两组都查询出来的表放到一个里面

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