Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Francisco Carlos.

Asked: March 15, 2022 - 12:10 pm UTC

Last updated: March 23, 2022 - 2:28 am UTC

Version: 19C

Viewed 10K+ times! This question is

You Asked

Hi all,

I have a XML file into a XMLTYPE field in one table, and I need to get all TAGs content...
But the problem is that after I run the select below, it returns empty (whitout tag content).
Could you help me how solve this, please?


EXAMPLE OF THE XML (partial...):

<?xml version="1.0"?>
-<nfeProc xmlns="http://www.portalfiscal.inf.br/nfe" versao="4.00">
-<NFe xmlns="http://www.portalfiscal.inf.br/nfe">
-<infNFe versao="4.00" Id="NFe33225501438784001250864020001388721704773244">
-<ide>
<cUF>33</cUF>
<cNF>70477324</cNF>
<natOp>VENDA DE MERCADORIA CONFORME CFOP</natOp>
<mod>65</mod>
<serie>2</serie>
<nNF>138842</nNF>
<dhEmi>2022-03-03T11:58:40-03:00</dhEmi>


SELECT MADE:
select ExtractValue(conteudo, '/nfeProc/NFe/infNFe/ide/mod',
               'xmlns="http://www.portalfiscal.inf.br/NFe"')  
  from dbcsi_p2k.tmp_teste_xml;


"conteudo" = XMLTYPE field.

and Chris said...

Pay careful attention to the case of the namespace:

with rws as (
  select xmltype ( '<?xml version="1.0"?>
  <nfeProc xmlns="http://www.portalfiscal.inf.br/nfe" versao="4.00">
  <NFe xmlns="http://www.portalfiscal.inf.br/nfe" >
  <infNFe versao="4.00" Id="NFe33225501438784001250864020001388721704773244">
  <ide>
  <cUF>33</cUF>
  <cNF>70477324</cNF>
  <natOp>VENDA DE MERCADORIA CONFORME CFOP</natOp>
  <mod>65</mod>
  <serie>2</serie>
  <nNF>138842</nNF>
  <dhEmi>2022-03-03T11:58:40-03:00</dhEmi>
  </ide>
  </infNFe>
  </NFe>
  </nfeProc>' ) x from dual
)
  select ExtractValue(
           x, 
           '/nfeProc/NFe/infNFe/ide/mod', 
           'xmlns="http://www.portalfiscal.inf.br/NFe"' 
         ) ev1,
         ExtractValue(
           x, 
           '/nfeProc/NFe/infNFe/ide/mod', 
           'xmlns="http://www.portalfiscal.inf.br/nfe"' 
         ) ev2
  from   rws;

EV1        EV2       
---------- ----------
<null>     65

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Reading TAGs from a XMLTYPE field

Francisco Moya, March 17, 2022 - 4:50 pm UTC

Hi Cris,

Thanks a Lot!!!
It worked as expected. The problem was the missing namespace in the select clause.

As I have no expertize working with XML, I didn´t know about the correct syntaxe.

I have more one doubt:

How I can get the content "CFe23220301438784004356592301302730184738086646" from the extract of the XML below, considering that the EXTRACVALUE command get data only for the nodes?

XML example (partial.....):
<CFe>
<infCFe Id="CFe23220301438784004356592301302730184738086646" versao="0.07" versaoDadosEnt="0.07" versaoSB="010006">
<ide>
<cUF>23</cUF>
<cNF>808664</cNF>
<mod>59</mod>
<nserieSAT>230130273</nserieSAT>
<nCFe>018473</nCFe>
<dEmi>20220303</dEmi>
<hEmi>115535</hEmi>
<cDV>6</cDV>
<tpAmb>1</tpAmb>
<CNPJ>54517628000198</CNPJ>
<signAC>LigakySvQZDIKXvaNBstALcMjKsGqKHr8o6HSmCogN3qj9+RqsaizEJbrftJPtqK1cW2+YzFgIEKPNUTOiYy4zbHFHk2CCU65XEwz4GjvLTpQJfPJCCrIHWPi7bBNFA1ADHGRBjV6IHVhW8YeHWxEvb18RmxUhnj4qtukNfl5NKON6xVfQ85toXfesvDRqJmkGIEuUI017DKSirkB/0v+itAKw+UJthgRWKmFEyBddKGrY3xPrjeziqqVD5DYIYGxZUDpCzdwG4T5Mm7hr9+2bClvHG8fg8Ss/IIaADLopfr3YzFwWkrvk61isAcqroYKkNodkZPmhwzYMhUTnAogg==</signAC>
<assinaturaQRCODE>VabAH61q4Oj7RBu8R0D0MzfPv8p0EuWwkrQ+ZN5kEO2p9iTwx/iqaUpEsOozJhtVTDVxiVSfSTDeDxfs66qMcyI6y/znFIzbdmDwMJhUg1pPdT8jT5e7XhgqlK6JOQTHJ8ZzneVN2JxPTynVFaSkH0ZjFjpMF7CVV2ykEPlbS/aYnSl5ovad2KcCMKC+KaQ1XlxmqOvHPK6XBJIAzn+E7kzByje57KdYOosOPbXThL7T+8rt2+EacPtHdK0H1DkWUYkCRQZzZ4NXgHtF5VEKmayWDxVFtqa2D4KGpDSilxRVj8rCFZ5v+lsZmdVFGanHl5dBwwK+Co+uhfUFcp8AsQ==</assinaturaQRCODE>
<numeroCaixa>030</numeroCaixa>
</ide>
.
.
.

Thanks in advance,

Francisco

Connor McDonald
March 18, 2022 - 5:00 am UTC

Something like this perhaps?

SQL> with testTable as
  2   (
  3   select xmltype('<CFe>
  4  <infCFe Id="CFe23220301438784004356592301302730184738086646" versao="0.07" versaoDadosEnt="0.07" versaoSB="010006">
  5  <ide>
  6  <cUF>23</cUF>
  7  <cNF>808664</cNF>
  8  <mod>59</mod>
  9  </ide>
 10  </infCFe>
 11  </CFe>') xml_val from dual
 12  )
 13  select xmlquery('/CFe/infCFe/@Id' passing xml_val returning content) as days
 14  from testTable;

DAYS
------------------------------------------------------------------------------------------------------------------------
CFe23220301438784004356592301302730184738086646



Reading TAGs from a XMLTYPE field

Francisco Moya, March 18, 2022 - 5:45 pm UTC

When I run your command in PL-SQL, it runs well, but the content that I need is not shown In the column Days, appears <XMLTYPE> instead of CFe23220301438784004356592301302730184738086646.
I have to click in the button "..." , and there I have the required content in the column Text…

This content (CFe23220301438784004356592301302730184738086646) I have to use to update other tables/fields in the Oracle DB.

My doubts:
1.) How can I show the content “CFe23220301438784004356592301302730184738086646” and be able to use it later?

2.) When I run the same command, but using my table, it doesn't show anything, and keeps running as if in a Loop. I have to force the cancellation of PL-SQL.

SELECT xmlquery('/CFe/infCFe/@Id' passing conteudo returning content) as days
FROM dbcsi_p2k.tmp_teste_xml
WHERE ROWID = 'AABV2iABBAAILUdAAB';


The table dbcsi_p2k.tmp_teste_xml has the following layout:
CONTEUDO XMLTYPE

I´m Using:
PL-SQL: 11.0.3.1770
ORACLE: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Thank you !!!

Connor McDonald
March 21, 2022 - 4:32 am UTC

Can we have a complete test case please

- create table
- insert data
- then your SQL

Datatype returned by XMLQUERY

Kim Berg Hansen, March 22, 2022 - 2:59 pm UTC

Francisco,

The XMLQUERY function returns XMLTYPE datatype, so your "PL/SQL Developer" GUI tool shows this in the grid rather than the text.

Perhaps use XMLCAST on the output of XMLQUERY to make it return a VARCHAR2?

Cheerio
/Kim

Connor McDonald
March 23, 2022 - 2:28 am UTC

nice catch
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.