Oracle PL/SQL parsing a nested object within xml using XMLTABLE

Question

everyone! I have the following problem - I am using Oracle 11g and I am trying to read values from a xml file, which is a response of web service. It has a structure like that:

<Response id="response">
             <ns14:StatusResponse>
                 <Code>3</Code>
                 <StatusName>Undefined</StatusName>
                 <FirstName>George</FirstName>
                 <MiddleName>Francis</MiddleName>
                 <LastName>Rosetown</LastName>
                 <Number>*******</Number>
                 <Gender>1</Gender>
                 <Relatives>
                     <Relative>
                         <OrderNumber>1</OrderNumber>
                         <FirstName>Louis</FirstName>
                         <MiddleName>Matel</MiddleName>
                         <LastName>Johnson</LastName>
                         <NUmber>********</Number>
                         <RelativeType>Spouse</RelativeType>
                     </Relative>
                     <Relative>
                         <OrderNumber>2</OrderNumber>
                         <FirstName>Jack</FirstName>
                         <MiddleName>Francis</MiddleName>
                         <LastName>Rosetown</LastName>
                         <EGN>**********</EGN>
                         <RelativeType>Son</RelativeType>
                     </Relative>
                 </Relatives>
                 <ReportDate>2019-09-05T15:22:42.6829678+03:00</ReportDate>
             </StatusResponse>
         </Response>

How I can retrieve the values of the child nodes 'Relative'? I am concatenating the information from the response and I form a html table for further visualisation to the end user. What can i use to achieve my goal. I am kind of new to these practises. Thanks in advance.

Answer 1

XMLTable can do this. I had to make two small changes to your XML - I removed the namespace and fixed the case of "NUmber". I'm sure there's a way to handle namespaces in Oracle XML, I just don't have experience with it. But you'll need to make sure the XML is syntactically valid or it won't load.

select OrderNumber, FirstName, MiddleName, LastName, EGN, RelativeType
from
(
    select xmltype('
    <Response id="response">
                 <StatusResponse>
                     <Code>3</Code>
                     <StatusName>Undefined</StatusName>
                     <FirstName>George</FirstName>
                     <MiddleName>Francis</MiddleName>
                     <LastName>Rosetown</LastName>
                     <Number>*******</Number>
                     <Gender>1</Gender>
                     <Relatives>
                         <Relative>
                             <OrderNumber>1</OrderNumber>
                             <FirstName>Louis</FirstName>
                             <MiddleName>Matel</MiddleName>
                             <LastName>Johnson</LastName>
                             <Number>********</Number>
                             <RelativeType>Spouse</RelativeType>
                         </Relative>
                         <Relative>
                             <OrderNumber>2</OrderNumber>
                             <FirstName>Jack</FirstName>
                             <MiddleName>Francis</MiddleName>
                             <LastName>Rosetown</LastName>
                             <EGN>**********</EGN>
                             <RelativeType>Son</RelativeType>
                         </Relative>
                     </Relatives>
                     <ReportDate>2019-09-05T15:22:42.6829678+03:00</ReportDate>
                 </StatusResponse>
             </Response>') relative_xml
    from dual
), xmltable('/Response/StatusResponse/Relatives/Relative'
    passing relative_xml
    columns
        OrderNumber number path 'OrderNumber',
        FirstName varchar2(4000) path 'FirstName',
        MiddleName varchar2(4000) path 'MiddleName',
        LastName varchar2(4000) path 'LastName',
        EGN varchar2(4000) path 'EGN',
        RelativeType varchar2(4000) path 'RelativeType'
);

Results:

ORDERNUMBER  FIRSTNAME  MIDDLENAME  LASTNAME  EGN         RELATIVETYPE
-----------  ---------  ----------  --------  ----------  ------------
          1      Louis       Matel   Johnson                    Spouse
          2       Jack     Franci   Rosetown  **********           Son