SQL联合查询和XML解析是如何的 如何理解

这篇文章主要讲解了“SQL联合查询和XML解析是怎样的，如何理解”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“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以后，发现逻辑也没有那么难了。

（编辑：银川站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!