Oracle | Extract XML with multiple child node using SELECT query

Question

I have a XML in a clob column in a table. The XML is given below. In a SELECT query, I need to extract the values in xml. Please help.

<Driver>
 <firstName>RAJ</firstName>
 <lastName>KUMAR</lastName>
 <licenses>
  <License>
   <licenseNumber>123456</licenseNumber>
   <licenseType code="ABC"></licenseType>
  </License>
  <License>
   <licenseNumber>XYZ123</licenseNumber>
   <licenseState code="TN"></licenseState>
   <licenseType code="General"></licenseType>
  </License>
  <License>
   <licenseNumber>PK4363</licenseNumber>
   <licenseState code="KL"></licenseState>
   <licenseType code="CS"></licenseType>
  </License>
 </licenses>
 <npiCode>9090909</npiCode>
 <DriverAddresses>
  <DriverAddress>
    <addressLine1>SFDGSDF</addressLine1>
    <city>Chennai</city>
    <DriverContacts>
      <DriverContact>
        <faxNumber>1947</faxNumber>
        <HphoneNumber>007</HphoneNumber>
        <CPhoneNumber>345</CPhoneNumber>
      </DriverContact>
     </DriverContacts>
     <state>KL</state>
     <zipCode>600088</zipCode>
    </DriverAddress>
   </DriverAddresses>
   <Drivertype code="AWD"></Drivertype>
  </Driver>

I tried with

SELECT XMLTYPE(u.xmlcollumn).EXTRACT('/Driver/firstName/text()').getStringVal() from xmltable u;

But I am not able to get the child nodes and the element with code=" "

thanks, PK

Answer 1

I figured out myself. This may be helpful for others who has similar requirement.

SELECT t.id, q.*, a.*, s.*
  FROM Driver_XML t
    LEFT JOIN XMLTABLE
   (
     '/Driver' PASSING XMLTYPE(t.Entity_Xml)
     COLUMNS FirstName     VARCHAR2(200) PATH 'firstName',
             LastName      VARCHAR2(200) PATH 'lastName',
             MiddleName    VARCHAR2(200) PATH 'middleInitial',
             NPICode       VARCHAR2(200) PATH 'npiCode',
             TypeCd        VARCHAR2(200) PATH 'type/@code',
             Locations     XMLTYPE       PATH 'DriverAddresses/DriverAddress',
             License       XMLTYPE       PATH 'licenses/License'
    ) q
    ON ( 1 = 1 )

   LEFT JOIN XMLTABLE
   (
     '/DriverAddress' PASSING q.Locations
     COLUMNS Addr1         VARCHAR2(200) PATH 'addressLine1',
             Addr2         VARCHAR2(200) PATH 'addressLine2',
             City          VARCHAR2(200) PATH 'city',
             State         VARCHAR2(200) PATH 'state',
             Zip           VARCHAR2(200) PATH 'zipCode',
             Contacts      XMLTYPE       PATH 'DriverContacts/DriverContact'
   ) a
    ON ( 1 = 1 )

   LEFT JOIN XMLTABLE
   (
     '/DriverContact' PASSING a.Contacts
     COLUMNS Fax           VARCHAR2(200) PATH 'faxNumber',
             Phone         VARCHAR2(200) PATH 'phoneNumber'
   ) s
    ON ( 1 = 1 )

  LEFT JOIN XMLTABLE
   (
     '/License' PASSING q.License
     COLUMNS licenseNumber VARCHAR2(200) PATH 'licenseNumber',
             licenseState  VARCHAR2(200) PATH 'licenseState/@code',
             licenseType   VARCHAR2(200) PATH 'licenseType/@code'
   ) u
   ON ( 1 = 1 );