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
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'