Extract text from CLOB PL/SQL NameSpace

Question

I am trying to extract some data from a CLOB column and see it in a separate column. This is the query I am using:

Select xmltype(t.detailbericht).extract('//Tekst_Reden_Afwikkeling/text()').getStringVal()

From ozg_if.mtwk_abcdef t

The colum t.detailbericht contains a CLOB text with the following contents:

<ns0:Zaak xmlns:ns0="http://www.xxxxx.xx/xxx_xxx_xxxxxxxxxxxx/text_text_v002">
    <ns0:Code_Reden_Afwikkeling>D99</ns0:Code_Reden_Afwikkeling>
    <ns0:Tekst_Reden_Afwikkeling>Volledig betaald</ns0:Tekst_Reden_Afwikkeling>
    <ns0:Code_koptekst>490</ns0:Code_koptekst>
    <ns0:Omschrijving_koptekst>Volledig voldaan.</ns0:Omschrijving_koptekst>
</ns0:Zaak>

Anybody got any ideas?

Answer 1

Hmm apparently it seems to work when i replace the ns0: with nothing This is the code I am using:

xmltype(replace(t.detailbericht,'ns0:','')).extract('//Tekst_Reden_Afwikkeling/text()').getStringVal() as Tekst_Reden_Afwikkeling