Skip to Main Content

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

XML Extract Issue - Namespace

765653Apr 27 2010 — edited Apr 27 2010
Hi,
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!
This post has been answered by chrisis on Apr 27 2010
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 25 2010
Added on Apr 27 2010
2 comments
2,556 views