XML with nodes

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