2 Replies Latest reply: Oct 2, 2012 6:16 PM by 964683 RSS

    Beginner Question for querying xml

    964683
      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
          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
            thanks :)