SQL parsing XML

-------------------------------Success------------------------------------------------------------

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

-----------------Semi-Finished----------------------

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

----------------Semi-Finished---------------

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

------------Semi-finished---------

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 () PATH

'/item/colunm[@name = "Workcode"] ',

DPAPC VARCHAR2 () PATH

'/item/colunm[@name = "DPAPC"] ',

Failmodelname VARCHAR2 () 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 () PATH '/user_deal_info/user_deal_id[@name = "AAA"],

User_deal_b VARCHAR2 () PATH '/user_deal_info/user_deal_id[@name = "BBB"] ',

Deal_inure_time VARCHAR2 () PATH '/user_deal_info/deal_inure_time ',

Deal_expire_time VARCHAR2 () PATH '/user_deal_info/deal_expire_time ',

Deal_create_time VARCHAR2 () PATH '/user_deal_info/deal_create_time ')

It is a necessity to parse XML with SQL. The CLOB fields generated by the row and column conversions are presented on the page and are too lazy to use code parsing just to apply the presentation tools that you have written.

After a day of tossing and asking Ibatis is a very good tool.

And then

1. Convert the result of a line train containing XML XmlType through To_clob (workcode_xml) to Clob

2. Use the Ibatis configuration file to isolate the string string to be converted to XML

2. Parse the string to encapsulate the grid

Finally the problem solved let go!

Write this blog post only for the retention of experience to facilitate the search.

This article from "Oak" blog, reproduced please contact the author!

SQL parsing XML