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('

              <DEAL_BASIC>

                 <USER_DEAL_INFO>

                     <USER_DEAL_ID name="AAA">A</USER_DEAL_ID>

                     <USER_DEAL_ID name="BBB">B</USER_DEAL_ID>

                     <DEAL_INURE_TIME>C</DEAL_INURE_TIME>

                     <DEAL_EXPIRE_TIME>D</DEAL_EXPIRE_TIME>

                     <DEAL_CREATE_TIME>E</DEAL_CREATE_TIME>

                 </USER_DEAL_INFO>

                 <USER_DEAL_INFO>

                     <USER_DEAL_ID name="AAA">1</USER_DEAL_ID>

                     <USER_DEAL_ID name="BBB">2</USER_DEAL_ID>

                     <DEAL_INURE_TIME>3</DEAL_INURE_TIME>

                     <DEAL_EXPIRE_TIME>4</DEAL_EXPIRE_TIME>

                     <DEAL_CREATE_TIME>5</DEAL_CREATE_TIME>

                 </USER_DEAL_INFO>

                 <USER_DEAL_INFO>

                     <USER_DEAL_ID name="AAA">6</USER_DEAL_ID>

                     <USER_DEAL_ID name="BBB">7</USER_DEAL_ID>

                     <DEAL_INURE_TIME>8</DEAL_INURE_TIME>

                     <DEAL_EXPIRE_TIME>9</DEAL_EXPIRE_TIME>

                     <DEAL_CREATE_TIME>10</DEAL_CREATE_TIME>

                 </USER_DEAL_INFO>

              </DEAL_BASIC>') 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 也是不得已。行列轉(zhuǎn)換生成的Clob字段得給頁面上展示  又懶得用代碼解析 只想套用自己寫好的展示工具。

后來折騰了一天之后 經(jīng)過請教 ibatis 是個很好的工具。 

然后

1.將行專列 的包含 XML 的結(jié)果 xmltype 通過 to_clob（workcode_xml） 轉(zhuǎn)換成clob 

2.用ibatis 配置文件 查出來轉(zhuǎn)換成 XML 的 String 字符串

2.對字符串進行解析 封裝grid

終于問題解決 釋懷了！

寫此博文 只為保留經(jīng)驗 以備用的時候方便查詢。