Here is a snippet from some XML that is held in CLOB Column on a database
<applicationVerificationResponse>
.... some data....
<errorsOrWarnings>
<errorMessage>
<errorMessage>
<errorMessage>BV3: Validation Failure INVALID - Sortcode</errorMessage>
</errorMessage>
<errorMessage>
<errorMessage>ErrorID(923024142):System Error Contact Support</errorMessage>
</errorMessage>
</errorMessage>
</errorsOrWarnings>
</applicationVerificationResponse>What I am trying to do is extract the text values from the lowest node level errorMessage tags, I can't do anything about the XML format as it comes in from another system.
I have tried this code
SELECT XMLQuery('/applicationVerificationResponse/errorsOrWarnings/errorMessage/errorMessage/errorMessage/text()'
PASSING XMLTYPE(xml_data) RETURNING CONTENT) Message
FROM my_table
WHERE <col1> = <value>
AND <col2> = <value>;I get this answer
BV3: Validation Failure INVALID - SortcodeErrorID(923024142):System Error Contact Support
Whereas I want to get back two rows of data like this
BV3: Validation Failure INVALID - Sortcode
ErrorID(923024142):System Error Contact Support
I've found lots of articles on the Internet but none seem to give me the answer I'm looking for.
Use XMLTable to convert xml to rows-and-columns:
with rws as (
select '<applicationVerificationResponse>
<errorsOrWarnings>
<errorMessage>
<errorMessage>
<errorMessage>BV3: Validation Failure INVALID - Sortcode</errorMessage>
</errorMessage>
<errorMessage>
<errorMessage>ErrorID(923024142):System Error Contact Support</errorMessage>
</errorMessage>
</errorMessage>
</errorsOrWarnings>
</applicationVerificationResponse>' x from dual
)
select errorMessage from rws, xmltable (
'/applicationVerificationResponse/errorsOrWarnings/errorMessage/errorMessage'
passing xmltype ( x )
columns
errorMessage varchar2(100) path 'errorMessage'
);
ERRORMESSAGE
BV3: Validation Failure INVALID - Sortcode
ErrorID(923024142):System Error Contact Support