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

栏目: 数据库 · XML · 发布时间: 7年前

内容简介:这篇文章主要介绍了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把两组都查询出来的表放到一个里面


以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持 码农网

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

Perl语言编程

Perl语言编程

[美] Larry Wall、Tom Christiansen、Jon Orwant / 何伟平 / 中国电力出版社 / 2001-12 / 129.00元

这不仅仅是一本关于Perl的书籍,更是一本独一无二的开发者自己介绍该语言及其文化的书籍。Larry Wall是Perl的开发者,他就这种语言的未来发展方向提出了自己的看法。Tom Christiansen是最早的几个拥护者之一,也是少数几个在错综复杂的中游刃有余的人之一。Jon Orwant是《Perl Journal》的主编,该杂志把Perl社区组合成了一个共同的论坛,以进行Perl新的开发。一起来看看 《Perl语言编程》 这本书的介绍吧!

HTML 压缩/解压工具
HTML 压缩/解压工具

在线压缩/解压 HTML 代码

图片转BASE64编码
图片转BASE64编码

在线图片转Base64编码工具

Markdown 在线编辑器
Markdown 在线编辑器

Markdown 在线编辑器