Ler XML dentro do XML

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
  

Mensagemem Ter, 25 Abr 2017 3:40 pm

Olá pessoal,
Estou comunicando com uma API que me retorno o XML dentro do XML.

Código: Selecionar todos
<RESPOSTA><REGISTROS><REGISTRO><CODIGO>17</CODIGO><CHAVE_ACESSO>31170410556925000177550030000000171000000172</CHAVE_ACESSO><XML><?xml version="1.0" encoding="UTF-8"?>

<nfeProc versao="3.10" xmlns="http://www.portalfiscal.inf.br/nfe"><NFe xmlns="http://www.portalfiscal.inf.br/nfe"><infNFe versao="3.10" Id="NFe31170410556925000177550030000000171000000172"><ide><cUF>31</cUF><cNF>00000017</cNF><natOp>Venda de produção própria, efetuada fora do estab.</natOp><indPag>1</indPag><mod>55</mod><serie>3</serie><nNF>17</nNF><dhEmi>2017-04-19T14:52:45-03:00</dhEmi><tpNF>1</tpNF><idDest>1</idDest><cMunFG>3148004</cMunFG><tpImp>1</tpImp><tpEmis>1</tpEmis><cDV>2</cDV><tpAmb>2</tpAmb><finNFe>1</finNFe><indFinal>0</indFinal><indPres>1</indPres><procEmi>0</procEmi><verProc>vê 4.38.1</verProc></ide><emit><CNPJ>10556925000177</CNPJ><xNome>OBERS LTDA - EPP</xNome><xFant>OBERS SISTEMAS</xFant><enderEmit><xLgr>R OLEGARIO MACIEL</xLgr><nro>203</nro><xBairro>CENTRO</xBairro><cMun>3148004</cMun><xMun>Patos de Minas</xMun><UF>MG</UF><CEP>38700122</CEP><cPais>1058</cPais><xPais>Brasil</xPais><fone>3438183800</fone></enderEmit><IE>0011044140070</IE><CRT>3</CRT></emit><dest><CNPJ>10835058000109</CNPJ><xNome>NF-E EMITIDA EM AMBIENTE DE HOMOLOGACAO - SEM VALOR FISCAL</xNome><enderDest><xLgr>R ZINHO BUENO</xLgr><nro>760</nro><xBairro>CENTRO</xBairro><cMun>3141207</cMun><xMun>Matutina</xMun><UF>MG</UF><CEP>38870000</CEP><cPais>1058</cPais><xPais>Brasil</xPais><fone>3436741048</fone></enderDest><indIEDest>1</indIEDest><IE>0011773850016</IE></dest><det nItem="1"><prod><cProd>2</cProd><cEAN/><xProd>Café Extra Forte 500g</xProd><NCM>09012100</NCM><CFOP>5103</CFOP><uCom>PC</uCom><qCom>12.0000</qCom><vUnCom>7.1500</vUnCom><vProd>85.80</vProd><cEANTrib/><uTrib>PC</uTrib><qTrib>12.0000</qTrib><vUnTrib>7.1500</vUnTrib><indTot>1</indTot></prod><imposto><vTotTrib>18.11</vTotTrib><ICMS><ICMS20><orig>0</orig><CST>20</CST><modBC>3</modBC><pRedBC>61.11</pRedBC><vBC>33.37</vBC><pICMS>18.00</pICMS><vICMS>6.01</vICMS></ICMS20></ICMS><IPI><cSelo>0.00000</cSelo><cEnq>999</cEnq><IPINT><CST>51</CST></IPINT></IPI><PIS><PISNT><CST>06</CST></PISNT></PIS><COFINS><COFINSNT><CST>06</CST></COFINSNT></COFINS></imposto></det><det nItem="2"><prod><cProd>3</cProd><cEAN/><xProd>Café Extra Forte 250g</xProd><NCM>09012100</NCM><CFOP>5103</CFOP><uCom>PC</uCom><qCom>20.0000</qCom><vUnCom>3.6000</vUnCom><vProd>72.00</vProd><cEANTrib/><uTrib>PC</uTrib><qTrib>20.0000</qTrib><vUnTrib>3.6000</vUnTrib><indTot>1</indTot></prod><imposto><vTotTrib>15.20</vTotTrib><ICMS><ICMS20><orig>0</orig><CST>20</CST><modBC>3</modBC><pRedBC>61.11</pRedBC><vBC>28.00</vBC><pICMS>18.00</pICMS><vICMS>5.04</vICMS></ICMS20></ICMS><IPI><cSelo>0.00000</cSelo><cEnq>999</cEnq><IPINT><CST>51</CST></IPINT></IPI><PIS><PISNT><CST>06</CST></PISNT></PIS><COFINS><COFINSNT><CST>06</CST></COFINSNT></COFINS></imposto></det><total><ICMSTot><vBC>61.37</vBC><vICMS>11.05</vICMS><vICMSDeson>0.00</vICMSDeson><vBCST>0.00</vBCST><vST>0.00</vST><vProd>157.80</vProd><vFrete>0.00</vFrete><vSeg>0.00</vSeg><vDesc>0.00</vDesc><vII>0.00</vII><vIPI>0.00</vIPI><vPIS>0.00</vPIS><vCOFINS>0.00</vCOFINS><vOutro>0.00</vOutro><vNF>157.80</vNF><vTotTrib>33.31</vTotTrib></ICMSTot></total><transp><modFrete>9</modFrete></transp><cobr><dup><nDup>1</nDup><dVenc>2017-05-19</dVenc><vDup>157.80</vDup></dup></cobr><infAdic><infCpl>Vendedor: LEANDRO;Número da Pronta entrega: 42;Condição de pagamento: 30 dias;Total dos tributos (lei 12.741/2012) - Federais: 33,31</infCpl></infAdic></infNFe><Signature xmlns="http://www.w3.org/2000/09/xmldsig#"><SignedInfo><CanonicalizationMethod Algorithm="http://www.w3.org/TR/2001/REC-xml-c14n-20010315"/><SignatureMethod Algorithm="http://www.w3.org/2000/09/xmldsig#rsa-sha1"/><Reference URI="#NFe31170410556925000177550030000000171000000172"><Transforms><Transform Algorithm="http://www.w3.org/2000/09/xmldsig#enveloped-signature"/><Transform Algorithm="http://www.w3.org/TR/2001/REC-xml-c14n-20010315"/></Transforms><DigestMethod Algorithm="http://www.w3.org/2000/09/xmldsig#sha1"/><DigestValue>jvHfRDAZb3LJw1mfLM57+hU6Tc0=</DigestValue></Reference></SignedInfo><SignatureValue>pNXlOA8ofZsb0EmfSGe1+vD98AQvN3Bl1l69qmgtPBNerHZdIciQeWr9WPu8KkCjKGA9uMhCFlWEDZrB75Cgd887P/YAVSDDbti2/XZ5fsIaO7Di6Jl/wcbHCdyEtOGm1qD/+L/f7jzzGrBPFc8Dx21LbD1siHg87b9MK7hvY/MainjMipry5T4ocONGnegR+U/NCdL771BHHZ9GHqi3XfBTf/TRKH9K+2iUwVn14HVSm0+FXFEywKkdxWGMm0D7shsbV678vpWagFP0Nj2IqvKP+e96JAA3PjhvwSjxpK1rB5w+LWb06krTe96j1tE1hgNTdt4fcaWJPiwNAhNV+Q==</SignatureValue><KeyInfo><X509Data><X509Certificate>MIIH5jCCBc6gAwIBAgIIbgpZgQMpIjUwDQYJKoZIhvcNAQELBQAwcDELMAkGA1UEBhMCQlIxEzARBgNVBAoTCklDUC1CcmFzaWwxNjA0BgNVBAsTLVNlY3JldGFyaWEgZGEgUmVjZWl0YSBGZWRlcmFsIGRvIEJyYXNpbCAtIFJGQjEUMBIGA1UEAxMLQUMgTElOSyBSRkIwHhcNMTcwNDA0MTg1MzE4WhcNMTgwNDA0MTg1MzE4WjCB1jELMAkGA1UEBhMCQlIxCzAJBgNVBAgTAk1HMRQwEgYDVQQHEwtTQU8gR09UQVJETzETMBEGA1UEChMKSUNQLUJyYXNpbDE2MDQGA1UECxMtU2VjcmV0YXJpYSBkYSBSZWNlaXRhIEZlZGVyYWwgZG8gQnJhc2lsIC0gUkZCMRYwFAYDVQQLEw1SRkIgZS1DTlBKIEExMRAwDgYDVQQLEwdBUiBMSU5LMS0wKwYDVQQDEyRDQUZFIFNBTyBHT1RBUkRPIExUREE6MTA1NTY5MjUwMDAxNzcwggEiMA0GCSqGSIb3DQEBAQUAA4IBDwAwggEKAoIBAQCtzsqs3xZhCWkogY/eKy+NskUc5YsqCiuCzQUjmGoJDAEh19eoIzI88xq/lXqvEigGqrCCwFKkAi4QjPmB8iW7T5Y4vCqV2VugN6mJb9ZTS9a6aBQi9XwV8EhtrZOg1IOlYOhfwjc4fouCxN86qz7Zqtn2vo8w1qAL5QiV9lthIJCi/MQVb7fiVYDqrf83vm2EQYfl8EQm8dKCkeIE4eIdKzG+JfgYiwkJTmwqQOW4RbpeHdnCWl1z9mpxNOB4lFbf5e5RoqX+J8gsWKmLhAr+DItNahYSp8D/bW3ik2WnhqyYIs7bBVM6OhjL3isfhM9MxKuFnlCvDXE+E0GnmACZAgMBAAGjggMbMIIDFzAfBgNVHSMEGDAWgBRZjSwlbOHzHl26JiFegQkoZYXVqDAOBgNVHQ8BAf8EBAMCBeAwbgYDVR0gBGcwZTBjBgZgTAECATswWTBXBggrBgEFBQcCARZLaHR0cDovL3JlcG9zaXRvcmlvLmxpbmtjZXJ0aWZpY2FjYW8uY29tLmJyL2FjLWxpbmtyZmIvYWMtbGluay1yZmItcGMtYTEucGRmMIH5BgNVHR8EgfEwge4wUKBOoEyGSmh0dHA6Ly9yZXBvc2l0b3Jpby5saW5rY2VydGlmaWNhY2FvLmNvbS5ici9hYy1saW5rcmZiL2xjci1hYy1saW5rcmZidjIuY3JsMFGgT6BNhktodHRwOi8vcmVwb3NpdG9yaW8yLmxpbmtjZXJ0aWZpY2FjYW8uY29tLmJyL2FjLWxpbmtyZmIvbGNyLWFjLWxpbmtyZmJ2Mi5jcmwwR6BFoEOGQWh0dHA6Ly9yZXBvc2l0b3Jpby5pY3BicmFzaWwuZ292LmJyL2xjci9saW5rL2xjci1hYy1saW5rcmZidjIuY3JsMIGVBggrBgEFBQcBAQSBiDCBhTBSBggrBgEFBQcwAoZGaHR0cDovL3JlcG9zaXRvcmlvLmxpbmtjZXJ0aWZpY2FjYW8uY29tLmJyL2FjLWxpbmtyZmIvYWMtbGlua3JmYnYyLnA3YjAvBggrBgEFBQcwAYYjaHR0cDovL29jc3AubGlua2NlcnRpZmljYWNhby5jb20uYnIwgbUGA1UdEQSBrTCBqoEgRklOQU5DRUlST0BDQUZFU0FPR09UQVJETy5DT00uQlKgGAYFYEwBAwKgDxMNVEFUSUFOQSBLSVJZVaAZBgVgTAEDA6AQEw4xMDU1NjkyNTAwMDE3N6A4BgVgTAEDBKAvEy0wNTAyMTk4MjAxNDQwNzA3NjY5MDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDCgFwYFYEwBAwegDhMMMDAwMDAwMDAwMDAwMB0GA1UdJQQWMBQGCCsGAQUFBwMCBggrBgEFBQcDBDAJBgNVHRMEAjAAMA0GCSqGSIb3DQEBCwUAA4ICAQAJqn4XeLFGghvDYvPCA3zvjR/YNZ2f5COvhYxGjOatE1hNXhBwKquFAHib1oLJ4nMZGiVlHskN+sG4xVTAWkO46rZqadJjJd1+BUPebRKVN3msgtaYqqhzn8EoN4QU5ZEDEWppvx6CErW3jJIXyIIwRLtxPEoCGaczAfMoqsuDUWyLQbny3DoQVYK7f0QWVymtJn8AT74fNeOxQc7/19roql6Z39N3EK6gJrJb/tZm4pWoDs8B1ThcGv6iQ5tRT5KBvwtUJciIYP2QvWQbNswzv04QiZMztIJ/yz4VJzkN829rKAYA2YNlrsU9ovjIAEq2PiIYBvAGPDpJaEsgn1k3roJdaVqvS1DyjsiFKFWq47/+YZsz5NNwHlrz1qz6/qTb3r8yZjD4z8RtZYtbb+BvcVGfcXKBVs0RRqHNVnVEdfFv7DBO0De9EZBNiiAKKNEsj+qJLvK51HcyeFS9nzBJsWSUz9TuXfeNQTGR+z23Mxzdd//g0dyevKwzGxim4c2n2DiuOs9iKxf1GvhNYOLvlf8Qw2U6+I9u7d9p6yAj3tMjjMQMDBnIzXRGfuz9qJ5HNSQYvdtChdvOvbnAjMsurtULiNN0+o2LzVtNCejaqgz+tk7DrxfWKU+skLK9jk8QniltwB32Eai8tdFwLTeSEoPv7uKRGGL3EtYx/Fz3gQ==</X509Certificate></X509Data></KeyInfo></Signature></NFe><protNFe versao="3.10"><infProt><tpAmb>2</tpAmb><verAplic>14.0.25</verAplic><chNFe>31170410556925000177550030000000171000000172</chNFe><dhRecbto>2017-04-19T14:50:14-03:00</dhRecbto><nProt>131170128665595</nProt><digVal>jvHfRDAZb3LJw1mfLM57+hU6Tc0=</digVal><cStat>100</cStat><xMotivo>Autorizado o uso da NF-e</xMotivo></infProt></protNFe></nfeProc></XML></REGISTRO><REGISTRO><CODIGO>18</CODIGO><CHAVE_ACESSO>31170410556925000177550030000000181000000188</CHAVE_ACESSO><XML><?xml version="1.0" encoding="UTF-8"?>

<nfeProc versao="3.10" xmlns="http://www.portalfiscal.inf.br/nfe"><NFe xmlns="http://www.portalfiscal.inf.br/nfe"><infNFe versao="3.10" Id="NFe31170410556925000177550030000000181000000188"><ide><cUF>31</cUF><cNF>00000018</cNF><natOp>Venda de produção própria, efetuada fora do estab.</natOp><indPag>1</indPag><mod>55</mod><serie>3</serie><nNF>18</nNF><dhEmi>2017-04-19T14:52:50-03:00</dhEmi><tpNF>1</tpNF><idDest>1</idDest><cMunFG>3148004</cMunFG><tpImp>1</tpImp><tpEmis>1</tpEmis><cDV>8</cDV><tpAmb>2</tpAmb><finNFe>1</finNFe><indFinal>0</indFinal><indPres>1</indPres><procEmi>0</procEmi><verProc>vê 4.38.1</verProc></ide><emit><CNPJ>10556925000177</CNPJ><xNome>OBERS LTDA - EPP</xNome><xFant>OBERS SISTEMAS</xFant><enderEmit><xLgr>R OLEGARIO MACIEL</xLgr><nro>203</nro><xBairro>CENTRO</xBairro><cMun>3148004</cMun><xMun>Patos de Minas</xMun><UF>MG</UF><CEP>38700122</CEP><cPais>1058</cPais><xPais>Brasil</xPais><fone>3438183800</fone></enderEmit><IE>0011044140070</IE><CRT>3</CRT></emit><dest><CNPJ>71393490000181</CNPJ><xNome>NF-E EMITIDA EM AMBIENTE DE HOMOLOGACAO - SEM VALOR FISCAL</xNome><enderDest><xLgr>R MAJOR OLIMPIO FRANCO</xLgr><nro>187</nro><xBairro>CENTRO</xBairro><cMun>3141207</cMun><xMun>Matutina</xMun><UF>MG</UF><CEP>38870000</CEP><cPais>1058</cPais><xPais>Brasil</xPais><fone>3436741194</fone></enderDest><indIEDest>1</indIEDest><IE>4128633270053</IE></dest><det nItem="1"><prod><cProd>3</cProd><cEAN/><xProd>Café Extra Forte 250g</xProd><NCM>09012100</NCM><CFOP>5103</CFOP><uCom>PC</uCom><qCom>12.0000</qCom><vUnCom>3.6300</vUnCom><vProd>43.56</vProd><cEANTrib/><uTrib>PC</uTrib><qTrib>12.0000</qTrib><vUnTrib>3.6300</vUnTrib><indTot>1</indTot></prod><imposto><vTotTrib>9.20</vTotTrib><ICMS><ICMS20><orig>0</orig><CST>20</CST><modBC>3</modBC><pRedBC>61.11</pRedBC><vBC>16.94</vBC><pICMS>18.00</pICMS><vICMS>3.05</vICMS></ICMS20></ICMS><IPI><cSelo>0.00000</cSelo><cEnq>999</cEnq><IPINT><CST>51</CST></IPINT></IPI><PIS><PISNT><CST>06</CST></PISNT></PIS><COFINS><COFINSNT><CST>06</CST></COFINSNT></COFINS></imposto></det><det nItem="2"><prod><cProd>2</cProd><cEAN/><xProd>Café Extra Forte 500g</xProd><NCM>09012100</NCM><CFOP>5103</CFOP><uCom>PC</uCom><qCom>4.0000</qCom><vUnCom>7.1500</vUnCom><vProd>28.60</vProd><cEANTrib/><uTrib>PC</uTrib><qTrib>4.0000</qTrib><vUnTrib>7.1500</vUnTrib><indTot>1</indTot></prod><imposto><vTotTrib>6.04</vTotTrib><ICMS><ICMS20><orig>0</orig><CST>20</CST><modBC>3</modBC><pRedBC>61.11</pRedBC><vBC>11.12</vBC><pICMS>18.00</pICMS><vICMS>2.00</vICMS></ICMS20></ICMS><IPI><cSelo>0.00000</cSelo><cEnq>999</cEnq><IPINT><CST>51</CST></IPINT></IPI><PIS><PISNT><CST>06</CST></PISNT></PIS><COFINS><COFINSNT><CST>06</CST></COFINSNT></COFINS></imposto></det><total><ICMSTot><vBC>28.06</vBC><vICMS>5.05</vICMS><vICMSDeson>0.00</vICMSDeson><vBCST>0.00</vBCST><vST>0.00</vST><vProd>72.16</vProd><vFrete>0.00</vFrete><vSeg>0.00</vSeg><vDesc>0.00</vDesc><vII>0.00</vII><vIPI>0.00</vIPI><vPIS>0.00</vPIS><vCOFINS>0.00</vCOFINS><vOutro>0.00</vOutro><vNF>72.16</vNF><vTotTrib>15.24</vTotTrib></ICMSTot></total><transp><modFrete>9</modFrete></transp><cobr><dup><nDup>1</nDup><dVenc>2017-05-19</dVenc><vDup>72.16</vDup></dup></cobr><infAdic><infCpl>Vendedor: Lucas;Número da Pronta entrega: 43;Condição de pagamento: 30 dias;Total dos tributos (lei 12.741/2012) - Federais: 15,24</infCpl></infAdic></infNFe><Signature xmlns="http://www.w3.org/2000/09/xmldsig#"><SignedInfo><CanonicalizationMethod Algorithm="http://www.w3.org/TR/2001/REC-xml-c14n-20010315"/><SignatureMethod Algorithm="http://www.w3.org/2000/09/xmldsig#rsa-sha1"/><Reference URI="#NFe31170410556925000177550030000000181000000188"><Transforms><Transform Algorithm="http://www.w3.org/2000/09/xmldsig#enveloped-signature"/><Transform Algorithm="http://www.w3.org/TR/2001/REC-xml-c14n-20010315"/></Transforms><DigestMethod Algorithm="http://www.w3.org/2000/09/xmldsig#sha1"/><DigestValue>lhjfW9aNGl8Op16WVNmwICRjvnU=</DigestValue></Reference></SignedInfo><SignatureValue>ThEj+6ixOu4xbvAq3LF0Rc1s/5zfgUJQ8hcUvfjnwoE02Wt5tCG/dYk5gjtPnNL59NDwPwlOTte6sXudDd2wHovofAhGvcbQ91dTlf+JPSCL0Ra9H5IeobjxAqqN2k2FS/iwTHiDITyDwES6QkJ+jQqZJQ/BUK5+cCTmUC2nehUZt1T2NC0E3d6+/jkozffN6hXLLtaGXY/vMjanWr7x6r6CJZcsXPxmeJ8nc2JJ9VZswWRU6B38TbXe4OSL9TNl/hR+nmWwuybC5Q4TuADE1HtFAvScSGG5sxbW826zpLT3JM2CzliduKnJA+/+hkNbi75SEpqOCpWnObjfWyAacw==</SignatureValue><KeyInfo><X509Data><X509Certificate>MIIH5jCCBc6gAwIBAgIIbgpZgQMpIjUwDQYJKoZIhvcNAQELBQAwcDELMAkGA1UEBhMCQlIxEzARBgNVBAoTCklDUC1CcmFzaWwxNjA0BgNVBAsTLVNlY3JldGFyaWEgZGEgUmVjZWl0YSBGZWRlcmFsIGRvIEJyYXNpbCAtIFJGQjEUMBIGA1UEAxMLQUMgTElOSyBSRkIwHhcNMTcwNDA0MTg1MzE4WhcNMTgwNDA0MTg1MzE4WjCB1jELMAkGA1UEBhMCQlIxCzAJBgNVBAgTAk1HMRQwEgYDVQQHEwtTQU8gR09UQVJETzETMBEGA1UEChMKSUNQLUJyYXNpbDE2MDQGA1UECxMtU2VjcmV0YXJpYSBkYSBSZWNlaXRhIEZlZGVyYWwgZG8gQnJhc2lsIC0gUkZCMRYwFAYDVQQLEw1SRkIgZS1DTlBKIEExMRAwDgYDVQQLEwdBUiBMSU5LMS0wKwYDVQQDEyRDQUZFIFNBTyBHT1RBUkRPIExUREE6MTA1NTY5MjUwMDAxNzcwggEiMA0GCSqGSIb3DQEBAQUAA4IBDwAwggEKAoIBAQCtzsqs3xZhCWkogY/eKy+NskUc5YsqCiuCzQUjmGoJDAEh19eoIzI88xq/lXqvEigGqrCCwFKkAi4QjPmB8iW7T5Y4vCqV2VugN6mJb9ZTS9a6aBQi9XwV8EhtrZOg1IOlYOhfwjc4fouCxN86qz7Zqtn2vo8w1qAL5QiV9lthIJCi/MQVb7fiVYDqrf83vm2EQYfl8EQm8dKCkeIE4eIdKzG+JfgYiwkJTmwqQOW4RbpeHdnCWl1z9mpxNOB4lFbf5e5RoqX+J8gsWKmLhAr+DItNahYSp8D/bW3ik2WnhqyYIs7bBVM6OhjL3isfhM9MxKuFnlCvDXE+E0GnmACZAgMBAAGjggMbMIIDFzAfBgNVHSMEGDAWgBRZjSwlbOHzHl26JiFegQkoZYXVqDAOBgNVHQ8BAf8EBAMCBeAwbgYDVR0gBGcwZTBjBgZgTAECATswWTBXBggrBgEFBQcCARZLaHR0cDovL3JlcG9zaXRvcmlvLmxpbmtjZXJ0aWZpY2FjYW8uY29tLmJyL2FjLWxpbmtyZmIvYWMtbGluay1yZmItcGMtYTEucGRmMIH5BgNVHR8EgfEwge4wUKBOoEyGSmh0dHA6Ly9yZXBvc2l0b3Jpby5saW5rY2VydGlmaWNhY2FvLmNvbS5ici9hYy1saW5rcmZiL2xjci1hYy1saW5rcmZidjIuY3JsMFGgT6BNhktodHRwOi8vcmVwb3NpdG9yaW8yLmxpbmtjZXJ0aWZpY2FjYW8uY29tLmJyL2FjLWxpbmtyZmIvbGNyLWFjLWxpbmtyZmJ2Mi5jcmwwR6BFoEOGQWh0dHA6Ly9yZXBvc2l0b3Jpby5pY3BicmFzaWwuZ292LmJyL2xjci9saW5rL2xjci1hYy1saW5rcmZidjIuY3JsMIGVBggrBgEFBQcBAQSBiDCBhTBSBggrBgEFBQcwAoZGaHR0cDovL3JlcG9zaXRvcmlvLmxpbmtjZXJ0aWZpY2FjYW8uY29tLmJyL2FjLWxpbmtyZmIvYWMtbGlua3JmYnYyLnA3YjAvBggrBgEFBQcwAYYjaHR0cDovL29jc3AubGlua2NlcnRpZmljYWNhby5jb20uYnIwgbUGA1UdEQSBrTCBqoEgRklOQU5DRUlST0BDQUZFU0FPR09UQVJETy5DT00uQlKgGAYFYEwBAwKgDxMNVEFUSUFOQSBLSVJZVaAZBgVgTAEDA6AQEw4xMDU1NjkyNTAwMDE3N6A4BgVgTAEDBKAvEy0wNTAyMTk4MjAxNDQwNzA3NjY5MDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDCgFwYFYEwBAwegDhMMMDAwMDAwMDAwMDAwMB0GA1UdJQQWMBQGCCsGAQUFBwMCBggrBgEFBQcDBDAJBgNVHRMEAjAAMA0GCSqGSIb3DQEBCwUAA4ICAQAJqn4XeLFGghvDYvPCA3zvjR/YNZ2f5COvhYxGjOatE1hNXhBwKquFAHib1oLJ4nMZGiVlHskN+sG4xVTAWkO46rZqadJjJd1+BUPebRKVN3msgtaYqqhzn8EoN4QU5ZEDEWppvx6CErW3jJIXyIIwRLtxPEoCGaczAfMoqsuDUWyLQbny3DoQVYK7f0QWVymtJn8AT74fNeOxQc7/19roql6Z39N3EK6gJrJb/tZm4pWoDs8B1ThcGv6iQ5tRT5KBvwtUJciIYP2QvWQbNswzv04QiZMztIJ/yz4VJzkN829rKAYA2YNlrsU9ovjIAEq2PiIYBvAGPDpJaEsgn1k3roJdaVqvS1DyjsiFKFWq47/+YZsz5NNwHlrz1qz6/qTb3r8yZjD4z8RtZYtbb+BvcVGfcXKBVs0RRqHNVnVEdfFv7DBO0De9EZBNiiAKKNEsj+qJLvK51HcyeFS9nzBJsWSUz9TuXfeNQTGR+z23Mxzdd//g0dyevKwzGxim4c2n2DiuOs9iKxf1GvhNYOLvlf8Qw2U6+I9u7d9p6yAj3tMjjMQMDBnIzXRGfuz9qJ5HNSQYvdtChdvOvbnAjMsurtULiNN0+o2LzVtNCejaqgz+tk7DrxfWKU+skLK9jk8QniltwB32Eai8tdFwLTeSEoPv7uKRGGL3EtYx/Fz3gQ==</X509Certificate></X509Data></KeyInfo></Signature></NFe><protNFe versao="3.10"><infProt><tpAmb>2</tpAmb><verAplic>14.0.25</verAplic><chNFe>31170410556925000177550030000000181000000188</chNFe><dhRecbto>2017-04-19T14:50:14-03:00</dhRecbto><nProt>131170128665596</nProt><digVal>lhjfW9aNGl8Op16WVNmwICRjvnU=</digVal><cStat>100</cStat><xMotivo>Autorizado o uso da NF-e</xMotivo></infProt></protNFe></nfeProc></XML></REGISTRO><REGISTRO/></REGISTROS></RESPOSTA>


O problema é que na documentação da API, o xml está do tipo string, e eu queria retornar em clob.

já tentei de várias formas, mas sem sucesso.
Ao chegar no "FOR", recebo a seguinte mensagem

ORA-01706: o valor do resultado da função do usuário era grande demais
ORA-06512: em "OBERS.PKG_INTEGRACAO_VENDASEXTERNAS", line 2588
ORA-06512: em line 6

Código: Selecionar todos
     -- GERA O XML DO RETORNO
    doc := xmltype.createxml(envelope);
   
    -- ABRE O CURSOR PARA VERIFICAR QUANTIDADE DE CODIGOS RETORNADOS
    declare cursor listar_nfe
      is
     
    -- EXTRAI O VALOR DA TAG
      select
            extractvalue(VALUE(TAB_ID),'/REGISTRO/CODIGO')           id_nota_fiscal,
            extractvalue(VALUE(TAB_ID),'/REGISTRO/CHAVE_ACESSO') x_chave,
            --xmltype.createxml(extractvalue(VALUE(TAB_ID),'/REGISTRO/XML'))           x_xml,
            --char_to_clob(extractvalue(VALUE(TAB_ID),'/REGISTRO/XML'))           x_xml
            --extractvalue(VALUE(TAB_ID).getclobval(),'/REGISTRO/CHAVE_ACESSO') x_xml
            --xmltype(envelope.xml).extract('//REGISTRO/XML/text()').getStringVal()
           
            EXTRACTVALUE(xmltype(doc), '/REGISTRO/XML')
         
           
            --extractvalue(value(tab_id), '/REGISTRO/XML/text()') x_xml
            --xmltype(t.xml).extract('//fax/text()').getStringVal() from mytab t
            --envelope.xmlval.extract('/REGISTRO/XML/text()').getClobVal()
            --SELECT REMOVE_ESPACOS_TAG(XCTECABECMSG.EXTRACT ('/*').GETCLOBVAL ()) INTO XCTECABECMSGCLOB FROM DUAL;
            --extractvalue(VALUE(TAB_ID),'/REGISTRO/XML') X_XML,
           
            --extractValue(doc,'/RESPOSTA/MESSAGE')                    ds_mensagem
        from
            TABLE(XMLSequence(extract(doc,'/RESPOSTA/REGISTROS/REGISTRO'))) TAB_ID;

    BEGIN
      FOR REC IN listar_nfe
        LOOP
          BEGIN
           
            arquivo := UTL_File.Fopen( 'DFE_DOWNLOAD', rec.x_chave||'-procNFe.xml' ,'w',32767);

             
            --texto := rec.x_xml.getclobval();
            texto := rec.x_xml;
           
       
            UTL_FILE.put_line ( arquivo, texto);
            UTL_File.Fclose(arquivo);
          END;
        END LOOP;
    END;
 
  END;
bessa_lucas

Lucas Bessa
Analista de Sistemas

lucas.bessa@obers.com.br
lucas.bessa.ti@gmail.com

Mensagemem Qua, 26 Abr 2017 6:17 pm

Eu procurei pelo erro ORA-01706 e eu achei a seguinte dica

https://forums.oracle.com/forums/messag ... 2#10736122


Para versão a partir de 11.2.0.2, pode usar
Código: Selecionar todos
SELECT x.*
FROM test_xml t
   , XMLTable(
       '/*/record'
       passing t.xml_data
       columns
         test_3  clob path 'test_3'
     ) x
;


Código: Selecionar todos
Mas para versão mais antiga, (10, por exemplo), aí se usa o getClobVal.
SELECT dbms_xmlgen.convert(x.test_3.getClobVal(), 1) as test_3
FROM test_xml t
   , XMLTable(
       '/*/record'
       passing t.xml_data
       columns
         test_3  xmltype path 'test_3/text()'
     ) x
;


Espero que isso ajude. Se conseguiu, manda a solução pra gente :-o
dr_gori
Localização: Seattle, WA, USA

Thomas F. G

Mensagemem Qua, 20 Set 2017 9:16 am

Bom dia pessoal,
estou com uma pequena dúvida.
Tenho um xml aqui, mas não estou conseguindo retornar o valor do ipi <vIPI>, alguém poderia me ajudar. Não sei se é porque o valor dele é 0.0, mas eu testei mudar para uma outra coluna por ex; '<CST>' que fica no mesmo nivel do nó.
Código: Selecionar todos
select Extractvalue(VALUE(xmlNFE),'/det/imposto/IPI/IPITtrib/vIPI','xmlns="http://www.portalfiscal.inf.br/nfe"') vIPI
from xml_nfe x,
             TABle(XMLSequence(extract(x.xml_xmltype,
                                       'nfeProc/NFe/infNFe/det',
                                       'xmlns="http://www.portalfiscal.inf.br/nfe"'))) xmlNFE



abaixo o xml

Código: Selecionar todos
<?xml version="1.0" encoding="UTF-8"?>
<nfeProc versao="3.10" xmlns="http://www.portalfiscal.inf.br/nfe">
<NFe xmlns="http://www.portalfiscal.inf.br/nfe">
<infNFe versao="3.10" Id="NFe35170304823980000183550010000349811009051927">
<ide>
<emit>
<dest>
<det nItem="1">
<prod>
<imposto>
<ICMS>
<IPI>
<cEnq>999</cEnq>
<IPITrib>
<CST>99</CST>
<vBC>0.00</vBC>
<pIPI>0.00</pIPI>
<vIPI>0.00</vIPI>
</IPITrib>
</IPI>
<PIS>
<COFINS>
</imposto>
</det>
<det nItem="2">
<det nItem="3">
<det nItem="4">
<det nItem="5">
<det nItem="6">
<det nItem="7">
<total>
<transp>
<cobr>
<infAdic>
<compra/>
</infNFe>
<Signature xmlns="http://www.w3.org/2000/09/xmldsig#">
</NFe>
<protNFe versao="3.10">
</nfeProc>
dcp

Mensagemem Qua, 20 Set 2017 9:19 am

Veja:
dcp escreveu:Bom dia pessoal,
estou com uma pequena dúvida.
Tenho um xml aqui, mas não estou conseguindo retornar o valor do ipi <vIPI>, alguém poderia me ajudar. Não sei se é porque o valor dele é 0.0, mas eu testei mudar para uma outra coluna por ex; '<CST>' que fica no mesmo nivel do nó e também não retornou nada.
Código: Selecionar todos
select Extractvalue(VALUE(xmlNFE),'/det/imposto/IPI/IPITtrib/vIPI','xmlns="http://www.portalfiscal.inf.br/nfe"') vIPI
from xml_nfe x,
             TABle(XMLSequence(extract(x.xml_xmltype,
                                       'nfeProc/NFe/infNFe/det',
                                       'xmlns="http://www.portalfiscal.inf.br/nfe"'))) xmlNFE



abaixo o xml

Código: Selecionar todos
<?xml version="1.0" encoding="UTF-8"?>
<nfeProc versao="3.10" xmlns="http://www.portalfiscal.inf.br/nfe">
<NFe xmlns="http://www.portalfiscal.inf.br/nfe">
<infNFe versao="3.10" Id="NFe35170304823980000183550010000349811009051927">
<ide>
<emit>
<dest>
<det nItem="1">
<prod>
<imposto>
<ICMS>
<IPI>
<cEnq>999</cEnq>
<IPITrib>
<CST>99</CST>
<vBC>0.00</vBC>
<pIPI>0.00</pIPI>
<vIPI>0.00</vIPI>
</IPITrib>
</IPI>
<PIS>
<COFINS>
</imposto>
</det>
<det nItem="2">
<det nItem="3">
<det nItem="4">
<det nItem="5">
<det nItem="6">
<det nItem="7">
<total>
<transp>
<cobr>
<infAdic>
<compra/>
</infNFe>
<Signature xmlns="http://www.w3.org/2000/09/xmldsig#">
</NFe>
<protNFe versao="3.10">
</nfeProc>
dcp

Mensagemem Qua, 20 Set 2017 3:07 pm

boa tarde dcp,

fiz um teste aqui.

Código: Selecionar todos
/det/imposto/IPI/IPITtrib/vIPI',


Acho que tem um t a mais por isso não retorna nada, é com ose não achasse o caminho.

Código: Selecionar todos
/det/imposto/IPI/IPITrib/vIPI',
spernega
Localização: São Paulo - SP

Mensagemem Sex, 22 Set 2017 11:23 am

Muito obrigado.

realmente um T a mais :roll:
abraços
dcp



Voltar para PL/SQL

Quem está online

Usuários navegando neste fórum: Nenhum usuário registrado e 4 visitantes