SQL 联合查询与XML解析实例详解
SQL联合查询与XML解析实例
这里举例说明如何实现该功能:
(selecta.EBILLNO, a.EMPNAME, a.APPLYDATE, b.HS_NAME, replace(replace(a.SUMMARY,char(10),''),char(13),'')asSUMMARY, cast(c.XmlDataasXML).value('(/List/item/No/text())[1]','NVARCHAR(300)')asNo, cast(c.XmlDataasXML).value('(/List/item/zje/text())[1]','NVARCHAR(300)')aszje, cast(c.XmlDataasXML).value('(/List/item/yfje/text())[1]','NVARCHAR(300)')asyfje, cast(c.XMLDataasXML).value('(/List/item/bcje/text())[1]','NVARCHAR(300)')asbcje, cast(c.XMLDataasXML).value('(/List/item/URL/text())[1]','NVARCHAR(300)')asURL, cast(c.XMLDataasXML).value('(/List/item/Remark/text())[1]','NVARCHAR(300)')asBZ, cast(p.XMLDataasXML).value('(/NewDataSet/Table1/UserName/text())[1]','NVARCHAR(500)')asSKRXM, ('http://……?sid=3&mid=7281&PID='+a.PID)asbxdljdz fromEx_Billasa leftjoinEx_System_Cfgasbon(a.BILLSYSTEMID=b.HS_IDanda.DATASYSTEMID=b.SYSTEM_NAME) leftjoin(select*from[10.2.3.39].AspireworkFlow.dbo.RepeaingTable)ascon(c.Keyword='URL'andc.ProcessID=a.PID) leftjoin(select*from[10.2.3.39].AspireworkFlow.dbo.RepeaingTable)asdon(d.Keyword='FKXX_New'andd.ProcessID=a.PIDord.Keyword='FKXX'andd.ProcessID=a.PID) leftjoin(select*fromEX_BillExtension)aspona.BILLNO=p.BILL_NO whereapplyempid='zhongxun'anda.EBILLNOisnotnull andstatus>5andstatusnotin(200,100,7000) anda.APPLYDATE>'2011-01-01' anda.HT='是' andcast(d.XMLDataasXML).value('(/List/item/SKRXM/text())[1]','NVARCHAR(300)')isnull) union (selecte.EBILLNO, e.EMPNAME, e.APPLYDATE, f.HS_NAME, replace(replace(e.SUMMARY,char(10),''),char(13),'')asSUMMARY, cast(g.XmlDataasXML).value('(/List/item/No/text())[1]','NVARCHAR(300)')asNo, cast(g.XmlDataasXML).value('(/List/item/zje/text())[1]','NVARCHAR(300)')aszje, cast(g.XmlDataasXML).value('(/List/item/yfje/text())[1]','NVARCHAR(300)')asyfje, cast(g.XMLDataasXML).value('(/List/item/bcje/text())[1]','NVARCHAR(300)')asbcje, cast(g.XMLDataasXML).value('(/List/item/URL/text())[1]','NVARCHAR(300)')asURL, cast(g.XMLDataasXML).value('(/List/item/Remark/text())[1]','NVARCHAR(300)')asBZ, cast(h.XMLDataasXML).value('(/List/item/SKRXM/text())[1]','NVARCHAR(300)')asSKRXM, ('http://……?sid=3&mid=7281&PID='+e.PID)asbxdljdz fromEx_Billase leftjoinEx_System_Cfgasfon(e.BILLSYSTEMID=f.HS_IDande.DATASYSTEMID=f.SYSTEM_NAME) leftjoin(select*from[10.2.3.39].AspireworkFlow.dbo.RepeaingTable)asgon(g.Keyword='URL'andg.ProcessID=e.PID) leftjoin(select*from[10.2.3.39].AspireworkFlow.dbo.RepeaingTable)ashon(h.Keyword='FKXX_New'andh.ProcessID=e.PIDorh.Keyword='FKXX'andh.ProcessID=e.PID) whereapplyempid='zhongxun'ande.EBILLNOisnotnull andstatus>5andstatusnotin(200,100,7000) ande.APPLYDATE>'2011-01-01' ande.HT='是' andcast(h.XMLDataasXML).value('(/List/item/SKRXM/text())[1]','NVARCHAR(300)')isnotnull)
在写SQL的时候,难点不在于SQL本身,而在于逻辑上,当写出这个SQL以后,发现逻辑也没有那么难了。
就是采用Union把两组都查询出来的表放到一个里面
感谢阅读,希望能帮助到大家,谢谢大家对本站的支持!