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
-------------------------------------------------------------------------------------------------