Segue Correção, executar no Studio;


----------INICIO SCRIPT---------------



IF NOT EXISTS(SELECT 1 FROM sys.columns 

          WHERE Name = N'XMLBKP'

          AND Object_ID = Object_ID(N'dbo.NFce'))

BEGIN

ALTER TABLE NFCe ADD XMLBKP NVARCHAR(MAX);

END

GO

UPDATE NFCe SET XMLBKP = XML, XML = (REPLACE(XML, '<infProt>', '<infProt ' + REPLACE(REPLACE(SUBSTRING(XML, CHARINDEX('<nProt>', xml,0),30), '<nProt>','Id="NFe'), '</nProt>', '"') + '>')) WHERE XML LIKE '%nProt%'

GO

UPDATE NFCe SET XML = REPLACE(XML, 'procNFe', 'nfeProc')

GO


----------FIM SCRIPT--------------- 



Após realizar a correção baixar os xmls novamente.




-------------- CORREÇÃO DA CONSULTA---------------------------


SELECT '' as Serie,
   p.ide.value('cNF[1]', 'NVARCHAR(150)') AS cNF
   , p.ide.value('nNF[1]', 'NVARCHAR(150)') AS nNF
   , i.inf.value('@Id', 'nvarchar(max)') AS Chave
   , CAST(p.ide.value('dhEmi[1]', 'nvarchar(10)') AS Date) AS DataEmissao
   , d.det.value('*:cProd[1]', 'NVARCHAR(150)') AS cProd
   , d.det.value('*:xProd[1]', 'NVARCHAR(150)') AS xProd
   , d.det.value('*:qCom[1]', 'decimal(10,4)') AS Quantidade
   , d.det.value('*:vProd[1]', 'decimal(10,4)') AS ValorUnitario
   , d.det.value('*:vDesc[1]', 'decimal(10,4)') Desconto
   , d.det.value('*:vOutro[1]', 'decimal(10,4)') Acrescimo
   , d.det.value('*:vFrete[1]', 'decimal(10,4)') Frete
   , d.det.value('*:CFOP[1]', 'nvarchar(4)') AS CFOP
   , myXML
  FROM
 (SELECT NFCe.*, CAST(REPLACE(REPLACE([XML], 'xmlns="http://www.portalfiscal.inf.br/nfe"', ''),'', '') AS XML) as myXML
FROM NFCe
WHERE CONVERT(DATE,DataHoraEmissao)>=DATAINICIAL()
AND CONVERT(DATE,DataHoraEmissao)<=DATAFINAL()
) TbNFCes
 CROSS APPLY myXML.nodes('/nfeProc/NFe/infNFe') i(inf)
CROSS APPLY myXML.nodes('/nfeProc/NFe/infNFe/ide') p(ide)
CROSS APPLY myXML.nodes('/nfeProc/NFe/infNFe/*:det/prod') d(det)
ORDER BY Chave ASC



-------------------------------------------------------------------------------------------------