SQL 联合查询与XML解析实例详解_mssql数据库教程-查字典教程网
SQL 联合查询与XML解析实例详解
SQL 联合查询与XML解析实例详解
发布时间:2016-12-29 来源:查字典编辑
摘要:SQL联合查询与XML解析实例这里举例说明如何实现该功能:(selecta.EBILLNO,a.EMPNAME,a.APPLYDATE,b....

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把两组都查询出来的表放到一个里面

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

相关阅读
推荐文章
猜你喜欢
附近的人在看
推荐阅读
拓展阅读
  • 大家都在看
  • 小编推荐
  • 猜你喜欢
  • 最新mssql数据库学习
    热门mssql数据库学习
    编程开发子分类