SQL解析XML

xuxiaohui9216

　　-------------------------------成功------------------------------------------------------------
　　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('
　　
　　
　　A
　　B
　　C
　　D
　　E
　　
　　
　　1
　　2
　　3
　　4
　　5
　　
　　
　　6
　　7
　　8
　　9
　　10
　　
　　') 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来解析xml 也是不得已。行列转换生成的Clob字段得给页面上展示  又懒得用代码解析 只想套用自己写好的展示工具。
　　后来折腾了一天之后 经过请教 ibatis 是个很好的工具。
　　然后
　　1.将行专列 的包含 XML 的结果 xmltype 通过 to_clob（workcode_xml） 转换成clob
　　2.用ibatis 配置文件 查出来转换成 XML 的 String 字符串
　　2.对字符串进行解析 封装grid
　　终于问题解决 释怀了！
　　写此博文 只为保留经验 以备用的时候方便查询。