Home » SQL & PL/SQL » SQL & PL/SQL » how to write the Query to convert the XML to table
how to write the Query to convert the XML to table [message #568614] Mon, 15 October 2012 01:57 Go to next message
mm_kanish05
Messages: 479
Registered: January 2007
Location: Chennai
Senior Member
Dear Experts,

I have table xx_xml_test
which have row single entry

<logentry
   revision="3">
<author>MA111300</author>
<date>2012-10-03T12:42:40</date>
<paths>
<path
   kind="file"
   action="A">/root.txt</path>
<path
   kind="file"
   action="A">/sample2/test_2.txt</path>
<path
   kind="dir"
   action="A">/sample1</path>
<path
   kind="file"
   action="A">/sample2/sample2.1/Test_2.1.txt</path>
<path
   kind="dir"
   action="A">/sample1/sample1.1</path>
<path
   kind="dir"
   action="A">/sample2</path>
<path
   kind="dir"
   action="A">/sample2/sample2.1</path>
<path
   kind="file"
   action="A">/sample1/sample1.1/test_1.1.txt</path>
<path
   kind="file"
   action="A">/sample1/test_1.txt</path>
</paths>
<msg></msg>
</logentry>


i want convert the table like below

Revision    author          date                  kind     action    path
3           MA111300      2012-10-03 12:42:40     file        A     /root.txt
3           MA111300      2012-10-03 12:42:40     file        A     /sample2/test_2.txt


Is it possible.

Report message to a moderator

Re: how to write the Query to convert the XML to table [message #568617 is a reply to message #568614] Mon, 15 October 2012 02:49 Go to previous messageGo to next message
Michel Cadot
Messages: 54672
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
 
SQL> col revision format a8
SQL> col author format a8
SQL> col date format a19
SQL> col kind format a4
SQL> col action format a1
SQL> col path format a50
SQL> with
  2    data as (
  3      select xmltype('<logentry
  4     revision="3">
  5  <author>MA111300</author>
  6  <date>2012-10-03T12:42:40</date>
  7  <paths>
  8  <path
  9     kind="file"
 10     action="A">/root.txt</path>
 11  <path
 12     kind="file"
 13     action="A">/sample2/test_2.txt</path>
 14  <path
 15     kind="dir"
 16     action="A">/sample1</path>
 17  <path
 18     kind="file"
 19     action="A">/sample2/sample2.1/Test_2.1.txt</path>
 20  <path
 21     kind="dir"
 22     action="A">/sample1/sample1.1</path>
 23  <path
 24     kind="dir"
 25     action="A">/sample2</path>
 26  <path
 27     kind="dir"
 28     action="A">/sample2/sample2.1</path>
 29  <path
 30     kind="file"
 31     action="A">/sample1/sample1.1/test_1.1.txt</path>
 32  <path
 33     kind="file"
 34     action="A">/sample1/test_1.txt</path>
 35  </paths>
 36  <msg></msg>
 37  </logentry>') val from dual
 38  )
 39  select extractvalue(value(x), '/logentry/@revision') revision,
 40         extractvalue(value(x), '/logentry/author') author,
 41         extractvalue(value(x), '/logentry/date') "date", 
 42         extractvalue(value(y), '/path/@kind') kind,
 43         extractvalue(value(y), '/path/@action') action,
 44         extractvalue(value(y), '/path') path
 45  from data, 
 46       table(xmlsequence(extract(val, '/logentry'))) x,
 47       table(xmlsequence(extract(val, '/logentry/paths/path'))) y 
 48  /
REVISION AUTHOR   date                KIND A PATH
-------- -------- ------------------- ---- - --------------------------------------------------
3        MA111300 2012-10-03T12:42:40 file A /root.txt
3        MA111300 2012-10-03T12:42:40 file A /sample2/test_2.txt
3        MA111300 2012-10-03T12:42:40 dir  A /sample1
3        MA111300 2012-10-03T12:42:40 file A /sample2/sample2.1/Test_2.1.txt
3        MA111300 2012-10-03T12:42:40 dir  A /sample1/sample1.1
3        MA111300 2012-10-03T12:42:40 dir  A /sample2
3        MA111300 2012-10-03T12:42:40 dir  A /sample2/sample2.1
3        MA111300 2012-10-03T12:42:40 file A /sample1/sample1.1/test_1.1.txt
3        MA111300 2012-10-03T12:42:40 file A /sample1/test_1.txt

Regards
Michel

Report message to a moderator

Re: how to write the Query to convert the XML to table [message #568620 is a reply to message #568617] Mon, 15 October 2012 04:11 Go to previous message
_jum
Messages: 459
Registered: February 2008
Senior Member
Oracle promotes the use of the XMLTable function on 10.2.x and higher Oracle versions rather than XLMSEQUENCE so (if you are on ORACLE 10.2 or higher) you could use:
WITH data AS (
  SELECT xmltype('<logentry
      revision="3">
   <author>MA111300</author>
   <date>2012-10-03T12:42:40</date>
   <paths>
   <path
      kind="file"
      action="A">/root.txt</path>
   <path
      kind="file"
      action="A">/sample2/test_2.txt</path>
   <path
      kind="dir"
      action="A">/sample1</path>
   <path
      kind="file"
      action="A">/sample2/sample2.1/Test_2.1.txt</path>
   <path
      kind="dir"
      action="A">/sample1/sample1.1</path>
   <path
      kind="dir"
      action="A">/sample2</path>
   <path
      kind="dir"
      action="A">/sample2/sample2.1</path>
   <path
      kind="file"
      action="A">/sample1/sample1.1/test_1.1.txt</path>
   <path
      kind="file"
      action="A">/sample1/test_1.txt</path>
   </paths>
   <msg></msg>
   </logentry>') val FROM dual) 
   SELECT 
     extractvalue(data.val, '/logentry/@revision') revision,
     extractvalue(data.val, '/logentry/author')    autor,
     extractvalue(data.val, '/logentry/date')      rdate,
     y.kind,
     y.action,
     y.path
   FROM data,
        XMLTABLE('/logentry/paths'
          PASSING val 
          COLUMNS strpath xmltype path '//path') x,
        XMLTABLE ('//path'
        PASSING x.strpath
          COLUMNS kind   VARCHAR2(30) path '//path/@kind',
                  path   VARCHAR2(30) path '//path',
                  action VARCHAR2(30) path '//path/@action') y; 


REVISION AUTHOR   RDATE                         KIND ACTION     PATH
-------- -------- ----------------------------- ---- ---------- ----------------         
3	MA111300	2012-10-03T12:42:40	file	A	/root.txt
3	MA111300	2012-10-03T12:42:40	file	A	/sample2/test_2.txt
3	MA111300	2012-10-03T12:42:40	dir	A	/sample1
3	MA111300	2012-10-03T12:42:40	file	A	/sample2/sample2.1/Test_2.1.tx
3	MA111300	2012-10-03T12:42:40	dir	A	/sample1/sample1.1
3	MA111300	2012-10-03T12:42:40	dir	A	/sample2
3	MA111300	2012-10-03T12:42:40	dir	A	/sample2/sample2.1
3	MA111300	2012-10-03T12:42:40	file	A	/sample1/sample1.1/test_1.1.tx
3	MA111300	2012-10-03T12:42:40	file	A	/sample1/test_1.txt

[Updated on: Mon, 15 October 2012 04:16] by Moderator

Report message to a moderator

Previous Topic: Best logic to iterate
Next Topic: How to fetch value which column has timestamp datatype?
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Jun 18 20:41:43 CDT 2013

Total time taken to generate the page: 0.14123 seconds
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.