SELECT
CFOP,
SUM(VL_OPR) AS VL_OPR,
SUM(VL_PROD) AS V_PROD
FROM (
SELECT
i.ide.value('dEmi[1]', 'DATETIME') AS DT_DOC,
d.det.value('(./prod/CFOP)[1]', 'NVARCHAR(4)') AS CFOP,
d.det.value('(./prod/vItem)[1]', 'decimal(10,2)') AS VL_OPR,
d.det.value('(./prod/vProd)[1]', 'decimal(10,2)') AS VL_PROD,
Status
FROM
(SELECT POV_SATCFe.*, CAST([XML] AS XML) as myXML
--(SELECT POV_SATCFe.*, CAST(REPLACE(XML ,'UTF-8', 'UTF-16') AS XML) AS myXML
--(SELECT POV_SATCFe.*, CAST(REPLACE(CAST([XML] AS VARCHAR(MAX)), 'encoding="utf-16"', 'encoding="utf-8"') AS XML) AS myXML
FROM POV_SATCFe WHERE CreatedOn >= DATAINICIAL() AND CreatedOn <= DATAFINAL() AND Status = 'Autorizada') TbCFes
CROSS APPLY myXML.nodes('/CFe/infCFe/det') d(det)
CROSS APPLY myXML.nodes('/CFe/infCFe/ide') i(ide)
) tb2
GROUP BY CFOP
ORDER BY CFOP