SQL解析XML

-------------------------------成功------------------------------------------------------------

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

终于问题解决 释怀了！

写此博文 只为保留经验 以备用的时候方便查询。