I have a clob column RCRD_CNTNT_TX in an oracle 11g v11.2 table TRNSCN_ERR_RCRD_LOG. The column contains complete xml docs. There are two DealerNumber elements, nested. The outer is in the bar namespace, the inner is in the foo (default) namespace. I want to write a select statement based on the value of the inner DealerNumber.
<?xml version="1.0" encoding="UTF-8"?>
<ns2:FinanceDealList xmlns="foo" xmlns:ns2="bar">
<ns2:FinanceDeal>
<ns2:DealerNumber>
<DealerNumber>5361</DealerNumber>...
I have a query which returns exactly one row, where the clob contains xml.
I tried hardcoding the outer namespace and the path:
SELECT xmltype(ERROR.RCRD_CNTNT_TX).extract('//ns2:FinanceDealList/ns2:FinanceDeal/ns2:DealerNumber/DealerNumber/text()').getStringVal()
FROM TRNSCN_ERR_RCRD_LOG ERROR...
but that complains about an invalid token -- the colon I think, but I don't know what to do about it.
I also tried this:
select xmltype(ERROR.RCRD_CNTNT_TX).extract('//DealerNumber/text()').getStringVal() FROM TRNSCN_ERR_RCRD_LOG ERROR...
I expected this second query to raise an error because there are two DealerNumber elements, the outer one containing an xml fragment, the inner containing the text node I want. But it actually returns null even through the rest of the query, as I mentioned, does return one row with xml in the clob.
Can anyone show me how to write a select based on the value of the the inner, default-namespace DealerNumber?
I also want to avoid hardcoding any namespace.