XML with nodes - Mailing list pgsql-sql
| From | Leandro Carnio |
|---|---|
| Subject | XML with nodes |
| Date | |
| Msg-id | CALRrN7uysGoEVrfkshTgBazsWzWL4qMeBgzFWgpwiWHb4V8JEw@mail.gmail.com Whole thread Raw |
| List | pgsql-sql |
Hello, good morning. How are you?
I have the following scenario, in the select below, I need to return cod product, descrproduct and value but I am not getting it, can anyone help me with this?
Today we have clients with SQL Server (Express) and those with larger databases use PostgreSQL, the entire routine of importing NFs into SQL was done inside the Bank in a procedure, and today a customer with 20,000 notes can import everything in less of 2 minutes, and those who use PostgreSQL, because we can not develop everything in the Bank, with that same amount of notes, it takes about 9 hours.
WITH xmldata(data) AS (VALUES ('
<nfeProc versao="2.00" xmlns="http://www.portalfiscal.inf.br/nfe">
<Root>
<det nItem="1">
<prod>
<cProd>55783</cProd>
<cEAN />
<xProd>SMARTPHONE SAMSUNG GALAXY POCKET 2 DUOS SM-G110B - PRETO, ANDROID 4.4, PROCESSADOR 1GHZ, TELA 3.3, 4GB, CAME</xProd>
<NCM>85171231</NCM>
<CFOP>5405</CFOP>
<uCom>UN</uCom>
<qCom>1.0000</qCom>
<vUnCom>268.1200000000</vUnCom>
<vProd>268.12</vProd>
<cEANTrib />
<uTrib>UN</uTrib>
<qTrib>1.0000</qTrib>
<vUnTrib>268.1200000000</vUnTrib>
<vFrete>7.84</vFrete>
<indTot>1</indTot>
</prod>
<imposto>
<ICMS>
<ICMS60>
<orig>0</orig>
<CST>60</CST>
<vBCSTRet>0.00</vBCSTRet>
<vICMSSTRet>0.00</vICMSSTRet>
</ICMS60>
</ICMS>
</imposto>
<infAdProd>GARANTIA 12 MESES - N.SERIE OU IMEI 354490061627365, 354490061627381 - IMPOSTO RECOLHIDO EM SUBSTITUICAO TRIBUTARIA - ARTIGO 313-Z19 DO RICMS.</infAdProd>
</det>
<det nItem="2">
<prod>
<cProd>41384</cProd>
<cEAN />
<xProd>PHILIPS FONE DE OUVIDO DJ SHL3000WT00 BRANCO</xProd>
<NCM>85183000</NCM>
<CFOP>5405</CFOP>
<uCom>UN</uCom>
<qCom>1.0000</qCom>
<vUnCom>52.1200000000</vUnCom>
<vProd>52.12</vProd>
<cEANTrib />
<uTrib>UN</uTrib>
<qTrib>1.0000</qTrib>
<vUnTrib>52.1200000000</vUnTrib>
<indTot>1</indTot>
</prod>
<imposto>
<ICMS>
<ICMS60>
<orig>0</orig>
<CST>60</CST>
<vBCSTRet>0.00</vBCSTRet>
<vICMSSTRet>0.00</vICMSSTRet>
</ICMS60>
</ICMS>
</imposto>
<infAdProd>GARANTIA 12 MESES - N.SERIE OU IMEI 354490061627365, 354490061627381 - IMPOSTO RECOLHIDO EM SUBSTITUICAO TRIBUTARIA - ARTIGO 313-Z19 DO RICMS.</infAdProd>
</det>
</Root>
</nfeProc>'::xml)
)
select id as Teste, "prod/cProd" as CodProd, "prod/xProd" as DescrProduto,
cast("prod/vProd" as decimal(10,2)) as Valor,
cast(coalesce(("prod/vFrete"),'0') as decimal(10,2)) as Frete
from
(
SELECT xmltable.*
FROM xmldata,
XMLTABLE(XMLNAMESPACES('http://www.portalfiscal.inf.br/nfe' AS x),
'/x:nfeProc/x:Root/x:det'
-- '//Root/det'
PASSING data
COLUMNS id int PATH '@nItem',
Linha FOR ORDINALITY,
"prod/cProd" text,
"prod/xProd" text,
"prod/vProd" text,
"prod/vFrete" text) ) A