SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Splitting XML tags as Table Rows and Columns

548091Apr 22 2008 — edited Apr 22 2008

Hi,

I have the following CLOB column containing XML:

XML_TYPE
--------
<message><fo_ext_ref>1234</fo_ext_ref><fo_rech><fo_rech_tarif>STEM</fo_rech_tarif><fo_rech_code>=</fo_rech_code><fo_rech_betr_cur>T</fo_rech_betr_cur><fo_rech_betr>-000000001509.85</fo_rech_betr></fo_rech><fo_rech><fo_rech_tarif>DSTA</fo_rech_tarif><fo_rech_code>=</fo_rech_code><fo_rech_betr_cur>T</fo_rech_betr_cur><fo_rech_betr>-000000000004</fo_rech_betr></fo_rech></message>
<message><fo_ext_ref>5678</fo_ext_ref><fo_rech><fo_rech_tarif>STEM</fo_rech_tarif><fo_rech_code>=</fo_rech_code><fo_rech_betr_cur>T</fo_rech_betr_cur><fo_rech_betr>-000000000591.82</fo_rech_betr></fo_rech><fo_rech><fo_rech_tarif>DSTA</fo_rech_tarif><fo_rech_code>=</fo_rech_code><fo_rech_betr_cur>T</fo_rech_betr_cur><fo_rech_betr>-000000000004</fo_rech_betr></fo_rech></message>

**I want the output as:

REF	TARIF  CODE     CUR  BETR
-----------------------------
1234 	STEM 	= 	T 	-000000000591.82
1234 	DSTA 	= 	T 	-000000000004
5678 	STEM 	= 	T 	-000000001509.85
5678 	DSTA 	= 	T 	-000000000004

I have tried the following query:

SELECT 
  (SELECT xml_tab.COLUMN_VALUE.EXTRACT('message/fo_ext_ref/text()').GETSTRINGVAL()
FROM TABLE(XMLSEQUENCE(xmltype.createxml(e.xml_type).EXTRACT('message'))) xml_tab)  AS ref

, (SELECT xml_tab.COLUMN_VALUE.EXTRACT('message/fo_rech/fo_rech_tarif/text()').GETSTRINGVAL()
FROM TABLE(XMLSEQUENCE(xmltype.createxml(e.xml_type).EXTRACT('message'))) xml_tab)  AS tarif

, (SELECT xml_tab.COLUMN_VALUE.EXTRACT('message/fo_rech/fo_rech_code/text()').GETSTRINGVAL()
FROM TABLE(XMLSEQUENCE(xmltype.createxml(e.xml_type).EXTRACT('message'))) xml_tab)  AS code

, (SELECT xml_tab.COLUMN_VALUE.EXTRACT('message/fo_rech/fo_rech_betr_cur/text()').GETSTRINGVAL()
FROM TABLE(XMLSEQUENCE(xmltype.createxml(e.xml_type).EXTRACT('message'))) xml_tab)  AS cur

, (SELECT xml_tab.COLUMN_VALUE.EXTRACT('message/fo_rech/fo_rech_betr/text()').GETSTRINGVAL()
FROM TABLE(XMLSEQUENCE(xmltype.createxml(e.xml_type).EXTRACT('message'))) xml_tab)  AS betr

FROM msg_table e

which gives the output as:

REF	TARIF  	       CODE CUR     BETR
---------------------------------------------------------
1234 	STEMDSTA 	== 	TT 	-000000000591.82-000000000004
5678 	STEMDSTA 	== 	TT 	-000000001509.85-000000000004

I am not able to split the above into multiple rows, even though the query is giving data in a single row.

I would appreciate if you could help me out in providing the above solution given in bold letters.

Thanks in advance,
MAK

Locked Post

New comments cannot be posted to this locked post.

Locked on May 20 2008

Added on Apr 22 2008

7 comments

1,575 views