How can i compare two XML files storeds in a LONG column

509198 · Oct 4, 2012 1:46PM

Hi,

I need to compare two xml files. My xmls are stored in two table like this:

Table 1
ID_COL number(5);
XML1 LONG()

Table 2
ID_COL number(5);
XML2 LONG()

I need compare the values of the tags of this xmls files e to list de differences.

Tks,
Fernando.

sb92075 · Oct 4, 2012 1:49PM

Bodnarczuk, Fernando wrote:
Hi,

I need to compare two xml files. My xmls are stored in two table like this:

Table 1
ID_COL number(5);
XML1 LONG()

Table 2
ID_COL number(5);
XML2 LONG()

I need compare the values of the tags of this xmls files e to list de differences.

Tks,
Fernando.

whoever implemented the LONG variable should be summarily terminated without any delay.

Start writing some PL/SQL to implement the comparisons.

509198 · Oct 4, 2012 2:01PM

ok, but is not my system and i cannot change the tables....and i have a lot of files to compare......i wont to compare the entire xml, but just some tags?

if someone can help me, i appreciate very much.

Tks,
Fernando.

odie_63 · Oct 4, 2012 2:02PM

You've not inherited the easiest datatype to work with.

Not enough details provided to help anyway :

- What's the database version?
- How big are those XMLs? > 32k?
- What do you mean by "compare"?

Post some sample documents from both tables and explain how you want the result to look like.

{message:id=9360002}

509198 · Oct 4, 2012 2:42PM

ok i´m sorry....

my database is :
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
PL/SQL Release 10.2.0.3.0 - Production
"CORE 10.2.0.3.0 Production"
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

my xmls files can be > 32k

compare means "to show the differences"

bellow is a copy/paste of a xml file stored in my database:

<?xml version="1.0" encoding="UTF-8" ?>
<nfeProc xmlns="http://www.portalfiscal.inf.br/nfe" xmlns:ds="http://www.w3.org/2000/09/xmldsig#" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" versao="1.10">
<?xml version="1.0" encoding="UTF-8"?><NFe xmlns="http://www.portalfiscal.inf.br/nfe"><infNFe Id="NFe31090359106377000172550010001000174450258602" versao="1.10"><ide><cUF>31</cUF><cNF>445025860</cNF><natOp>TRANSF.MAT.USO/CONSUMO -(E)</natOp><indPag>0</indPag><mod>55</mod><serie>1</serie><nNF>100017</nNF><dEmi>2009-03-23</dEmi><dSaiEnt>2009-03-23</dSaiEnt><tpNF>1</tpNF><cMunFG>3159605</cMunFG><tpImp>1</tpImp><tpEmis>1</tpEmis><cDV>2</cDV><tpAmb>1</tpAmb><finNFe>1</finNFe><procEmi>0</procEmi><verProc>1.0</verProc></ide><emit><CNPJ>59106377000172</CNPJ><xNome>METAGAL IND E COM LTDA</xNome><xFant>METAGAL INDUSTRIA E COMERCIO LTDA</xFant><enderEmit><xLgr>ROD BR 459</xLgr><nro>333</nro><xCpl>KM 121</xCpl><xBairro>DISTRITO INDUSTRIAL</xBairro><cMun>3159605</cMun><xMun>SANTA RITA DO SAPUCAI</xMun><UF>MG</UF><CEP>37540000</CEP><cPais>1058</cPais><xPais>BRASIL</xPais><fone>3534719100</fone></enderEmit><IE>5969141300009</IE><IM>01183</IM><CNAE>2949299</CNAE></emit><dest><CNPJ>59106377000849</CNPJ><xNome>METAGAL IND. COM. LTDA.</xNome><enderDest><xLgr>RUA MERCURIO</xLgr><nro>333</nro><xBairro>CH DOS EUCLIDE</xBairro><cMun>3117801</cMun><xMun>CONCEICAO DOS OUROS</xMun><UF>MG</UF><CEP>37548000</CEP><cPais>1058</cPais><xPais>BRASIL</xPais></enderDest><IE>1789141300224</IE></dest><det nItem="1"><prod><cProd>E1020031012</cProd><cEAN>00000000000000</cEAN><xProd>RACK PRACA</xProd><NCM>73102190</NCM><EXTIPI>00</EXTIPI><genero>73</genero><CFOP>5557</CFOP><uCom>PC</uCom><qCom>4.0000</qCom><vUnCom>200.0000</vUnCom><vProd>800.00</vProd><cEANTrib></cEANTrib><uTrib>PC</uTrib><qTrib>4.0000</qTrib><vUnTrib>200.0000</vUnTrib></prod><imposto><ICMS><ICMS40><orig>0</orig><CST>40</CST></ICMS40></ICMS><IPI><clEnq>0</clEnq><CNPJProd>00000000000000</CNPJProd><cSelo>0</cSelo><qSelo>0</qSelo><cEnq>0</cEnq><IPINT><CST>52</CST></IPINT></IPI><PIS><PISNT><CST>04</CST></PISNT></PIS><COFINS><COFINSNT><CST>04</CST></COFINSNT></COFINS></imposto></det><total><ICMSTot><vBC>0.00</vBC><vICMS>0.00</vICMS><vBCST>0.00</vBCST><vST>0.00</vST><vProd>800.00</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>800.00</vNF></ICMSTot><retTrib></retTrib></total><transp><modFrete>0</modFrete><transporta><CNPJ>00980331000488</CNPJ><xNome>THALE TRANSPORTES E LOG. LTDA</xNome><IE>5963866160070</IE><xEnder>ROD.BR.459-KM 121 - DIST.INDL.</xEnder><xMun>SANTA RITA DO SAPUCAI</xMun><UF>MG</UF></transporta><veicTransp><placa>CYN8393</placa><UF>SP</UF></veicTransp><vol><qVol>8</qVol><esp>VOLUMES</esp><pesoL>40.000</pesoL><pesoB>40.000</pesoB></vol></transp><cobr></cobr><infAdic><infCpl>VIA DE TRANSPORTE RODOVIARIO A/C VALDIR -PCP ICMS - ISENTO CONF.ART.6o, ITEM 105, DO ANEXO I, LETRA "B", DO DEC. 43.080/02. IPI - NAO-INCIDENCIA CONF.ART.6o,INC II DO DECRETO 4.544/02.</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="#NFe31090359106377000172550010001000174450258602"><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>drXfDwREJavPjmfN/Oqg3sNW19c=</DigestValue></Reference></SignedInfo><SignatureValue>fergRTNyM3h5wuWXR0QhCfsBkQTiB4fghE1xJka50LxqDggvUXG8Dp9iUFiRe+e4Hk11WlvG6mUWRAIXB6S+PT9DIn3tbh+69dM53A0ZNxtwLQRAexVSm2w2Mdd4heUFhef1aF3WXICgEchIXBaXaTMu9TOdRUb3LPoMNXQAhSA=</SignatureValue><KeyInfo><X509Data><X509Certificate>MIIFFDCCA/ygAwIBAgIQMjAwODEyMDgxODU0MTgwMjANBgkqhkiG9w0BAQUFADCBpjELMAkGA1UEBhMCQlIxEzARBgNVBAoTCklDUC1CcmFzaWwxOzA5BgNVBAsTMlNlcnZpY28gRmVkZXJhbCBkZSBQcm9jZXNzYW1lbnRvIGRlIERhZG9zIC0gU0VSUFJPMQ8wDQYDVQQLEwZDU1BCLTExNDAyBgNVBAMTK0F1dG9yaWRhZGUgQ2VydGlmaWNhZG9yYSBkbyBTRVJQUk8gRmluYWwgdjEwHhcNMDgxMjE2MTE1ODIwWhcNMDkxMjE2MTE1NzI1WjCBnTELMAkGA1UEBhMCQlIxEzARBgNVBAoTCklDUC1CcmFzaWwxKzApBgNVBAsTIkF1dG9yaWRhZGUgQ2VydGlmaWNhZG9yYSBTRVJQUk9BQ0YxETAPBgNVBAsTCE1BWFhEQVRBMTkwNwYDVQQDEzBNRVRBR0FMIElORFVTVFJJQSBFIENPTUVSQ0lPIExUREE6NTkxMDYzNzcwMDAxNzIwgZ8wDQYJKoZIhvcNAQEBBQADgY0AMIGJAoGBAM/lVVGdppN5K70HWHeJmBcwXuqDFtbF27+qafHIZYOgJNZUS4OuRP+JTG7adCYZi8YM071FHrm9m/kkdYuqIg3yYMJV+4jjyJ+V43V7nYPQM9LgRdVMYxyTp27CqD3tp8fgZeKOgiIpSt/oZK+fbNjM5uPDYTJbdm8yndY3R5IjAgMBAAGjggHHMIIBwzAPBgNVHRMBAf8EBTADAQEAMB8GA1UdIwQYMBaAFDI4lsfuRGTpmqoVXeAItI2JR1GiMA4GA1UdDwEB/wQEAwIF4DBcBgNVHSAEVTBTMFEGBmBMAQIBEDBHMEUGCCsGAQUFBwIBFjlodHRwczovL2NjZC5zZXJwcm8uZ292LmJyL3NlcnByb2FjZi9kb2NzL2RwY3NlcnByb2FjZi5wZGYwgbUGA1UdEQSBrTCBqqA9BgVgTAEDBKA0BDIxNjEwMTk0NDAwNTUzMDAwODEwMDAwMDAwMDAwMDAwMDAwMDAwMjg1OTI0NzVTU1BTUKAdBgVgTAEDAqAUBBJSQUZBRUwgSk9TRSBIQVNTT06gGQYFYEwBAwOgEAQONTkxMDYzNzcwMDAxNzKgFwYFYEwBAwegDgQMMDAwMDAwMDAwMDAwgRZyaGFzc29uQG1ldGFnYWwuY29tLmJyMCoGA1UdJQEB/wQgMB4GCCsGAQUFBwMCBggrBgEFBQcDAwYIKwYBBQUHAwQwPQYDVR0fBDYwNDAyoDCgLoYsaHR0cDovL2NjZC5zZXJwcm8uZ292LmJyL2xjci9zZXJwcm9hY2Z2MS5jcmwwDQYJKoZIhvcNAQEFBQADggEBAEjQqBQ9ksYL7/wITtyK6Bs97lih1naoKEUIGCqCGARDA3/tb3+K1jlVHkF68OW07iLauYXuLALME+0A5JmCKTwKqjZHgG40+bs6lg9HOKo7QGALvsRFZQ88uu9NGy2/1xNNKdD3O8NIjcXz00BAY7nxxvWo2fFuhJVE743RR3YZ/ocqhs8I4geu+gxmivcY3allagdAJU2IkJIZwYspd7DqN9WPcY4Ix5b1PjinVaNQJadzCTYiBzrg4OeqNg0JWyiG7SbqpVTLf4dPY+IyynJ4mF5qUz96qzCBHEPAuvIOI1NTxOAZ4lzN1p+RMqWycJqvNtACi/JLe3brJEFq/Rk=</X509Certificate></X509Data></KeyInfo></Signature></NFe><protNFe versao="1.10"><infProt><tpAmb>1</tpAmb><verAplic>1.00</verAplic><chNFe>31090359106377000172550010001000174450258602</chNFe><dhRecbto>23/03/2009</dhRecbto><nProt>131090015588649</nProt><cStat>100</cStat><xMotivo>Autorizado o uso da NF-e</xMotivo></infProt></protNFe></nfeProc>

Tks,
Fernando

odie_63 · Oct 4, 2012 4:03PM

compare means "to show the differences"

Really?

That still doesn't explain what output format you need.
Do you want to compare every single element/attribute? Or just a restricted list?

>

bellow is a copy/paste of a xml file stored in my database:

<?xml version="1.0" encoding="UTF-8" ?>
<nfeProc xmlns="http://www.portalfiscal.inf.br/nfe" xmlns:ds="http://www.w3.org/2000/09/xmldsig#" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" versao="1.10">
<?xml version="1.0" encoding="UTF-8"?><NFe xmlns="http://www.portalfiscal.inf.br/nfe"><infNFe Id="NFe31090359106377000172550010001000174450258602" versao="1.10">

Doesn't seem to be a wellformed XML.
What's with this 2nd XML prolog on line 3?

509198 · Oct 4, 2012 4:17PM

yes odie you are right...i think that my xml is wrong...
I would like to compare every element/attribute...

Bellow is another xml...this is ok....tks

<?xml version="1.0" encoding="UTF-8" ?>
- <nfeProc xmlns="http://www.portalfiscal.inf.br/nfe" versao="2.00">
- <NFe xmlns="http://www.portalfiscal.inf.br/nfe">
- <infNFe Id="NFe31121059106377000172550010003957681605366269" versao="2.00">
+ <ide>
<cUF>31</cUF>
<cNF>60536626</cNF>
<natOp>VDAS PROD ESTABELECIMENT</natOp>
<indPag>1</indPag>
<mod>55</mod>
<serie>1</serie>
<nNF>395768</nNF>
<dEmi>2012-10-03</dEmi>
<dSaiEnt>2012-10-03</dSaiEnt>
<hSaiEnt>18:30:00</hSaiEnt>
<tpNF>1</tpNF>
<cMunFG>3159605</cMunFG>
<tpImp>1</tpImp>
<tpEmis>1</tpEmis>
<cDV>9</cDV>
<tpAmb>1</tpAmb>
<finNFe>1</finNFe>
<procEmi>0</procEmi>
<verProc>1.0</verProc>
</ide>
+ <emit>
<CNPJ>59106377000172</CNPJ>
<xNome>METAGAL IND E COM LTDA</xNome>
<xFant>METAGAL INDUSTRIA E COMERCIO LTDA</xFant>
- <enderEmit>
<xLgr>ROD BR 459</xLgr>
<nro>333</nro>
<xCpl>KM 121</xCpl>
<xBairro>DISTRITO INDUSTRIAL</xBairro>
<cMun>3159605</cMun>
<xMun>SANTA RITA DO SAPUCAI</xMun>
<UF>MG</UF>
<CEP>37540000</CEP>
<cPais>1058</cPais>
<xPais>BRASIL</xPais>
<fone>3534719100</fone>
</enderEmit>
<IE>5969141300009</IE>
<IM>01183</IM>
<CNAE>2949299</CNAE>
<CRT>3</CRT>
</emit>
+ <dest>
<CNPJ>59275792000150</CNPJ>
<xNome>GENERAL MOTORS DO BRASIL LTDA</xNome>
- <enderDest>
<xLgr>AV GOIAS</xLgr>
<nro>1805</nro>
<xBairro>BARCELONA</xBairro>
<cMun>3548807</cMun>
<xMun>SAO CAETANO DO SUL</xMun>
<UF>SP</UF>
<CEP>09501970</CEP>
<cPais>1058</cPais>
<xPais>BRASIL</xPais>
</enderDest>
<IE>636003724112</IE>
<email>[email protected]</email>
</dest>
- <det nItem="1">
+ <prod>
<cProd>XM20C9500PPR</cProd>
<cEAN />
<xProd>ESPELHO RETROVISOR EXTERNO</xProd>
<NCM>70091000</NCM>
<CFOP>6501</CFOP>
<uCom>PC</uCom>
<qCom>80.0000</qCom>
<vUnCom>35.8700000000</vUnCom>
<vProd>2869.60</vProd>
<cEANTrib />
<uTrib>PC</uTrib>
<qTrib>80.0000</qTrib>
<vUnTrib>35.8700000000</vUnTrib>
<indTot>1</indTot>
<xPed>XRW001RV</xPed>
<nItemPed>000001</nItemPed>
</prod>
- <imposto>
- <ICMS>
- <ICMS00>
<orig>0</orig>
<CST>00</CST>
<modBC>3</modBC>
<vBC>2869.60</vBC>
<pICMS>12.00</pICMS>
<vICMS>344.35</vICMS>
</ICMS00>
</ICMS>
- <IPI>
<CNPJProd>00000000000000</CNPJProd>
<cEnq>0</cEnq>
- <IPINT>
<CST>54</CST>
</IPINT>
</IPI>
- <II>
<vBC>0.00</vBC>
<vDespAdu>0.00</vDespAdu>
<vII>0.00</vII>
<vIOF>0.00</vIOF>
</II>
- <PIS>
- <PISNT>
<CST>08</CST>
</PISNT>
</PIS>
- <COFINS>
- <COFINSNT>
<CST>08</CST>
</COFINSNT>
</COFINS>
</imposto>
<infAdProd>PC.93378954-COMPL.PED.XRW001RV</infAdProd>
</det>
+ <total>
- <ICMSTot>
<vBC>2869.60</vBC>
<vICMS>344.35</vICMS>
<vBCST>0.00</vBCST>
<vST>0.00</vST>
<vProd>2869.60</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>2869.60</vNF>
</ICMSTot>
<retTrib />
</total>
+ <transp>
<modFrete>0</modFrete>
- <transporta>
<CNPJ>00980331000488</CNPJ>
<xNome>THALE TRANSPORTES E LOG. LTDA</xNome>
<IE>5963866160070</IE>
<xEnder>ROD BR 459-KM 121 - DIST INDL, S/N</xEnder>
<xMun>SANTA RITA DO SAPUCAI</xMun>
<UF>MG</UF>
</transporta>
- <veicTransp>
<placa>DPF8048</placa>
<UF>SP</UF>
</veicTransp>
- <vol>
<qVol>20</qVol>
<esp>OUTROS</esp>
<pesoL>64.000</pesoL>
<pesoB>104.000</pesoB>
</vol>
</transp>
+ <cobr>
- <fat>
<nFat>000000395768</nFat>
<vOrig>2869.60</vOrig>
</fat>
- <dup>
<nDup>1</nDup>
<dVenc>2012-11-20</dVenc>
<vDup>2869.60</vDup>
</dup>
</cobr>
- <infAdic>
<infCpl>VIA DE TRANSPORTE RODOVIARIA CODIGO : 108061 PEDIDO NRO : ACIMA FABRICA :72480 REDESPACHO ATRAVES DE VELOCE LOGISTICA S/A ESTRADA DOS ALVARENGAS SAO BERNARDO CAMPO ASSUNCAO SP CNPJ : 10.299.567/0003-26 IE : 635.600.028.11 IPI - IMUNE CFE.ART.18, INCISO II, DO RIPI - DECRETO No.7.212/2010. REMESSA COM FIM ESPECIFICO DE EXPORTACAOMERC.A SER EXPORT.P/GENERAL MOTORS DO BRASIL LTDA.DECEX=3-0322/10-0007 ESTOCA-GEM TEMPORARIA NA VELOCE LOGISTICA S.A. ESTR.ALVARENGAS,4018 B.ASSUNCAO S.B.C. CNPJ10.299.567/0003-26IE.635.600.028.110 FT NR.431.183 ROMANEIO :131.588</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="#NFe31121059106377000172550010003957681605366269">
- <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>jN2ozPH3/GvAS8Q5lh/t9bzuXCw=</DigestValue>
</Reference>
</SignedInfo>
<SignatureValue>GAXPLvMCtIYdwMxXDcyL0kr5hCDPCFw8/uNYHFcdTMqBhLgIcEtzHRf8qioWlUVSHNf5jnCLKGjhDV4bEJqkcBhWsKouMzojQ+Z6hkFQAWNuJfPIzutmtRy3AePc5tHK0lI3tF3ws9memboJ8sW21IOWHB6eB0jK2gmhcOlDejc=</SignatureValue>
- <KeyInfo>
- <X509Data>
<X509Certificate>MIIGajCCBVKgAwIBAgIIaHrIAHUBA4wwDQYJKoZIhvcNAQEFBQAwdTELMAkGA1UEBhMCQlIxEzARBgNVBAoTCklDUC1CcmFzaWwxNjA0BgNVBAsTLVNlY3JldGFyaWEgZGEgUmVjZWl0YSBGZWRlcmFsIGRvIEJyYXNpbCAtIFJGQjEZMBcGA1UEAxMQQUMgU0VSQVNBIFJGQiB2MTAeFw0xMTEwMjQxMzQ3MThaFw0xMjEwMjMxMzQ3MThaMIHuMQswCQYDVQQGEwJCUjELMAkGA1UECBMCTUcxHjAcBgNVBAcTFVNBTlRBIFJJVEEgRE8gU0FQVUNBSTETMBEGA1UEChMKSUNQLUJyYXNpbDE2MDQGA1UECxMtU2VjcmV0YXJpYSBkYSBSZWNlaXRhIEZlZGVyYWwgZG8gQnJhc2lsIC0gUkZCMRYwFAYDVQQLEw1SRkIgZS1DTlBKIEExMRIwEAYDVQQLEwlBUiBTRVJBU0ExOTA3BgNVBAMTME1FVEFHQUwgSU5EVVNUUklBIEUgQ09NRVJDSU8gTFREQTo1OTEwNjM3NzAwMDE3MjCBnzANBgkqhkiG9w0BAQEFAAOBjQAwgYkCgYEA0E4tWimBp7BdqbUbNQLK8NDkxMsqeEnJILklbGp7e0MghfjADGcV9z07B0t2KsAhlPAtx22D885rycUzVehoUisyB3a3Xfu3FqRB9ItXvEPDaLM/DtJrMu3xIWq60RzoSgnFyw8cNJ3hYJxloPm5exTc5kOHcQlNhsiLzzJLk4ECAwEAAaOCAwYwggMCMAkGA1UdEwQCMAAwDgYDVR0PAQH/BAQDAgXgMB0GA1UdJQQWMBQGCCsGAQUFBwMCBggrBgEFBQcDBDAfBgNVHSMEGDAWgBSa3SK29nfpQm9IwlFAoFbi83Q/uzCBuQYDVR0RBIGxMIGugRVHTklMQ0VATUVUQUdBTC5DT00uQlKgIQYFYEwBAwKgGBMWR0VPVkFOSSBEQSBTSUxWQSBOSUxDRaAZBgVgTAEDA6AQEw41OTEwNjM3NzAwMDE3MqA+BgVgTAEDBKA1EzMwMTExMTk2NzA3MzY3NTg3ODAzMDAwMDAwMDAwMDAwMDAwMTUuNzcwLjg2NDBTU1AgU1CgFwYFYEwBAwegDhMMMDAwMDAwMDAwMDAwMFcGA1UdIARQME4wTAYGYEwBAgENMEIwQAYIKwYBBQUHAgEWNGh0dHA6Ly93d3cuY2VydGlmaWNhZG9kaWdpdGFsLmNvbS5ici9yZXBvc2l0b3Jpby9kcGMwgfMGA1UdHwSB6zCB6DBKoEigRoZEaHR0cDovL3d3dy5jZXJ0aWZpY2Fkb2RpZ2l0YWwuY29tLmJyL3JlcG9zaXRvcmlvL2xjci9zZXJhc2FyZmJ2MS5jcmwwRKBCoECGPmh0dHA6Ly9sY3IuY2VydGlmaWNhZG9zLmNvbS5ici9yZXBvc2l0b3Jpby9sY3Ivc2VyYXNhcmZidjEuY3JsMFSgUqBQhk5odHRwOi8vcmVwb3NpdG9yaW8uaWNwYnJhc2lsLmdvdi5ici9sY3IvU2VyYXNhL3JlcG9zaXRvcmlvL2xjci9zZXJhc2FyZmJ2MS5jcmwwgZkGCCsGAQUFBwEBBIGMMIGJMEgGCCsGAQUFBzAChjxodHRwOi8vd3d3LmNlcnRpZmljYWRvZGlnaXRhbC5jb20uYnIvY2FkZWlhcy9zZXJhc2FyZmJ2MS5wN2IwPQYIKwYBBQUHMAGGMWh0dHA6Ly9vY3NwLmNlcnRpZmljYWRvZGlnaXRhbC5jb20uYnIvc2VyYXNhcmZidjEwDQYJKoZIhvcNAQEFBQADggEBAD70onZUzYAAUjK/j3b+d1VULHGPxmJU9sjfAa1QiCt1JniRTZITjXcw08pT/DMDmZRHOkWM0amQZtKKa6Oz9fg2Mv+aBoh0ERuC2XMTpdB0Kq04cY90zMJbteMvCzpUKIsT2wJDRZok1my+GyR3rUxLyHTfnqt1+f3o1DeRiGmldHIHHlv6MeVZeL82jfrw3kZnFi8k+rDGfywcfum9M66qfNqUv9fL/ibLVogzwg8WyErbbW1cAMqxv8rWNJHvNs8dbJOCBKaW4ZJDkO/8CpuvyKxSdS3OUdjuI1RAx9R0RBMemuv4h4S7rhOEhjkBB5hHFT5IeDded+oVzY3lpIU=</X509Certificate>
</X509Data>
</KeyInfo>
</Signature>
</NFe>
- <protNFe versao="2.00">
- <infProt>
<tpAmb>1</tpAmb>
<verAplic>13_0_32</verAplic>
<chNFe>31121059106377000172550010003957681605366269</chNFe>
<dhRecbto>2012-10-03T17:35:55</dhRecbto>
<nProt>131120853536488</nProt>
<digVal>jN2ozPH3/GvAS8Q5lh/t9bzuXCw=</digVal>
<cStat>100</cStat>
<xMotivo>Autorizado o uso da NF-e</xMotivo>
</infProt>
</protNFe>
</nfeProc>

509198 · Oct 4, 2012 4:19PM

im sorry bellow is the xml ok:

<?xml version="1.0" encoding="UTF-8" ?>
- <nfeProc xmlns="http://www.portalfiscal.inf.br/nfe" versao="2.00">
- <NFe xmlns="http://www.portalfiscal.inf.br/nfe">
- <infNFe Id="NFe31121059106377000172550010003957681605366269" versao="2.00">
- <ide>
<cUF>31</cUF>
<cNF>60536626</cNF>
<natOp>VDAS PROD ESTABELECIMENT</natOp>
<indPag>1</indPag>
<mod>55</mod>
<serie>1</serie>
<nNF>395768</nNF>
<dEmi>2012-10-03</dEmi>
<dSaiEnt>2012-10-03</dSaiEnt>
<hSaiEnt>18:30:00</hSaiEnt>
<tpNF>1</tpNF>
<cMunFG>3159605</cMunFG>
<tpImp>1</tpImp>
<tpEmis>1</tpEmis>
<cDV>9</cDV>
<tpAmb>1</tpAmb>
<finNFe>1</finNFe>
<procEmi>0</procEmi>
<verProc>1.0</verProc>
</ide>
- <emit>
<CNPJ>59106377000172</CNPJ>
<xNome>METAGAL IND E COM LTDA</xNome>
<xFant>METAGAL INDUSTRIA E COMERCIO LTDA</xFant>
- <enderEmit>
<xLgr>ROD BR 459</xLgr>
<nro>333</nro>
<xCpl>KM 121</xCpl>
<xBairro>DISTRITO INDUSTRIAL</xBairro>
<cMun>3159605</cMun>
<xMun>SANTA RITA DO SAPUCAI</xMun>
<UF>MG</UF>
<CEP>37540000</CEP>
<cPais>1058</cPais>
<xPais>BRASIL</xPais>
<fone>3534719100</fone>
</enderEmit>
<IE>5969141300009</IE>
<IM>01183</IM>
<CNAE>2949299</CNAE>
<CRT>3</CRT>
</emit>
- <dest>
<CNPJ>59275792000150</CNPJ>
<xNome>GENERAL MOTORS DO BRASIL LTDA</xNome>
- <enderDest>
<xLgr>AV GOIAS</xLgr>
<nro>1805</nro>
<xBairro>BARCELONA</xBairro>
<cMun>3548807</cMun>
<xMun>SAO CAETANO DO SUL</xMun>
<UF>SP</UF>
<CEP>09501970</CEP>
<cPais>1058</cPais>
<xPais>BRASIL</xPais>
</enderDest>
<IE>636003724112</IE>
<email>[email protected]</email>
</dest>
- <det nItem="1">
- <prod>
<cProd>XM20C9500PPR</cProd>
<cEAN />
<xProd>ESPELHO RETROVISOR EXTERNO</xProd>
<NCM>70091000</NCM>
<CFOP>6501</CFOP>
<uCom>PC</uCom>
<qCom>80.0000</qCom>
<vUnCom>35.8700000000</vUnCom>
<vProd>2869.60</vProd>
<cEANTrib />
<uTrib>PC</uTrib>
<qTrib>80.0000</qTrib>
<vUnTrib>35.8700000000</vUnTrib>
<indTot>1</indTot>
<xPed>XRW001RV</xPed>
<nItemPed>000001</nItemPed>
</prod>
- <imposto>
- <ICMS>
- <ICMS00>
<orig>0</orig>
<CST>00</CST>
<modBC>3</modBC>
<vBC>2869.60</vBC>
<pICMS>12.00</pICMS>
<vICMS>344.35</vICMS>
</ICMS00>
</ICMS>
- <IPI>
<CNPJProd>00000000000000</CNPJProd>
<cEnq>0</cEnq>
- <IPINT>
<CST>54</CST>
</IPINT>
</IPI>
- <II>
<vBC>0.00</vBC>
<vDespAdu>0.00</vDespAdu>
<vII>0.00</vII>
<vIOF>0.00</vIOF>
</II>
- <PIS>
- <PISNT>
<CST>08</CST>
</PISNT>
</PIS>
- <COFINS>
- <COFINSNT>
<CST>08</CST>
</COFINSNT>
</COFINS>
</imposto>
<infAdProd>PC.93378954-COMPL.PED.XRW001RV</infAdProd>
</det>
- <total>
- <ICMSTot>
<vBC>2869.60</vBC>
<vICMS>344.35</vICMS>
<vBCST>0.00</vBCST>
<vST>0.00</vST>
<vProd>2869.60</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>2869.60</vNF>
</ICMSTot>
<retTrib />
</total>
- <transp>
<modFrete>0</modFrete>
- <transporta>
<CNPJ>00980331000488</CNPJ>
<xNome>THALE TRANSPORTES E LOG. LTDA</xNome>
<IE>5963866160070</IE>
<xEnder>ROD BR 459-KM 121 - DIST INDL, S/N</xEnder>
<xMun>SANTA RITA DO SAPUCAI</xMun>
<UF>MG</UF>
</transporta>
- <veicTransp>
<placa>DPF8048</placa>
<UF>SP</UF>
</veicTransp>
- <vol>
<qVol>20</qVol>
<esp>OUTROS</esp>
<pesoL>64.000</pesoL>
<pesoB>104.000</pesoB>
</vol>
</transp>
- <cobr>
- <fat>
<nFat>000000395768</nFat>
<vOrig>2869.60</vOrig>
</fat>
- <dup>
<nDup>1</nDup>
<dVenc>2012-11-20</dVenc>
<vDup>2869.60</vDup>
</dup>
</cobr>
- <infAdic>
<infCpl>VIA DE TRANSPORTE RODOVIARIA CODIGO : 108061 PEDIDO NRO : ACIMA FABRICA :72480 REDESPACHO ATRAVES DE VELOCE LOGISTICA S/A ESTRADA DOS ALVARENGAS SAO BERNARDO CAMPO ASSUNCAO SP CNPJ : 10.299.567/0003-26 IE : 635.600.028.11 IPI - IMUNE CFE.ART.18, INCISO II, DO RIPI - DECRETO No.7.212/2010. REMESSA COM FIM ESPECIFICO DE EXPORTACAOMERC.A SER EXPORT.P/GENERAL MOTORS DO BRASIL LTDA.DECEX=3-0322/10-0007 ESTOCA-GEM TEMPORARIA NA VELOCE LOGISTICA S.A. ESTR.ALVARENGAS,4018 B.ASSUNCAO S.B.C. CNPJ10.299.567/0003-26IE.635.600.028.110 FT NR.431.183 ROMANEIO :131.588</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="#NFe31121059106377000172550010003957681605366269">
- <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>jN2ozPH3/GvAS8Q5lh/t9bzuXCw=</DigestValue>
</Reference>
</SignedInfo>
<SignatureValue>GAXPLvMCtIYdwMxXDcyL0kr5hCDPCFw8/uNYHFcdTMqBhLgIcEtzHRf8qioWlUVSHNf5jnCLKGjhDV4bEJqkcBhWsKouMzojQ+Z6hkFQAWNuJfPIzutmtRy3AePc5tHK0lI3tF3ws9memboJ8sW21IOWHB6eB0jK2gmhcOlDejc=</SignatureValue>
- <KeyInfo>
- <X509Data>
<X509Certificate>MIIGajCCBVKgAwIBAgIIaHrIAHUBA4wwDQYJKoZIhvcNAQEFBQAwdTELMAkGA1UEBhMCQlIxEzARBgNVBAoTCklDUC1CcmFzaWwxNjA0BgNVBAsTLVNlY3JldGFyaWEgZGEgUmVjZWl0YSBGZWRlcmFsIGRvIEJyYXNpbCAtIFJGQjEZMBcGA1UEAxMQQUMgU0VSQVNBIFJGQiB2MTAeFw0xMTEwMjQxMzQ3MThaFw0xMjEwMjMxMzQ3MThaMIHuMQswCQYDVQQGEwJCUjELMAkGA1UECBMCTUcxHjAcBgNVBAcTFVNBTlRBIFJJVEEgRE8gU0FQVUNBSTETMBEGA1UEChMKSUNQLUJyYXNpbDE2MDQGA1UECxMtU2VjcmV0YXJpYSBkYSBSZWNlaXRhIEZlZGVyYWwgZG8gQnJhc2lsIC0gUkZCMRYwFAYDVQQLEw1SRkIgZS1DTlBKIEExMRIwEAYDVQQLEwlBUiBTRVJBU0ExOTA3BgNVBAMTME1FVEFHQUwgSU5EVVNUUklBIEUgQ09NRVJDSU8gTFREQTo1OTEwNjM3NzAwMDE3MjCBnzANBgkqhkiG9w0BAQEFAAOBjQAwgYkCgYEA0E4tWimBp7BdqbUbNQLK8NDkxMsqeEnJILklbGp7e0MghfjADGcV9z07B0t2KsAhlPAtx22D885rycUzVehoUisyB3a3Xfu3FqRB9ItXvEPDaLM/DtJrMu3xIWq60RzoSgnFyw8cNJ3hYJxloPm5exTc5kOHcQlNhsiLzzJLk4ECAwEAAaOCAwYwggMCMAkGA1UdEwQCMAAwDgYDVR0PAQH/BAQDAgXgMB0GA1UdJQQWMBQGCCsGAQUFBwMCBggrBgEFBQcDBDAfBgNVHSMEGDAWgBSa3SK29nfpQm9IwlFAoFbi83Q/uzCBuQYDVR0RBIGxMIGugRVHTklMQ0VATUVUQUdBTC5DT00uQlKgIQYFYEwBAwKgGBMWR0VPVkFOSSBEQSBTSUxWQSBOSUxDRaAZBgVgTAEDA6AQEw41OTEwNjM3NzAwMDE3MqA+BgVgTAEDBKA1EzMwMTExMTk2NzA3MzY3NTg3ODAzMDAwMDAwMDAwMDAwMDAwMTUuNzcwLjg2NDBTU1AgU1CgFwYFYEwBAwegDhMMMDAwMDAwMDAwMDAwMFcGA1UdIARQME4wTAYGYEwBAgENMEIwQAYIKwYBBQUHAgEWNGh0dHA6Ly93d3cuY2VydGlmaWNhZG9kaWdpdGFsLmNvbS5ici9yZXBvc2l0b3Jpby9kcGMwgfMGA1UdHwSB6zCB6DBKoEigRoZEaHR0cDovL3d3dy5jZXJ0aWZpY2Fkb2RpZ2l0YWwuY29tLmJyL3JlcG9zaXRvcmlvL2xjci9zZXJhc2FyZmJ2MS5jcmwwRKBCoECGPmh0dHA6Ly9sY3IuY2VydGlmaWNhZG9zLmNvbS5ici9yZXBvc2l0b3Jpby9sY3Ivc2VyYXNhcmZidjEuY3JsMFSgUqBQhk5odHRwOi8vcmVwb3NpdG9yaW8uaWNwYnJhc2lsLmdvdi5ici9sY3IvU2VyYXNhL3JlcG9zaXRvcmlvL2xjci9zZXJhc2FyZmJ2MS5jcmwwgZkGCCsGAQUFBwEBBIGMMIGJMEgGCCsGAQUFBzAChjxodHRwOi8vd3d3LmNlcnRpZmljYWRvZGlnaXRhbC5jb20uYnIvY2FkZWlhcy9zZXJhc2FyZmJ2MS5wN2IwPQYIKwYBBQUHMAGGMWh0dHA6Ly9vY3NwLmNlcnRpZmljYWRvZGlnaXRhbC5jb20uYnIvc2VyYXNhcmZidjEwDQYJKoZIhvcNAQEFBQADggEBAD70onZUzYAAUjK/j3b+d1VULHGPxmJU9sjfAa1QiCt1JniRTZITjXcw08pT/DMDmZRHOkWM0amQZtKKa6Oz9fg2Mv+aBoh0ERuC2XMTpdB0Kq04cY90zMJbteMvCzpUKIsT2wJDRZok1my+GyR3rUxLyHTfnqt1+f3o1DeRiGmldHIHHlv6MeVZeL82jfrw3kZnFi8k+rDGfywcfum9M66qfNqUv9fL/ibLVogzwg8WyErbbW1cAMqxv8rWNJHvNs8dbJOCBKaW4ZJDkO/8CpuvyKxSdS3OUdjuI1RAx9R0RBMemuv4h4S7rhOEhjkBB5hHFT5IeDded+oVzY3lpIU=</X509Certificate>
</X509Data>
</KeyInfo>
</Signature>
</NFe>
- <protNFe versao="2.00">
- <infProt>
<tpAmb>1</tpAmb>
<verAplic>13_0_32</verAplic>
<chNFe>31121059106377000172550010003957681605366269</chNFe>
<dhRecbto>2012-10-03T17:35:55</dhRecbto>
<nProt>131120853536488</nProt>
<digVal>jN2ozPH3/GvAS8Q5lh/t9bzuXCw=</digVal>
<cStat>100</cStat>
<xMotivo>Autorizado o uso da NF-e</xMotivo>
</infProt>
</protNFe>
</nfeProc>

Gaff · Oct 4, 2012 5:01PM

Well, first you'll need to get it out of that LONG and into something you can work with. Can you copy the data to a working table of type CLOB? If so, Tom Kyte has some approaches.

http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:510622111991

For the diff, I don't know if there is an existing PL/SQL solution, but apparently there is a Java one.

http://www.oracle.com/technetwork/developer-tools/xmldevkit/index.html

odie_63 · Oct 4, 2012 6:42PM

As Gaff mentioned, the problem is two-fold. Converting the LONG and performing the comparison.
Oracle 11 provides an SQL interface to the Xdiff algorithm, but there's no such straightforward method for 10g. It can probably be done via the Java XDK but requires a Java stored procedure and its PL/SQL wrapper.

I hope you have some latitude for creating objects in the database, otherwise you're out of luck and probably need an out-of-the-box solution.

Here's a possible solution that uses only SQL, and an auxilliary table :

1) Create a staging table (could be temporary if you want) with a CLOB column :

create table tmp_cnv_clob (id number, xmldoc clob);

2) Migrate a first XML document to CLOB :

insert into tmp_cnv_clob (id, xmldoc) 
select id, to_lob(xml1) 
from tab1 
where id = 1;

3) Flatten out the structure so that each leaf node ends up in its own row :

SELECT sys_connect_by_path(
          case when node_kind = 'attribute' then '@' end 
       || node_name
       || case when node_kind = 'element' and sibling_cnt_by_qname > 1 
            then '['||node_pos||']' 
          end
       , '/'
       ) as node_path
     , node_value
FROM (
  SELECT x.*
       , row_number() over(partition by parent_node_id, node_name order by node_id) as node_pos
       , count(*) over(partition by parent_node_id, node_name) as sibling_cnt_by_qname
  FROM tmp_cnv_clob t
     , XMLTable(
       'declare function local:getChildren($e as node(), $pID as xs:string?) as element()*
        {
          for $i at $p in $e/(child::*|attribute::*)
          let $ID := if ($pID) then concat($pID,".",$p) else "1"
          return element r
          {
            element node_id {$ID}
          , element parent_node_id {$pID}
          , element node_name {name($i)}
          , if ($i instance of attribute())
              then ( element node_value {data($i)}, element node_kind {"attribute"} )
              else ( element node_value {$i/text()}, element node_kind {"element"} )
          }
          | local:getChildren($i,$ID)
        }; (: :)
        local:getChildren($d,())'
        passing xmltype(t.xmldoc) as "d"
        columns node_id         varchar2(100)   path 'node_id'
              , node_name       varchar2(30)    path 'node_name'
              , node_value      varchar2(2000)  path 'node_value'
              , parent_node_id  varchar2(100)   path 'parent_node_id'
              , node_kind       varchar2(30)    path 'node_kind'
       ) x
)
WHERE connect_by_isleaf = 1
CONNECT BY PRIOR node_id = parent_node_id
START WITH parent_node_id IS NULL
;

NODE_PATH                                                                        NODE_VALUE
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
/nfeProc/@versao                                                                 2.00
/nfeProc/NFe/infNFe/@Id                                                          NFe31121059106377000172550010003957681605366269
/nfeProc/NFe/infNFe/infAdic/infCpl                                               VIA DE TRANSPORTE RODOVIARIA CODIGO : 108061 PEDIDO NRO : ACIMA FABRICA :72480 R
/nfeProc/NFe/infNFe/@versao                                                      2.00
/nfeProc/NFe/infNFe/ide/cUF                                                      31
/nfeProc/NFe/infNFe/ide/hSaiEnt                                                  18:30:00
/nfeProc/NFe/infNFe/ide/tpNF                                                     1
/nfeProc/NFe/infNFe/ide/cMunFG                                                   3159605
/nfeProc/NFe/infNFe/ide/tpImp                                                    1
/nfeProc/NFe/infNFe/ide/tpEmis                                                   1
/nfeProc/NFe/infNFe/ide/cDV                                                      9
/nfeProc/NFe/infNFe/ide/tpAmb                                                    1
/nfeProc/NFe/infNFe/ide/finNFe                                                   1
/nfeProc/NFe/infNFe/ide/procEmi                                                  0
/nfeProc/NFe/infNFe/ide/verProc                                                  1.0
/nfeProc/NFe/infNFe/ide/cNF                                                      60536626
/nfeProc/NFe/infNFe/ide/natOp                                                    VDAS PROD ESTABELECIMENT

[...]

/nfeProc/NFe/infNFe/cobr/dup/nDup                                                1
/nfeProc/NFe/infNFe/cobr/dup/dVenc                                               2012-11-20
/nfeProc/NFe/infNFe/cobr/dup/vDup                                                2869.60
/nfeProc/NFe/Signature/SignedInfo/CanonicalizationMethod/@Algorithm              http://www.w3.org/TR/2001/REC-xml-c14n-20010315
/nfeProc/NFe/Signature/SignedInfo/SignatureMethod/@Algorithm                     http://www.w3.org/2000/09/xmldsig#rsa-sha1
/nfeProc/NFe/Signature/SignedInfo/Reference/@URI                                 #NFe31121059106377000172550010003957681605366269
/nfeProc/NFe/Signature/SignedInfo/Reference/Transforms/Transform[1]/@Algorithm   http://www.w3.org/2000/09/xmldsig#enveloped-signature
/nfeProc/NFe/Signature/SignedInfo/Reference/Transforms/Transform[2]/@Algorithm   http://www.w3.org/TR/2001/REC-xml-c14n-20010315
/nfeProc/NFe/Signature/SignedInfo/Reference/DigestMethod/@Algorithm              http://www.w3.org/2000/09/xmldsig#sha1
/nfeProc/NFe/Signature/SignedInfo/Reference/DigestValue                          jN2ozPH3/GvAS8Q5lh/t9bzuXCw=
/nfeProc/NFe/Signature/SignatureValue                                            GAXPLvMCtIYdwMxXDcyL0kr5hCDPCFw8/uNYHFcdTMqBhLgIcEtzHRf8qioWlUVSHNf5jnCLKGjhDV4b
/nfeProc/NFe/Signature/KeyInfo/X509Data/X509Certificate                          MIIGajCCBVKgAwIBAgIIaHrIAHUBA4wwDQYJKoZIhvcNAQEFBQAwdTELMAkGA1UEBhMCQlIxEzARBgNV
/nfeProc/protNFe/@versao                                                         2.00
/nfeProc/protNFe/infProt/tpAmb                                                   1
/nfeProc/protNFe/infProt/verAplic                                                13_0_32
/nfeProc/protNFe/infProt/chNFe                                                   31121059106377000172550010003957681605366269
/nfeProc/protNFe/infProt/dhRecbto                                                2012-10-03T17:35:55
/nfeProc/protNFe/infProt/nProt                                                   131120853536488
/nfeProc/protNFe/infProt/digVal                                                  jN2ozPH3/GvAS8Q5lh/t9bzuXCw=
/nfeProc/protNFe/infProt/cStat                                                   100
/nfeProc/protNFe/infProt/xMotivo                                                 Autorizado o uso da NF-e
 
138 rows selected

4) Do the same thing for the 2nd document you want to compare, and join the two result sets on the NODE_PATH column.

509198 · Oct 5, 2012 9:33AM

ok Odie your solution is perfect for me.

Thank you very much!

Fernando.

Forum Stats

Quick Links

Categories

How can i compare two XML files storeds in a LONG column

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

How can i compare two XML files storeds in a LONG column

Answers