Solved

writing oracle xml query

Posted on 2011-09-22
6
614 Views
Last Modified: 2012-08-14
<node1>
 
  <node2 id=1>
    <text1>abc</text1>
    <text2>def</text2>
    <nest>
      <node3 id=1>ghi</node3>
      <node3 id=2>jkl</node3>
    </nest>
  </node2>

  <node2 id=2>
    <text1>xxx</text1>
    <text2>yyy</text2>
  </node2>

</node1>

Hi All,
Can somebody please advise me how to write an oracle xml query to extract the information in the following format from the above xml:

node2  text1  text2  node3_attribute  node3_text
  1     abc   def    1                ghi
  1     abc   def    2                jkl
  2     xxx   yyy  
 
I tried using the xmltable  feature but cannot extract all the information in a single query in the above format.
I am using oracle 10g.
Many thanks in advance.
0
Comment
Question by:subratoc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 36584136
Try this
SELECT xmltype(DBMS_XMLGEN.getxml('select node2,  text1 , text2,  node3_attribute,  node3_text from ' || table_name)), '/ROWSET/ROW/X' FROM your_tabl
0
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 36584141
sorry Typo

SELECT xmltype(DBMS_XMLGEN.getxml('select node2,  text1 , text2,  node3_attribute,  node3_text from your_table')), '/ROWSET/ROW/X' FROM dual
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 36584895
SELECT EXTRACTVALUE(VALUE(x), '/node2/@id') node2,
       EXTRACTVALUE(VALUE(y), '//text1') text1,
       EXTRACTVALUE(VALUE(y), '//text2') text2,
       EXTRACTVALUE(VALUE(z), '//node3/@id') node3_attribute,
       EXTRACTVALUE(VALUE(z), '//node3') node3_text
  FROM XMLTABLE(
          '//node2'
          PASSING xmltype('<node1>
 
  <node2 id="1">
    <text1>abc</text1>
    <text2>def</text2>
    <nest>
      <node3 id="1">ghi</node3>
      <node3 id="2">jkl</node3>
    </nest>
  </node2>

  <node2 id="2">
    <text1>xxx</text1>
    <text2>yyy</text2>
  </node2>

</node1>')
      ) x
      JOIN XMLTABLE(
               '/node2'
               PASSING x.COLUMN_VALUE
           ) y
          ON 1 = 1
      LEFT OUTER JOIN XMLTABLE(
                          '//node3'
                          PASSING y.COLUMN_VALUE
                      ) z
          ON 1 = 1
0
Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

 

Author Comment

by:subratoc
ID: 36585174
Hi ,
Thanks for your replies.
sdstuber, using the above query I cannot see any row corresponding to node2.
On running the query, I am getting the following:

node2  text1  text2  node3_attribute  node3_text
  1        abc     def       1                             ghi
  1        abc      def      2                             jkl

for some reason, I the left outer join is not being used and the following line is not displayed in the result:
node2  text1  text2
"  2     xxx   yyy  "

Regards.
 
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36586143
I don't know what to say,  I tested it, and it works fine for me...

SQL> /

NODE2
---------------------------------------------------------------------------
TEXT1
---------------------------------------------------------------------------
TEXT2
---------------------------------------------------------------------------
NODE3_ATTRIBUTE
---------------------------------------------------------------------------
NODE3_TEXT
---------------------------------------------------------------------------
1
abc
def
1
ghi

1
abc
def
2
jkl

2
xxx
yyy




SQL>
0
 

Author Closing Comment

by:subratoc
ID: 36586759
Thanks a lot sdstuber. Do not know if it is my version of oracle, but when I replaced the left outer join xmltable()z with (+)z it worked just fine.
Cheers.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question