XML Extract Issue - Namespace
765653Apr 27 2010 — edited Apr 27 2010Hi,
I'm trying to extract an element value from a xml, but it's not working when I put the namespace.
Here is the XML:
<?xml version="1.0" encoding="UTF-8" ?>
<nfeProc xmlns="http://www.portalfiscal.inf.br/nfe" xmlns:ds="http://www.w3.org/2000/09/xmldsig#" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" versao="1.10">
<NFe xmlns="http://www.portalfiscal.inf.br/nfe">
<infNFe Id="NFe35100492754738007094550010000418641120612904" versao="1.10">
<ide>
<cUF>35</cUF>
<cNF>112061290</cNF>
<natOp>DEVOLUCAO DE COMPRA PARA COMERCIALIZACAO</natOp>
<indPag>0</indPag>
<mod>55</mod>
<serie>1</serie>
<nNF>41864</nNF>
<dEmi>2010-04-26</dEmi>
<dSaiEnt>2010-04-26</dSaiEnt>
<tpNF>1</tpNF>
<cMunFG>3548708</cMunFG>
<tpImp>1</tpImp>
<tpEmis>1</tpEmis>
<cDV>4</cDV>
<tpAmb>2</tpAmb>
<finNFe>1</finNFe>
<procEmi>0</procEmi>
<verProc>1.0</verProc>
</ide>
</infNFe>
</NFe>
</nfeProc>
But this XML is a BLOB in a table, that I convert to CLOB and after to XMLTYPE.
And here is the SQL that I'm trying to execute:
select extract(column_value,'cUF/text()') AS "XML ELEMENT"
from (select xmltype(blob_to_clob(fl_l.file_data)) media_data_clob
from fnd_attached_documents fad_l
, fnd_documents_tl fdt_l
, fnd_lobs fl_l
where fad_l.document_id = fdt_l.document_id
and fdt_l.media_id = fl_l.file_id
and fdt_l.language = 'PTB'
and fad_l.entity_name = 'NFe_iSupplier') myxml,
table(xmlsequence(extract(media_data_clob,'//cUF'))) t;
Anyone can help me?
Thanks!