I am trying to use =IMPORTXML() in Google Spreadsheets, but I get "Imported content is empty." even with the right xpath_query argument. I've used a xPath Generator tool here.
I get this error here when using this tool:
ROOT CAUSE IS : java.lang.Exception: Error in input=org.xml.sax.SAXParseException; lineNumber: 1; columnNumber: 1; Content is not allowed in prolog. |
<div class="errorLabel">Failed With Following Error message:</div> <div class="bar error"> Error:Error in input=org.xml.sax.SAXParseException; lineNumber: 1; columnNumber: 1; Content is not allowed in prolog. || Description:java.lang.Exception: Error in input=org.xml.sax.SAXParseException; lineNumber: 1; columnNumber: 1; Content is not allowed in prolog. |*Please check input.</div>[Ljava.lang.StackTraceElement;@34325e99
I am not a developer but I have a little bit of knowledge in Appscript if this is the only solution. Could anyone help me? I would prefer to solve just with formulas, but maybe this is too much to ask given the condition of this XML file. This is a very important asset for a public hospital in Brazil with very scarce resources. Sorry for the XML indentation, it is a tricky one.
Formula added due to comment below: =IMPORTXML("https://www.dropbox.com/scl/fi/ozjw0tkjwais45f6do0ai/35240896382429000160550010004358881248052188.xml?rlkey=ddztt2vqxzr3ch1kwyk4enve5&st=k0ps6ehm&dl=1","/nfeProc/@versao")
This XML file does not appear to have any style information associated with it. The document tree is shown below.
<nfeProc xmlns="http://www.portalfiscal.inf.br/nfe" versao="4.00">
<protNFe>
<infProt>
<nProt>135241709295739</nProt>
<digVal>mm/WysVUfmCGPjR1DGK6vdKgefQ=</digVal>
<dhRecbto>2024-08-07T12:12:03-03:00</dhRecbto>
<Id>Id135241709295739</Id>
<chNFe>35240896382429000160550010004358881248052188</chNFe>
<xMotivo>Autorizado o uso da NF-e</xMotivo>
<cStat>100</cStat>
</infProt>
</protNFe>
<NFe>
<infNFe Id="NFe35240896382429000160550010004358881248052188">
<infAdic>
<infCpl>Pedido No: 291625 EMPENHO 2024NE04264 - PREGAO 10520/02 - PROCESSO SEI-260008/010121/2022 - BB Ag. 3336-7 CC. 2289-6 End.Entrega: BOULEVARD VINTE E OITO DE SETEMBRO, 77 - VILA ISABEL - RIO DE JANEIRO - RJ Cep:20551900 - Valor Aproximado do(s) Tributo(s): R$ 3314.30 (15.45%) Federal e R$ 3861.00 (18.00%) Estadual Fonte: IBPT .Valor do ICMS relativo ao Fundo de Combate a Pobreza - FCP da UF de destino: R$ 429.00. Valor do ICMS Interestadual para a UF de destino: R$ 1716.00. Valor do ICMS Interestadual para a UF do remetente: R$ 0.</infCpl>
<infAdFisco>Lei 10865 2004 art 28 XVI Ficam reduzidas a ZERO para LENTES INTRAOCULAR as aliquotas da contribuicao para o PIS e da COFINS incidentes sobre a receita bruta decorrente da venda. Retencao de IR aliquota de 1.2 por cento IN RFB 2145 de 26 de junho de 2023 alterando IN RFB 1234 de 11 de janeiro de 2012.</infAdFisco>
</infAdic>
<infRespTec>
<fone>1128593904</fone>
<CNPJ>53113791000122</CNPJ>
<xContato>Rodrigo de Almeida Sartorio</xContato>
<email>resp_tecnico_dfe_protheus@totvs.com.br</email>
</infRespTec>
<det>
<prod>
<cEAN>04547480451831</cEAN>
<cProd>B0321077116002I</cProd>
<qCom>1.0000</qCom>
<cEANTrib>04547480451831</cEANTrib>
<vUnTrib>195.00000000</vUnTrib>
<cBenef/>
<qTrib>1.0000</qTrib>
<vProd>195.00</vProd>
<xProd>ISERT 151 ESFERICA D 21.5</xProd>
<vUnCom>195.00000000</vUnCom>
<indTot>1</indTot>
<uTrib>UN</uTrib>
<NCM>90213920</NCM>
<uCom>UN</uCom>
<CFOP>6108</CFOP>
</prod>
<imposto>
<vTotTrib>65.23</vTotTrib>
<ICMS>
<ICMS00>
<modBC>3</modBC>
<orig>6</orig>
<CST>00</CST>
<vBC>195.00</vBC>
<vICMS>23.40</vICMS>
<pICMS>12.0000</pICMS>
</ICMS00>
</ICMS>
<IPI>
<IPINT>
<CST>51</CST>
</IPINT>
<cEnq>999</cEnq>
</IPI>
<ICMSUFDest>
<vBCFCPUFDest>195.00</vBCFCPUFDest>
<pICMSInter>12.00</pICMSInter>
<vICMSUFDest>15.60</vICMSUFDest>
<vICMSUFRemet>0</vICMSUFRemet>
<pICMSInterPart>100</pICMSInterPart>
<vFCPUFDest>3.90</vFCPUFDest>
<pFCPUFDest>2.0000</pFCPUFDest>
<vBCUFDest>195.00</vBCUFDest>
<pICMSUFDest>20.0000</pICMSUFDest>
</ICMSUFDest>
<COFINS>
<COFINSNT>
<CST>06</CST>
</COFINSNT>
</COFINS>
<PIS>
<PISNT>
<CST>06</CST>
</PISNT>
</PIS>
</imposto>
<infAdProd>Lote(s):TFP50GW9,</infAdProd>
</det>
<det>
<prod>
<cEAN>04547480451862</cEAN>
<cProd>B0321077116002L</cProd>
<qCom>1.0000</qCom>
<cEANTrib>04547480451862</cEANTrib>
<vUnTrib>195.00000000</vUnTrib>
<cBenef/>
<qTrib>1.0000</qTrib>
<vProd>195.00</vProd>
<xProd>ISERT 151 ESFERICA D 23.0</xProd>
<vUnCom>195.00000000</vUnCom>
<indTot>1</indTot>
<uTrib>UN</uTrib>
<NCM>90213920</NCM>
<uCom>UN</uCom>
<CFOP>6108</CFOP>
</prod>
<imposto>
<vTotTrib>65.23</vTotTrib>
<ICMS>
<ICMS00>
<modBC>3</modBC>
<orig>6</orig>
<CST>00</CST>
<vBC>195.00</vBC>
<vICMS>23.40</vICMS>
<pICMS>12.0000</pICMS>
</ICMS00>
</ICMS>
<IPI>
<IPINT>
<CST>51</CST>
</IPINT>
<cEnq>999</cEnq>
</IPI>
<ICMSUFDest>
<vBCFCPUFDest>195.00</vBCFCPUFDest>
<pICMSInter>12.00</pICMSInter>
<vICMSUFDest>15.60</vICMSUFDest>
<vICMSUFRemet>0</vICMSUFRemet>
<pICMSInterPart>100</pICMSInterPart>
<vFCPUFDest>3.90</vFCPUFDest>
<pFCPUFDest>2.0000</pFCPUFDest>
<vBCUFDest>195.00</vBCUFDest>
<pICMSUFDest>20.0000</pICMSUFDest>
</ICMSUFDest>
<COFINS>
<COFINSNT>
<CST>06</CST>
</COFINSNT>
</COFINS>
<PIS>
<PISNT>
<CST>06</CST>
</PISNT>
</PIS>
</imposto>
<infAdProd>Lote(s):TFP50L61,</infAdProd>
</det>
<det>
<nItem>107</nItem>
<prod>
<cEAN>04547480451862</cEAN>
<cProd>B0321077116002L</cProd>
<qCom>1.0000</qCom>
<cEANTrib>04547480451862</cEANTrib>
<vUnTrib>195.00000000</vUnTrib>
<cBenef/>
<qTrib>1.0000</qTrib>
<vProd>195.00</vProd>
<xProd>ISERT 151 ESFERICA D 23.0</xProd>
<vUnCom>195.00000000</vUnCom>
<indTot>1</indTot>
<uTrib>UN</uTrib>
<NCM>90213920</NCM>
<uCom>UN</uCom>
<CFOP>6108</CFOP>
</prod>
<imposto>
<vTotTrib>65.23</vTotTrib>
<ICMS>
<ICMS00>
<modBC>3</modBC>
<orig>6</orig>
<CST>00</CST>
<vBC>195.00</vBC>
<vICMS>23.40</vICMS>
<pICMS>12.0000</pICMS>
</ICMS00>
</ICMS>
<IPI>
<IPINT>
<CST>51</CST>
</IPINT>
<cEnq>999</cEnq>
</IPI>
<ICMSUFDest>
<vBCFCPUFDest>195.00</vBCFCPUFDest>
<pICMSInter>12.00</pICMSInter>
<vICMSUFDest>15.60</vICMSUFDest>
<vICMSUFRemet>0</vICMSUFRemet>
<pICMSInterPart>100</pICMSInterPart>
<vFCPUFDest>3.90</vFCPUFDest>
<pFCPUFDest>2.0000</pFCPUFDest>
<vBCUFDest>195.00</vBCUFDest>
<pICMSUFDest>20.0000</pICMSUFDest>
</ICMSUFDest>
<COFINS>
<COFINSNT>
<CST>06</CST>
</COFINSNT>
</COFINS>
<PIS>
<PISNT>
<CST>06</CST>
</PISNT>
</PIS>
</imposto>
<infAdProd>Lote(s):TFP50L62,</infAdProd>
</det>
<det>
<nItem>108</nItem>
<prod>
<cEAN>04547480451862</cEAN>
<cProd>B0321077116002L</cProd>
<qCom>1.0000</qCom>
<cEANTrib>04547480451862</cEANTrib>
<vUnTrib>195.00000000</vUnTrib>
<cBenef/>
<qTrib>1.0000</qTrib>
<vProd>195.00</vProd>
<xProd>ISERT 151 ESFERICA D 23.0</xProd>
<vUnCom>195.00000000</vUnCom>
<indTot>1</indTot>
<uTrib>UN</uTrib>
<NCM>90213920</NCM>
<uCom>UN</uCom>
<CFOP>6108</CFOP>
</prod>
<imposto>
<vTotTrib>65.23</vTotTrib>
<ICMS>
<ICMS00>
<modBC>3</modBC>
<orig>6</orig>
<CST>00</CST>
<vBC>195.00</vBC>
<vICMS>23.40</vICMS>
<pICMS>12.0000</pICMS>
</ICMS00>
</ICMS>
<IPI>
<IPINT>
<CST>51</CST>
</IPINT>
<cEnq>999</cEnq>
</IPI>
<ICMSUFDest>
<vBCFCPUFDest>195.00</vBCFCPUFDest>
<pICMSInter>12.00</pICMSInter>
<vICMSUFDest>15.60</vICMSUFDest>
<vICMSUFRemet>0</vICMSUFRemet>
<pICMSInterPart>100</pICMSInterPart>
<vFCPUFDest>3.90</vFCPUFDest>
<pFCPUFDest>2.0000</pFCPUFDest>
<vBCUFDest>195.00</vBCUFDest>
<pICMSUFDest>20.0000</pICMSUFDest>
</ICMSUFDest>
<COFINS>
<COFINSNT>
<CST>06</CST>
</COFINSNT>
</COFINS>
<PIS>
<PISNT>
<CST>06</CST>
</PISNT>
</PIS>
</imposto>
<infAdProd>Lote(s):TFP50L63,</infAdProd>
</det>
<det>
<nItem>109</nItem>
<prod>
<cEAN>04547480451862</cEAN>
<cProd>B0321077116002L</cProd>
<qCom>1.0000</qCom>
<cEANTrib>04547480451862</cEANTrib>
<vUnTrib>195.00000000</vUnTrib>
<cBenef/>
<qTrib>1.0000</qTrib>
<vProd>195.00</vProd>
<xProd>ISERT 151 ESFERICA D 23.0</xProd>
<vUnCom>195.00000000</vUnCom>
<indTot>1</indTot>
<uTrib>UN</uTrib>
<NCM>90213920</NCM>
<uCom>UN</uCom>
<CFOP>6108</CFOP>
</prod>
<imposto>
<vTotTrib>65.23</vTotTrib>
<ICMS>
<ICMS00>
<modBC>3</modBC>
<orig>6</orig>
<CST>00</CST>
<vBC>195.00</vBC>
<vICMS>23.40</vICMS>
<pICMS>12.0000</pICMS>
</ICMS00>
</ICMS>
<IPI>
<IPINT>
<CST>51</CST>
</IPINT>
<cEnq>999</cEnq>
</IPI>
<ICMSUFDest>
<vBCFCPUFDest>195.00</vBCFCPUFDest>
<pICMSInter>12.00</pICMSInter>
<vICMSUFDest>15.60</vICMSUFDest>
<vICMSUFRemet>0</vICMSUFRemet>
<pICMSInterPart>100</pICMSInterPart>
<vFCPUFDest>3.90</vFCPUFDest>
<pFCPUFDest>2.0000</pFCPUFDest>
<vBCUFDest>195.00</vBCUFDest>
<pICMSUFDest>20.0000</pICMSUFDest>
</ICMSUFDest>
<COFINS>
<COFINSNT>
<CST>06</CST>
</COFINSNT>
</COFINS>
<PIS>
<PISNT>
<CST>06</CST>
</PISNT>
</PIS>
</imposto>
<infAdProd>Lote(s):TFP50L64,</infAdProd>
</det>
<det>
<nItem>110</nItem>
<prod>
<cEAN>04547480451862</cEAN>
<cProd>B0321077116002L</cProd>
<qCom>1.0000</qCom>
<cEANTrib>04547480451862</cEANTrib>
<vUnTrib>195.00000000</vUnTrib>
<cBenef/>
<qTrib>1.0000</qTrib>
<vProd>195.00</vProd>
<xProd>ISERT 151 ESFERICA D 23.0</xProd>
<vUnCom>195.00000000</vUnCom>
<indTot>1</indTot>
<uTrib>UN</uTrib>
<NCM>90213920</NCM>
<uCom>UN</uCom>
<CFOP>6108</CFOP>
</prod>
<imposto>
<vTotTrib>65.23</vTotTrib>
<ICMS>
<ICMS00>
<modBC>3</modBC>
<orig>6</orig>
<CST>00</CST>
<vBC>195.00</vBC>
<vICMS>23.40</vICMS>
<pICMS>12.0000</pICMS>
</ICMS00>
</ICMS>
<IPI>
<IPINT>
<CST>51</CST>
</IPINT>
<cEnq>999</cEnq>
</IPI>
<ICMSUFDest>
<vBCFCPUFDest>195.00</vBCFCPUFDest>
<pICMSInter>12.00</pICMSInter>
<vICMSUFDest>15.60</vICMSUFDest>
<vICMSUFRemet>0</vICMSUFRemet>
<pICMSInterPart>100</pICMSInterPart>
<vFCPUFDest>3.90</vFCPUFDest>
<pFCPUFDest>2.0000</pFCPUFDest>
<vBCUFDest>195.00</vBCUFDest>
<pICMSUFDest>20.0000</pICMSUFDest>
</ICMSUFDest>
<COFINS>
<COFINSNT>
<CST>06</CST>
</COFINSNT>
</COFINS>
<PIS>
<PISNT>
<CST>06</CST>
</PISNT>
</PIS>
</imposto>
<infAdProd>Lote(s):TFP50L65,</infAdProd>
</det>
<total>
<retTrib>
<vIRRF>257.40</vIRRF>
<vBCIRRF>21450.00</vBCIRRF>
</retTrib>
<ICMSTot>
<vICMSUFDest>1716.00</vICMSUFDest>
<vICMSUFRemet>0</vICMSUFRemet>
<vCOFINS>0</vCOFINS>
<vBCST>0</vBCST>
<vICMSDeson>0</vICMSDeson>
<vFCPUFDest>429.00</vFCPUFDest>
<vProd>21450.00</vProd>
<vSeg>0</vSeg>
<vFCP>0</vFCP>
<vFCPST>0</vFCPST>
<vNF>21450.00</vNF>
<vTotTrib>7175.30</vTotTrib>
<vPIS>0</vPIS>
<vIPIDevol>0</vIPIDevol>
<vBC>21450.00</vBC>
<vST>0</vST>
<vICMS>2574.00</vICMS>
<vII>0</vII>
<vFCPSTRet>0</vFCPSTRet>
<vDesc>0</vDesc>
<vOutro>0</vOutro>
<vIPI>0</vIPI>
<vFrete>0</vFrete>
</ICMSTot>
</total>
<cobr>
<fat>
<vOrig>21192.60</vOrig>
<nFat>1000435888</nFat>
<vDesc>0</vDesc>
<vLiq>21192.60</vLiq>
</fat>
<dup>
<dVenc>2024-09-06</dVenc>
<nDup>001</nDup>
<vDup>21192.60</vDup>
</dup>
</cobr>
<pag>
<detPag>
<vPag>21450.00</vPag>
<tPag>01</tPag>
<indPag>1</indPag>
</detPag>
</pag>
<entrega>
<cPais>1058</cPais>
<xLgr>BOULEVARD VINTE E OITO DE SETEMBRO</xLgr>
<nro>77</nro>
<cMun>3304557</cMun>
<xBairro>VILA ISABEL</xBairro>
<CEP>20551900</CEP>
<fone>2123342240</fone>
<xNome>UNIVERSIDADE DO ESTADO DO RIO DE JANEIRO</xNome>
<UF>RJ</UF>
<xPais>BRASIL</xPais>
<xMun>RIO DE JANEIRO</xMun>
<CNPJ>33540014000157</CNPJ>
<email>COMPRASPPCUERJ@YAHOO.COM.BR</email>
</entrega>
<ide>
<tpNF>1</tpNF>
<mod>55</mod>
<indPres>9</indPres>
<tpImp>1</tpImp>
<nNF>435888</nNF>
<cMunFG>3505708</cMunFG>
<procEmi>0</procEmi>
<finNFe>1</finNFe>
<dhEmi>2024-08-07T11:08:00-03:00</dhEmi>
<tpAmb>1</tpAmb>
<indFinal>1</indFinal>
<dhSaiEnt>2024-08-07T11:08:00-03:00</dhSaiEnt>
<idDest>2</idDest>
<tpEmis>1</tpEmis>
<cDV>8</cDV>
<cUF>35</cUF>
<serie>1</serie>
<natOp>VENDA DE MERCADORIA ADQUIRIDA OU RECEBIDA DE TERCEIROS, DEST</natOp>
<cNF>24805218</cNF>
<verProc>12.1.2310 | 3.0</verProc>
<indIntermed>0</indIntermed>
</ide>
<Id>NFe35240896382429000160550010004358881248052188</Id>
<dest>
<xNome>UNIVERSIDADE DO ESTADO DO RIO DE JANEIRO</xNome>
<CNPJ>33540014000157</CNPJ>
<enderDest>
<fone>02123342240</fone>
<UF>RJ</UF>
<xPais>BRASIL</xPais>
<cPais>1058</cPais>
<xLgr>R SAO FRANCISCO XAVIER</xLgr>
<xMun>RIO DE JANEIRO</xMun>
<nro>524</nro>
<cMun>3304557</cMun>
<xBairro>MARACANA</xBairro>
<CEP>20550013</CEP>
</enderDest>
<IE>76003939</IE>
<indIEDest>9</indIEDest>
<email>COMPRASPPCUERJ@YAHOO.COM.BR</email>
</dest>
<emit>
<CNAE>4645101</CNAE>
<xNome>ADAPT PRODUTOS OFTALMOLOGICOS LTDA</xNome>
<IM>5AP3754</IM>
<CRT>3</CRT>
<xFant>ADAPT-FIL/BARUERI</xFant>
<CNPJ>96382429000160</CNPJ>
<enderEmit>
<xCpl>GALPAO 5</xCpl>
<fone>1150991900</fone>
<UF>SP</UF>
<xPais>BRASIL</xPais>
<cPais>1058</cPais>
<xLgr>RUA VICTORINO</xLgr>
<xMun>BARUERI</xMun>
<nro>207</nro>
<cMun>3505708</cMun>
<xBairro>JARDIM MUTINGA</xBairro>
<CEP>06463290</CEP>
</enderEmit>
<IE>206810441111</IE>
<IEST>92015840</IEST>
</emit>
<transp>
<modFrete>0</modFrete>
<vol>
<pesoL>7.700</pesoL>
<esp>CAIXA</esp>
<qVol>1</qVol>
<pesoB>8.536</pesoB>
</vol>
<transporta>
<xNome>CORREIOS SEDEX COMUM</xNome>
<UF>SP</UF>
<xEnder>RUA GUARARAPES, 1832</xEnder>
<xMun>SAO PAULO</xMun>
<CNPJ>34028316000103</CNPJ>
<IE>ISENTO</IE>
</transporta>
</transp>
</infNFe>
</NFe>
<versao>4.00</versao>
As an alternative to XML parsing, you can use "regex" within the function below and use it on the sheet like a normal formula such as =getData()
function getData() {
const url = 'https://www.dropbox.com/scl/fi/ozjw0tkjwais45f6do0ai/35240896382429000160550010004358881248052188.xml?rlkey=ddztt2vqxzr3ch1kwyk4enve5&st=k0ps6ehm&dl=1';
const response = UrlFetchApp.fetch(url);
const content = response.getContentText();
const results = content.match(/versao=(".+?")/g);
return results[0];
}
You may use the below function as well;
function getVersion() {
const url = 'https://www.dropbox.com/scl/fi/ozjw0tkjwais45f6do0ai/35240896382429000160550010004358881248052188.xml?rlkey=ddztt2vqxzr3ch1kwyk4enve5&st=k0ps6ehm&dl=1';
const response = UrlFetchApp.fetch(url);
const content = response.getContentText();
var regExp = new RegExp('(versao=")(.+?)"',"g");
var version = parseFloat(regExp.exec(content)[2]);
Logger.log(version);
return version;
}