This discussion is archived
2 Replies Latest reply: Oct 2, 2012 4:16 PM by 964683 RSS

Beginner Question for querying xml

964683 Newbie
Currently Being Moderated
I have Oracle 10g and I am a novice querying XMLs from the Database records. In a table with just one record there is a a clob with the following xml.
<MyXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
     <MyOrganization>
          <Organization>
               <string>Micorosft</string>
          </Organization>
          <Depts>
               <Dept>
                    <Department>Sales</Department>
                    <Employees>
                         <Employee>
                              <Name>Jack</Name>
                              <Salary>400</Salary>
                         </Employee>
                         <Employee>
                              <Name>Michael</Name>
                              <Salary>800</Salary>
                         </Employee>
                    </Employees>
               </Dept>
               <Dept>
                    <Department>Production</Department>
                    <Employees>
                         <Employee>
                              <Name>Richard</Name>
                              <Salary>1000</Salary>
                         </Employee>
                         <Employee>
                              <Name>Jenniffer</Name>
                              <Salary>700</Salary>
                         </Employee>
                    </Employees>
               </Dept>
          </Depts>
     </MyOrganization>
</MyXML>
I want to write a select statement which gives me result as follows

Organization department Employees Salary
Microsoft Sales Jack 400
Microsoft Sales Michael 800
Microsoft Production Richard 1000
Microsoft Production Jennifer 700

Can someone help me with this?

If there are better solutions in Oracle 11g to query xml, please let me know as well- I may plan to upgrade.
  • 1. Re: Beginner Question for querying xml
    odie_63 Guru
    Currently Being Moderated
    On 10.2 and upwards :
    SQL> select x1.org, x2.dept, x3.emp, x3.sal
      2  from tmp_xml_clob t
      3     , xmltable('/MyXML/MyOrganization'
      4         passing xmltype(t.xmlcontent)
      5         columns org   varchar2(20) path 'Organization'
      6               , depts xmltype      path 'Depts/Dept'
      7       ) x1
      8     , xmltable('/Dept'
      9         passing x1.depts
     10         columns dept  varchar2(20) path 'Department'
     11               , emps  xmltype      path 'Employees/Employee'
     12       ) x2
     13     , xmltable('/Employee'
     14         passing x2.emps
     15         columns emp   varchar2(20) path 'Name'
     16               , sal   number       path 'Salary'
     17       ) x3
     18  ;
     
    ORG                  DEPT                 EMP                         SAL
    -------------------- -------------------- -------------------- ----------
    Microsoft            Sales                Jack                        400
    Microsoft            Sales                Michael                     800
    Microsoft            Production           Richard                    1000
    Microsoft            Production           Jennifer                    700
     
    If there are better solutions in Oracle 11g to query xml, please let me know as well- I may plan to upgrade.
    You can do the same in Oracle 11, but I'd recommend you then store the XML document in a binary XMLType column for the best performance.
  • 2. Re: Beginner Question for querying xml
    964683 Newbie
    Currently Being Moderated
    thanks :)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points