Hello everyone,
The following query gives error in the number 27953,42 which is how it arrives in the XML, if I change the comma to a point 27953.42 it is executed correctly, the question is how do I make the change of the decimal characters in the sql.
WITH sii_dte_email_upload(RutEmisor, RutReceptor,correl, EnvioDTE_XML) AS
(SELECT 1,1001,850
, XMLTYPE(
'<?xml version="1.0" encoding="UTF-8"?>
<EnvioDTE xmlns="http://www.sii.cl/SiiDte" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.sii.cl/SiiDte EnvioDTE_v10.xsd">
<SetDTE ID="SetDoc">
<Caratula>
<RutEmisor>79760110-1</RutEmisor>
<RutEnvia>6424091-9</RutEnvia>
<RutReceptor>97004000-5</RutReceptor>
<FchResol>2014-08-20</FchResol>
<NroResol>79</NroResol>
<TmstFirmaEnv>2019-07-25T17:47:44.000000</TmstFirmaEnv>
<SubTotDTE>
<TpoDTE>34</TpoDTE>
<NroDTE>1</NroDTE>
</SubTotDTE>
<SubTotDTE>
<TpoDTE>33</TpoDTE>
<NroDTE>2</NroDTE>
</SubTotDTE>
</Caratula>
<DTE>
<Documento ID="F6766T34">
<Detalle>
<NroLinDet>1</NroLinDet>
<IndExe>1</IndExe>
<NmbItem>SERVICIO SOFTWARE ASISTENCIA JULIO 2019</NmbItem>
<QtyItem>23</QtyItem>
<UnmdItem>UF</UnmdItem>
<PrcItem>27953,42</PrcItem>
<MontoItem>642929</MontoItem>
</Detalle>
</Documento>
</DTE>
</SetDTE>
</EnvioDTE>')
FROM dual)
SELECT *
FROM xmltable(XMLNAMESPACES(DEFAULT 'http://www.sii.cl/SiiDte'), '/EnvioDTE/SetDTE/DTE/Documento/Detalle'
passing( select EnvioDTE_XML
from SII_DTE_EMAIL_UPLOAD
where correl = 850 )
columns NroLinDet VARCHAR2(10) path 'NroLinDet'
, NmbItem VARCHAR2(90) path 'NmbItem'
, QtyItem VARCHAR2(22) path 'QtyItem'
, UnmdItem VARCHAR2(22) path 'UnmdItem'
, PrcItem number path 'PrcItem'
, MontoItem number path 'MontoItem'
);
thank you very much
Carlos