Read Tag De Nitem

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Post Reply
claudioreiis01
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Posts: 14
Joined: Tue, 25 Aug 2015 1:31 pm

Good morning guys,
I would like to know if you have any way to read the tag det nitem
because so I can see how many items has a product because it has 2 products for example this tag is

Select all

 
<det nItem = "1"> 
   <prod> 
        <cprod>produto 1</cprod> 
   </prod> 
  
<det nItem = "2"> 
   <prod> 
        <cprod>produto 2</cprod> 
   </prod> 
</det>
I imagined that if I can read the nitem and the number I can make a counter to read how many items I have and load a table, my XML is already in a table in a XmlType field.
Top
saziba
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 3
Joined: Sun, 22 Feb 2015 11:04 am

Hi, Claudio.

Sample XML seems to me to be wrong.
In any case follow some tips with XML extraction. I hope they are helpful.

1- You do not need to know the quantity repeated elements to iterate between them. Using XMLTable syntax You can make a SELECT in XML quite simply as if it were a table

Select all

SELECT * 
FROM   Xmltable('det/prod' PASSING XMLTYPE('<det nItem = "4"> 
   <prod> 
        <cprod>produto 1</cprod> 
   </prod> 
   <prod> 
        <cprod>produto 2</cprod> 
   </prod> 
   <prod> 
        <cprod>produto 3</cprod> 
   </prod> 
   <prod> 
        <cprod>produto 4</cprod> 
   </prod> 
</det>') COLUMNS cprod VARCHAR2(4000) PATH 'cprod') 
;
2- You really need to read an attribute of a tag, your query can be like this:

Select all

 
SELECT nitem 
FROM   xmltable('det' passing xmltype(' 
<det nItem = "4"> 
   <prod> 
        <cprod>produto 1</cprod> 
   </prod> 
   <prod> 
        <cprod>produto 2</cprod> 
   </prod> 
   <prod> 
        <cprod>produto 3</cprod> 
   </prod> 
   <prod> 
        <cprod>produto 4</cprod> 
   </prod> 
</det>') columns nItem VARCHAR2(4000) path '@nItem');
3- If XML has tags with values ??at different levels (parent and Son) You should use an XMLTable for each level.
To perform the junction between them use the XML itself extracted from the highest level:

Select all

SELECT nitem, cprod 
FROM   xmltable('det' passing xmltype(' 
<det nItem = "4"> 
   <prod> 
        <cprod>produto 1</cprod> 
   </prod> 
   <prod> 
        <cprod>produto 2</cprod> 
   </prod> 
   <prod> 
        <cprod>produto 3</cprod> 
   </prod> 
   <prod> 
        <cprod>produto 4</cprod> 
   </prod> 
</det>') columns nItem VARCHAR2(4000) path '@nItem' 
                ,produtos XMLTYPE path '/') det, 
Xmltable('det/prod' PASSING det.produtos  COLUMNS cprod VARCHAR2(4000) PATH 'cprod')

I hope it helps!
Top
claudioreiis01
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Posts: 14
Joined: Tue, 25 Aug 2015 1:31 pm

@saziba
The XML I'm trying to read is the following:

Select all

 <?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 Id="NFe35150809005784000115550010000002301900200512" versao="3.10"> 
- <ide> 
  <cUF>35</cUF>  
  <cNF>90020051</cNF>  
  <natOp>VENDA DE PROD IND</natOp>  
  <indPag>0</indPag>  
  <mod>55</mod>  
  <serie>1</serie>  
  <nNF>230</nNF>  
  <dhEmi>2015-08-24T17:27:00-03:00</dhEmi>  
  <dhSaiEnt>2015-08-25T00:00:00-03:00</dhSaiEnt>  
  <tpNF>1</tpNF>  
  <idDest>1</idDest>  
  <cMunFG>3550308</cMunFG>  
  <tpImp>1</tpImp>  
  <tpEmis>1</tpEmis>  
  <cDV>2</cDV>  
  <tpAmb>1</tpAmb>  
  <finNFe>1</finNFe>  
  <indFinal>0</indFinal>  
  <indPres>0</indPres>  
  <procEmi>3</procEmi>  
  <verProc>3.10.61</verProc>  
  </ide> 
- <emit> 
  <CNPJ>09005784000115</CNPJ>  
  <xNome>KISTON RESTAURANTES LTDA</xNome>  
  <xFant>RÁSCAL</xFant>  
- <enderEmit> 
  <xLgr>RUA LEOPOLDO COUTO MAGALHÃES JR</xLgr>  
  <nro>831</nro>  
  <xBairro>ITAIM BIBI</xBairro>  
  <cMun>3550308</cMun>  
  <xMun>são Paulo</xMun>  
  <UF>SP</UF>  
  <CEP>04542011</CEP>  
  <cPais>1058</cPais>  
  <xPais>BRASIL</xPais>  
  </enderEmit> 
  <IE>149774516114</IE>  
  <CRT>3</CRT>  
  </emit> 
- <dest> 
  <CNPJ>19693295000157</CNPJ>  
  <xNome>DELPHINUS RESTAURANTES LTDA</xNome>  
- <enderDest> 
  <xLgr>AV DAS NAÇÕES UNIDAS</xLgr>  
  <nro>4777</nro>  
  <xCpl>LOJA SVL00131</xCpl>  
  <xBairro>JARDIM UNIVERSIDADE</xBairro>  
  <cMun>3550308</cMun>  
  <xMun>são Paulo</xMun>  
  <UF>SP</UF>  
  <CEP>05477000</CEP>  
  <cPais>1058</cPais>  
  <xPais>BRASIL</xPais>  
  </enderDest> 
  <indIEDest>1</indIEDest>  
  <IE>143229696116</IE>  
  </dest> 
- <det nItem="1"> 
- <prod> 
  <cProd>VND00002623</cProd>  
  <cEAN />  
  <xProd>PAO ITALIANO P - UN</xProd>  
  <NCM>19059090</NCM>  
  <CFOP>5101</CFOP>  
  <uCom>UN</uCom>  
  <qCom>60.0000</qCom>  
  <vUnCom>1.6000000000</vUnCom>  
  <vProd>96.00</vProd>  
  <cEANTrib />  
  <uTrib>UN</uTrib>  
  <qTrib>60.0000</qTrib>  
  <vUnTrib>1.6000000000</vUnTrib>  
  <indTot>1</indTot>  
  </prod> 
- <imposto> 
- <ICMS> 
- <ICMS20> 
  <orig>0</orig>  
  <CST>20</CST>  
  <modBC>3</modBC>  
  <pRedBC>41.6700</pRedBC>  
  <vBC>56.00</vBC>  
  <pICMS>12.0000</pICMS>  
  <vICMS>6.72</vICMS>  
  </ICMS20> 
  </ICMS> 
- <PIS> 
- <PISNT> 
  <CST>07</CST>  
  </PISNT> 
  </PIS> 
- <COFINS> 
- <COFINSNT> 
  <CST>07</CST>  
  </COFINSNT> 
  </COFINS> 
  </imposto> 
  </det> 
- <det nItem="2"> 
- <prod> 
  <cProd>EST1008020045</cProd>  
  <cEAN />  
  <xProd>PAO CIABATTA - PCT 7 UN</xProd>  
  <NCM>19059090</NCM>  
  <CFOP>5101</CFOP>  
  <uCom>PCT</uCom>  
  <qCom>2.0000</qCom>  
  <vUnCom>11.2000000000</vUnCom>  
  <vProd>22.40</vProd>  
  <cEANTrib />  
  <uTrib>PCT</uTrib>  
  <qTrib>2.0000</qTrib>  
  <vUnTrib>11.2000000000</vUnTrib>  
  <indTot>1</indTot>  
  </prod> 
- <imposto> 
- <ICMS> 
- <ICMS20> 
  <orig>0</orig>  
  <CST>20</CST>  
  <modBC>3</modBC>  
  <pRedBC>41.6700</pRedBC>  
  <vBC>13.07</vBC>  
  <pICMS>12.0000</pICMS>  
  <vICMS>1.57</vICMS>  
  </ICMS20> 
  </ICMS> 
- <PIS> 
- <PISNT> 
  <CST>07</CST>  
  </PISNT> 
  </PIS> 
- <COFINS> 
- <COFINSNT> 
  <CST>07</CST>  
  </COFINSNT> 
  </COFINS> 
  </imposto> 
  </det> 
- <total> 
- <ICMSTot> 
  <vBC>69.07</vBC>  
  <vICMS>8.29</vICMS>  
  <vICMSDeson>0.00</vICMSDeson>  
  <vBCST>0.00</vBCST>  
  <vST>0.00</vST>  
  <vProd>118.40</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>118.40</vNF>  
  <vTotTrib>0.00</vTotTrib>  
  </ICMSTot> 
  </total> 
- <transp> 
  <modFrete>0</modFrete>  
- <transporta> 
  <CNPJ>09005784000115</CNPJ>  
  <xNome>KISTON RESTAURANTES LTDA</xNome>  
  <IE>149774516114</IE>  
  <xEnder>RUA LEOPOLDO COUTO MAGALHÃES</xEnder>  
  <xMun>são Paulo</xMun>  
  <UF>SP</UF>  
  </transporta> 
  </transp> 
- <infAdic> 
  <infCpl>REDUÇÃO DA CARGA TRIBUTARIA EM 41,67% CONFORME DECRETO 50.071/05 ART. 1°, ITEM XIV</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="#NFe35150809005784000115550010000002301900200512"> 
- <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>1KS3ACeA4hqLZmb3zpBeB2pJPMI=</DigestValue>  
  </Reference> 
  </SignedInfo> 
  <SignatureValue>acDGnyM5nudJ9Vr75AUDkn7olHx5K3enJ//T/+Q7R327cpNbRs3un9vO38dk9zHqD0dwA88t1JS/ v6gcl/CJEN9/zkb3m2ACGQY1cR6huWuIIPcZQSvpYSWrjVdzH1NCjA21LPH/LQGP0PzLgBJOZ3Dz /ff47oZUKAgtg3KOUVs7qFFddi3UFgnRWQfsfnSTndUVRvnEubp2Va4iTh4zcOxvi/UZy/PGxrX0 LGhk2n19Usghgtrzg0iwzN/qNy/UIGmnG2DZlPVGQ1KtV0FKTAe5Vi9PXNfzUOxU8wm8OM7nQs4u EwaAa/KUU1YVhxH3QKQSFna3Ie4m08w17AuchQ==</SignatureValue>  
- <KeyInfo> 
- <X509Data> 
  <X509Certificate>MIIIRTCCBi2gAwIBAgIQZNRU0aN/uLREz6Tsex2SvTANBgkqhkiG9w0BAQsFADB4MQswCQYDVQQG EwJCUjETMBEGA1UEChMKSUNQLUJyYXNpbDE2MDQGA1UECxMtU2VjcmV0YXJpYSBkYSBSZWNlaXRh IEZlZGVyYWwgZG8gQnJhc2lsIC0gUkZCMRwwGgYDVQQDExNBQyBDZXJ0aXNpZ24gUkZCIEc0MB4X DTE1MDYxODAwMDAwMFoXDTE4MDYxNjIzNTk1OVowgegxCzAJBgNVBAYTAkJSMRMwEQYDVQQKFApJ Q1AtQnJhc2lsMQswCQYDVQQIEwJTUDESMBAGA1UEBxQJU0FPIFBBVUxPMTYwNAYDVQQLFC1TZWNy ZXRhcmlhIGRhIFJlY2VpdGEgRmVkZXJhbCBkbyBCcmFzaWwgLSBSRkIxFjAUBgNVBAsUDVJGQiBl LUNOUEogQTMxITAfBgNVBAsUGEF1dGVudGljYWRvIHBvciBBUiBBUlBFTjEwMC4GA1UEAxMnS0lT VE9OIFJFU1RBVVJBTlRFUyBMVERBOjA5MDA1Nzg0MDAwMTE1MIIBIjANBgkqhkiG9w0BAQEFAAOC AQ8AMIIBCgKCAQEAjfXFYME20wgIk9+NZ2BsfnTr1GrYa1qdc/nQMM3n3y71WxeZBTwKCjvCIlOn XBctIt2Lrg/WarOvsUbWVEjlfbefseH3Wc3R4pgasd6BV9c1u3bgEBm6OlwvHHG9jJZayEZjzt4q jE5j6NbbhXhn9/+6+xo5HFrRHM44cLqCPksHSNxrwWRk7nnNzJ/mFo7kTHq32Th0ycTxkfP0P1mr gVdzWg0cOOzFzhyyeDtgchDRpjYIGLB7ARXbdIEJ+lTFdvvEwFH+jU7PwkBA5hVJB7b7RpVGkcfc MHnDl7vtcez3GHActHCOG49nDNmfZnYZ/rxe8h78KX+EDJPR49X/jQIDAQABo4IDWDCCA1Qwgb0G A1UdEQSBtTCBsqA+BgVgTAEDBKA1BDMyNDA0MTk1Njk0NDgzODMzODA0MDAwMDAwMDAwMDAwMFJO RSBXNDM3MjM3LTJDR05SU1CgHgYFYEwBAwKgFQQTQU5HRUwgRE9NSU5HTyBURVNUQaAZBgVgTAED A6AQBA4wOTAwNTc4NDAwMDExNaAXBgVgTAEDB6AOBAwwMDAwMDAwMDAwMDCBHHBhdWxvLmhlbnJp cXVlQHJhc2NhbC5jb20uYnIwCQYDVR0TBAIwADAfBgNVHSMEGDAWgBQukerWbeWyWYLcOIUpdjQW VjzQPjAOBgNVHQ8BAf8EBAMCBeAwfwYDVR0gBHgwdjB0BgZgTAECAwYwajBoBggrBgEFBQcCARZc aHR0cDovL2ljcC1icmFzaWwuY2VydGlzaWduLmNvbS5ici9yZXBvc2l0b3Jpby9kcGMvQUNfQ2Vy dGlzaWduX1JGQi9EUENfQUNfQ2VydGlzaWduX1JGQi5wZGYwggEWBgNVHR8EggENMIIBCTBXoFWg U4ZRaHR0cDovL2ljcC1icmFzaWwuY2VydGlzaWduLmNvbS5ici9yZXBvc2l0b3Jpby9sY3IvQUND ZXJ0aXNpZ25SRkJHNC9MYXRlc3RDUkwuY3JsMFagVKBShlBodHRwOi8vaWNwLWJyYXNpbC5vdXRy YWxjci5jb20uYnIvcmVwb3NpdG9yaW8vbGNyL0FDQ2VydGlzaWduUkZCRzQvTGF0ZXN0Q1JMLmNy bDBWoFSgUoZQaHR0cDovL3JlcG9zaXRvcmlvLmljcGJyYXNpbC5nb3YuYnIvbGNyL0NlcnRpc2ln bi9BQ0NlcnRpc2lnblJGQkc0L0xhdGVzdENSTC5jcmwwHQYDVR0lBBYwFAYIKwYBBQUHAwIGCCsG AQUFBwMEMIGbBggrBgEFBQcBAQSBjjCBizBfBggrBgEFBQcwAoZTaHR0cDovL2ljcC1icmFzaWwu Y2VydGlzaWduLmNvbS5ici9yZXBvc2l0b3Jpby9jZXJ0aWZpY2Fkb3MvQUNfQ2VydGlzaWduX1JG Ql9HNC5wN2MwKAYIKwYBBQUHMAGGHGh0dHA6Ly9vY3NwLmNlcnRpc2lnbi5jb20uYnIwDQYJKoZI hvcNAQELBQADggIBALcSSZnkqZIIXEA0oULZ1NSUA9hx8pv/BhmQiyr1yo22hyESnlthOO7rB4pw Z7VPGB+OV3VzNfL9PPaywp98r0RsopfZqRktYn6YB7zz+Iofv8mhG/EI97E0EghI51o7+MLRnG9k Kbg23prS02ec8Do18l3rhtcwj41TA4nD5j3jZu4i5THSy6ik4/I/cur3A51sKsfBObEb1ZsrtaM1 L+6i6l2lPqj9ZPEUBGUvyCj4DPZowjmU92rHN0wYrezJTkTTkX1Rg36OIY8NLNmLlPQQmknAX9Ou a7UciIcYh3faRGLIIiqgaqljw/AWWLZtBcBOo5Ynf7Av4OZT9r2+I/ZilZZj/tVFyJ2MhDZol2wQ Vuzf1cu4HqB4GqFh6FkjyRGUgdQZEFxKZzMA8OQXLUtC0Wn1jCykxhk0gt5VsvrFSYYD2yWMneUC WSn5VnMZE7FmYqx7iwAYSpllpfT+U/xt31CWhCnIQlOl41D++HJwFTMGb4GeDzRVepwQrWNX7ZXY h04VjKGVS7BNNbxrOD+G5ivrlYWlbjVBmTvnjzszaOd3JT2OTe/TNtBAs3XZ8GVWW/tvGqn6OByk fKQiyWKqhtt5/WwN+t01+6HNUERHowrDya/e+rqEV9udeYlWN16kdPy0utay9gHr/6EwywiLuMrq DMxwPzLD9xI5gO4C</X509Certificate>  
  </X509Data> 
  </KeyInfo> 
  </Signature> 
  </NFe> 
- <protNFe versao="3.10"> 
- <infProt> 
  <tpAmb>1</tpAmb>  
  <verAplic>SP_NFE_PL_008f</verAplic>  
  <chNFe>35150809005784000115550010000002301900200512</chNFe>  
  <dhRecbto>2015-08-24T17:30:47-03:00</dhRecbto>  
  <nProt>135150523568120</nProt>  
  <digVal>1KS3ACeA4hqLZmb3zpBeB2pJPMI=</digVal>  
  <cStat>100</cStat>  
  <xMotivo>Autorizado o uso da NF-e</xMotivo>  
  </infProt> 
  </protNFe> 
  </nfeProc>

My problem is that I will not fix this XML because I will develop A procedure to read several XML or item fields should be according to XML,
I managed to make it read a product just because it has a knot and with it the error of Too Many Rows
I need to read the items of this XML.
and I created a table that leaves the file generates a CloB field after I have to extract and generate more 2 tables one from header and one of items ...
Top
claudioreiis01
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Posts: 14
Joined: Tue, 25 Aug 2015 1:31 pm

Claudioreiis01 wrote: @ Saziba
The XML I'm trying to read is the following:

My problem is that I will not fix this XML because I will develop a procedure for Read files ie item fields should be according to XML and XML can not be fixed on select,
I was able to make it read a product just because it has a knot and with it the error of TOO Many Rows
I need to read the items of this XML I used the.
and I created a table that leaves a clobling field after I have to extract and generate more 2 tables one from header and one of items ...
to read from a field table Clob and insert in another I used the extractValue (content, '/ nfeproc / nfe / infnfe / det / prod / cfop', 'xmlns = "http://www.portalfiscal.inf.br/nfe"')CFOP for example
Top
Post Reply

Return to “PL/SQL”

  • Information

  • Who is online

    Users browsing this forum: No registered users and 21 guests