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把两组都查询出来的表放到一个里面
感谢阅读，希望能帮助到大家，谢谢大家对本站的支持！