Database Software

Hi everyone!

I've having some trouble with XML again,

I followed these steps:

Selecting multiple XML data

INSERT INTO PPAINICIALESTIMATIVARECEITA (INTCODIGO,DECVALORANO1,DECVALORANO2,DECVALORANO3,DECVALORANO4)
with t (xmlcnts) as (
select xml from XML_TAB)
select x.* from t, xmltable( xmlnamespaces ('http://www.tce.sp.gov.br/audesp/xml/auxiliar' AS "aux",   
                          'http://www.tce.sp.gov.br/audesp/xml/generico' AS "gen", 
                          'http://www.w3.org/2001/XMLSchema-instance' AS "xsi", 
                          DEFAULT 'http://www.tce.sp.gov.br/audesp/xml/planejamento'),
                          '/PPAInicial/PPAConteudo/EstimativaReceitasPPA'   PASSING t.xmlcnts  COLUMNS
           TIPODOC1 VARCHAR2 (50) PATH 'Receita/Codigo',
           v1 varchar2(50)  path 'ValorAno1',
           v2 varchar2(50)  path 'ValorAno2',
           v3 varchar2(50)  path 'ValorAno3',
           v4 varchar2(50)  path 'ValorAno4') x;

And it works, but the values i need to put in DECVALORANO columns it's a decimal number, but when i use decimal instead of varchar i get the error ORA-01722 invalid number.

The XML it's like below, how can i put the data in decimal format?

<EstimativaReceitasPPA>

     <Receita Tipo="DIRETA">

          <Codigo>17000000</Codigo>

     </Receita>

     <ReceitaDeducao>1</ReceitaDeducao>

     <ValorAno1>178941923.38</ValorAno1>

     <ValorAno2>187889019.50</ValorAno2>

     <ValorAno3>197283470.50</ValorAno3>

     <ValorAno4>207147644.00</ValorAno4>

</EstimativaReceitasPPA>