Search code examples
xmloracle-databasexmltable

Oracle, XMLTABLE, get value from tag, if tag near equals


I have this XML and expression to get values:

SELECT * 
FROM   XMLTABLE('//contract-dates'
                PASSING xmltype('
<contract-dates>
     <other-dates>
       <other-date>
         <date-type>DATE_1</date-type>
         <date-value>2020-01-01</date-value>
       </other-date>
       <other-date>
         <date-type>DATE_2</date-type>
         <date-value>2020-03-30</date-value>
       </other-date>
     </other-dates>
</contract-dates>')

      COLUMNS
        date_1       PATH 'other-dates/other-date[1]/date-value',
        date_2       PATH 'other-dates/other-date[2]/date-value'
);

Problem starts, where first of these values could be null so one tag is gone:

SELECT * 
FROM   XMLTABLE('//contract-dates'
                PASSING xmltype('
<contract-dates>
     <other-dates>
       <other-date>
         <date-type>DATE_2</date-type>
         <date-value>2020-03-30</date-value>
       </other-date>
     </other-dates>
</contract-dates>')

      COLUMNS
        date_1          PATH 'other-dates/other-date[1]/date-value',
        date_2          PATH 'other-dates/other-date[2]/date-value'
);

in this case scenario date_1 contains date from tag date-type = "DATE_2" which is wrong.

Is there a way to say 'get value from date-value if date-type="DATE_1"??

EDIT: This xml is part of bigger xml, contract-dates its just one of child tags


Solution

  • Look for the child element value instead of position:

    date_1       PATH 'other-dates/other-date[date-type="DATE_1"]/date-value',
    date_2       PATH 'other-dates/other-date[date-type="DATE_2"]/date-value'
    

    With your examples modified:

    SELECT * 
    FROM   XMLTABLE('//contract-dates'
                    PASSING xmltype('
    <contract-dates>
         <other-dates>
           <other-date>
             <date-type>DATE_1</date-type>
             <date-value>2020-01-01</date-value>
           </other-date>
           <other-date>
             <date-type>DATE_2</date-type>
             <date-value>2020-03-30</date-value>
           </other-date>
         </other-dates>
    </contract-dates>')
    
          COLUMNS
            date_1       PATH 'other-dates/other-date[date-type="DATE_1"]/date-value',
            date_2       PATH 'other-dates/other-date[date-type="DATE_2"]/date-value'
    );
    
    DATE_1                         DATE_2                        
    ------------------------------ ------------------------------
    2020-01-01                     2020-03-30                    
    

    and

    SELECT * 
    FROM   XMLTABLE('//contract-dates'
                    PASSING xmltype('
    <contract-dates>
         <other-dates>
           <other-date>
             <date-type>DATE_2</date-type>
             <date-value>2020-03-30</date-value>
           </other-date>
         </other-dates>
    </contract-dates>')
    
          COLUMNS
            date_1          PATH 'other-dates/other-date[date-type="DATE_1"]/date-value',
            date_2          PATH 'other-dates/other-date[date-type="DATE_2"]/date-value'
    );
    
    DATE_1                         DATE_2                        
    ------------------------------ ------------------------------
                                   2020-03-30                    
    

    Incidentally, you can specify the data type of the result columns, which can make them a bit easier to deal with; as the values are in a sensibel format you can declare them directly as dates here:

    date_1 date PATH 'other-dates/other-date[date-type="DATE_1"]/date-value',
    date_2 date PATH 'other-dates/other-date[date-type="DATE_2"]/date-value'