SQL解析XML
-------------------------------成功------------------------------------------------------------
創(chuàng)新互聯(lián)服務(wù)項(xiàng)目包括織金網(wǎng)站建設(shè)、織金網(wǎng)站制作、織金網(wǎng)頁(yè)制作以及織金網(wǎng)絡(luò)營(yíng)銷策劃等。多年來(lái)，我們專注于互聯(lián)網(wǎng)行業(yè)，利用自身積累的技術(shù)優(yōu)勢(shì)、行業(yè)經(jīng)驗(yàn)、深度合作伙伴關(guān)系等，向廣大中小型企業(yè)、政府機(jī)構(gòu)等提供互聯(lián)網(wǎng)行業(yè)的解決方案，織金網(wǎng)站推廣取得了明顯的社會(huì)效益與經(jīng)濟(jì)效益。目前，我們服務(wù)的客戶以成都為中心已經(jīng)輻射到織金省份的部分城市，未來(lái)相信會(huì)繼續(xù)擴(kuò)大服務(wù)區(qū)域并繼續(xù)獲得客戶的支持與信任！
with pivot_info as(
select * from (
select t.workcode, to_char(count(1)) DPAPC,failmodeldic.dic_name failmodelname
from tdm_testpart t,pub_dictionary failmodeldic
where t.workcode is not null
and t.isdpa = '1'
and t.isreturn=1
and t.failuremodel=failmodeldic.dic_code(+)
group by t.workcode,failmodeldic.dic_name
) pivot xml(max(DPAPC) DPAPC,max(failmodelname) failmodelname for workcode in (select distinct tt.workcode
from TDM_TESTPART tt
where tt.workcode is not null)))
select extractvalue (value (t), '/item/column[@name="WORKCODE"]') WORKCODE,
extractvalue (value (t), '/item/column[@name="DPAPC"]') DPAPC,
extractvalue (value (t), '/item/column[@name="FAILMODELNAME"]') FAILMODELNAME
from pivot_info,
XMLTable ('/PivotSet/item' passing WORKCODE_XML) t
-----------------半成品----------------------
with pivot_info as(
select * from (
select t.workcode, to_char(count(1)) DPAPC,failmodeldic.dic_name failmodelname
from tdm_testpart t,pub_dictionary failmodeldic
where t.workcode is not null
and t.isdpa = '1'
and t.isreturn=1
and t.failuremodel=failmodeldic.dic_code(+)
group by t.workcode,failmodeldic.dic_name
) pivot xml(max(DPAPC) DPAPC,max(failmodelname) failmodelname for workcode in (select distinct tt.workcode
from TDM_TESTPART tt
where tt.workcode is not null)))
select extractvalue (value (t), '/item/colunm[@name="WORKCODE"]') WORKCODE,
extractvalue (value (t), '/item/colunm[@name="DPAPC"]') DPAPC,
extractvalue (value (t), '/item/colunm[@name="FAILMODELNAME"]') FAILMODELNAME
from pivot_info,
XMLTable ('/PivotSet/item' passing WORKCODE_XML) t
----------------半成品---------------
with a as(
select * from (
select t.workcode, to_char(count(1)) DPAPC,failmodeldic.dic_name failmodelname
from tdm_testpart t,pub_dictionary failmodeldic
where t.workcode is not null
and t.isdpa = '1'
and t.isreturn=1
and t.failuremodel=failmodeldic.dic_code(+)
group by t.workcode,failmodeldic.dic_name
) pivot xml(max(DPAPC) DPAPC,max(failmodelname) failmodelname for workcode in (select distinct tt.workcode
from TDM_TESTPART tt
where tt.workcode is not null)))
select extractvalue (WORKCODE_XML, '/PivotSet//item/colunm[@name="WORKCODE"]/text()') WORKCODE,
extractvalue (WORKCODE_XML, '/PivotSet/item/colunm[@name="DPAPC"]/text()') DPAPC,
extractvalue (WORKCODE_XML, '/PivotSet/item/colunm[@name="FAILMODELNAME"]/text()') FAILMODELNAME
from a
------------半成品---------
select *
from (
select workcode ,DPAPC,failmodelname from(
select t.workcode, to_char(count(1)) DPAPC,failmodeldic.dic_name failmodelname
from tdm_testpart t,pub_dictionary failmodeldic
where t.workcode is not null
and t.isdpa = '1'
and t.isreturn=1
and t.failuremodel=failmodeldic.dic_code(+)
group by t.workcode,failmodeldic.dic_name
) ) pivot xml(max(DPAPC) DPAPC,max(failmodelname) failmodelname for workcode in (select distinct tt.workcode
from TDM_TESTPART tt
where tt.workcode is not null)) b,
xmltable('/PivotSet' passing b.workcode_xml columns
WORKCODE VARCHAR2(50) PATH
'/item/colunm[@name="WORKCODE"]',
DPAPC VARCHAR2(50) PATH
'/item/colunm[@name="DPAPC"]',
FAILMODELNAME VARCHAR2(50) PATH
'/item/colunm[@name="FAILMODELNAME"]')
--------------demo-------------
SELECT * FROM XMLTABLE('$B/DEAL_BASIC/USER_DEAL_INFO' PASSING XMLTYPE('
') AS B
COLUMNS USER_DEAL_A VARCHAR2(50) PATH '/USER_DEAL_INFO/USER_DEAL_ID[@name="AAA"]',
USER_DEAL_B VARCHAR2(50) PATH '/USER_DEAL_INFO/USER_DEAL_ID[@name="BBB"]',
DEAL_INURE_TIME VARCHAR2(50) PATH '/USER_DEAL_INFO/DEAL_INURE_TIME',
DEAL_EXPIRE_TIME VARCHAR2(50) PATH '/USER_DEAL_INFO/DEAL_EXPIRE_TIME',
DEAL_CREATE_TIME VARCHAR2(50) PATH '/USER_DEAL_INFO/DEAL_CREATE_TIME')
這里之所以非得用sql來(lái)解析xml 也是不得已。行列轉(zhuǎn)換生成的Clob字段得給頁(yè)面上展示 又懶得用代碼解析 只想套用自己寫好的展示工具。
后來(lái)折騰了一天之后 經(jīng)過(guò)請(qǐng)教 ibatis 是個(gè)很好的工具。
然后
1.將行專列 的包含 XML 的結(jié)果 xmltype 通過(guò) to_clob（workcode_xml） 轉(zhuǎn)換成clob
2.用ibatis 配置文件 查出來(lái)轉(zhuǎn)換成 XML 的 String 字符串
2.對(duì)字符串進(jìn)行解析 封裝grid
終于問(wèn)題解決 釋懷了！
寫此博文 只為保留經(jīng)驗(yàn) 以備用的時(shí)候方便查詢。
分享文章：SQL解析XML
分享網(wǎng)址：http://m.jiaotiyi.com/article/joeohj.html