Skip to Main Content
Forums

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

To extract unique values of recurring tag from binary BLOB xml file

3228936Nov 11 2017 — edited Nov 12 2017

Hello All,

Could you please help me to understand how can i fetch specific tag values (also by skipping duplicate values) from a huge BLOB file. Is there a way to achieve this by using PL/SQL?

I tried but unable to figure out where i am missing the logic. "fetching the few values of XML tags inside the BLOB content" by using SQL / PLSQL.

Here are the details.

I have a BLOB file (which is too lengthy.. sometimes it will be of 1000 lines) and it is stored in one of the column in my database.

Table: NES.WLI_DB

ID Varchar(30)

Column: Data_value (BLOB TYPE)

I am trying to parse through the whole content to fetch the values of <MATNR> tag.

EX: <MATNR>000000000012268648</MATNR> [ i have fetch all this kind of values (only last 8 digits) and to ignore duplicates]

But, i am neither able to do it by SQL (could able to fetch only one) and nor able to apply cursor to scan through the BLOB content to fetch repeating values.

Queries i tried: Below one will give only first value

SELECT dbms_output.put_line(utl_raw.cast_to_varchar2( dbms_lob.substr(DATA_VALUE, 8,DBMS_LOB.INSTR(DATA_VALUE,utl_raw.cast_to_raw('<MATNR>000000000'),1,1)+17)) FROM NES.WLI_DB WHERE ID = '0000000331756259';

Below is also not working

select distinct MANTR from NES.WLI_DB t,

  xmltable('//MATNR' passing xmltype(t.DATA_VALUE,1)

   columns mantr varchar2(2000) path 'text()')

   where t.id = '0000000331756259';

Payload:

<?xml version="1.0" encoding="UTF-8"?>

<_-GLB_-OGTX_DESADV01_CUST_ASN xmlns:SOAP="http://schemas.xmlsoap.org/soap/envelope/">

<IDOC BEGIN="1">

<EDI_DC40 SEGMENT="1">

<TABNAM>EDI_DC40</TABNAM>

<MANDT>103</MANDT>

<DOCNUM>0000000331756259</DOCNUM>

<DOCREL>620</DOCREL>

<STATUS>30</STATUS>

<DIRECT>1</DIRECT>

<OUTMOD>2</OUTMOD>

<IDOCTYP>DESADV01</IDOCTYP>

<CIMTYP>/GLB/OGTX_DESADV01_CUST_ASN</CIMTYP>

<MESTYP>DESADV</MESTYP>

<SNDPOR>SAPE9A</SNDPOR>

<SERIAL>20171024075346</SERIAL>

</EDI_DC40>

<E1EDK08 SEGMENT="1">

<ACTION>000</ACTION>

<VBELN>0820046682</VBELN>

<E1EDP07 SEGMENT="1">

<BSTNK>LDP5877367</BSTNK>

<POSEX>000010</POSEX>

<E1EDP09 SEGMENT="1">

<VBELN>0820046682</VBELN>

<POSNR>000010</POSNR>

<MATNR>000000000012268648</MATNR>

<WERKS>8819</WERKS>

<LGORT>0001</LGORT>

<CHARG>7186T11101</CHARG>

<LFIMG>1.000</LFIMG>

<VRKME>CS</VRKME>

<BTGEW>250.000</BTGEW>

<NTGEW>200.000</NTGEW>

<GEWEI>GRM</GEWEI>

<VOLUM>3.020</VOLUM>

<VOLEH>DMQ</VOLEH>

<ARKTX>Aspiration Tube LATTISSIMA+ (50Pc) XI</ARKTX>

<EANNR>7640154060593</EANNR>

<STAWN>85169000</STAWN>

<EXART>1</EXART>

<HERKL>CH</HERKL>

<GRWRT>95.13</GRWRT>

<DOKPF>X</DOKPF>

</E1EDP09>

</E1EDP07>

<E1EDP07 SEGMENT="1">

<BSTNK>LDP5877367</BSTNK>

<POSEX>000020</POSEX>

<E1EDP09 SEGMENT="1">

<VBELN>0820046682</VBELN>

<POSNR>000020</POSNR>

<MATNR>000000000012350651</MATNR>

<WERKS>8819</WERKS>

<LGORT>0001</LGORT>

<LFIMG>56.000</LFIMG>

<VRKME>CS</VRKME>

<BTGEW>218.400</BTGEW>

<NTGEW>217.280</NTGEW>

<GEWEI>KGM</GEWEI>

<VOLUM>1563.184</VOLUM>

<VOLEH>DMQ</VOLEH>

<ARKTX>Case Corr NESPRS 250Caps 20PcN1XI</ARKTX>

<EANNR>7630039622299</EANNR>

<STAWN>48191000</STAWN>

<EXART>1</EXART>

<HERKL>CH</HERKL>

<GRWRT>405.46</GRWRT>

</E1EDP09>

</E1EDP07>

<E1EDP07 SEGMENT="1">

<BSTNK>LDP5877367</BSTNK>

<POSEX>000030</POSEX>

<E1EDP09 SEGMENT="1">

<VBELN>0820046682</VBELN>

<POSNR>000030</POSNR>

<MATNR>000000000012302120</MATNR>

<WERKS>8819</WERKS>

<LGORT>0001</LGORT>

<CHARG>7242T182M</CHARG>

<LFIMG>7.000</LFIMG>

<VRKME>CS</VRKME>

<BTGEW>9.590</BTGEW>

<NTGEW>8.400</NTGEW>

<GEWEI>KGM</GEWEI>

<VOLUM>29.400</VOLUM>

<VOLEH>DMQ</VOLEH>

<ARKTX>NESPRS Sugar Sachet 1.2kg (300pc) N1 XI</ARKTX>

<EANNR>7630030392412</EANNR>

<STAWN>17019990</STAWN>

<EXART>1</EXART>

<HERKL>NL</HERKL>

<GRWRT>16.82</GRWRT>

<DOKPF>X</DOKPF>

</E1EDP09>

</E1EDP07>

<E1EDP07 SEGMENT="1">

<BSTNK>LDP5877367</BSTNK>

<POSEX>000040</POSEX>

<E1EDP09 SEGMENT="1">

<VBELN>0820046682</VBELN>

<POSNR>000040</POSNR>

<MATNR>000000000012268362</MATNR>

<WERKS>8819</WERKS>

<LGORT>0001</LGORT>

<LFIMG>0.000</LFIMG>

<VRKME>CS</VRKME>

<BTGEW>0.000</BTGEW>

<NTGEW>0.000</NTGEW>

<VOLUM>0.000</VOLUM>

<ARKTX>NESPRS Pro DspbPapCup 40(55x175ml/6oz)XI</ARKTX>

<EANNR>7640145295027</EANNR>

<STAWN>48236910</STAWN>

<EXART>1</EXART>

<HERKL>CN</HERKL>

<GRWRT>70.25</GRWRT>

<DOKPF>X</DOKPF>

</E1EDP09>

<E1EDP09 SEGMENT="1">

<VBELN>0820046682</VBELN>

<POSNR>900001</POSNR>

<MATNR>000000000012268362</MATNR>

<WERKS>8819</WERKS>

<LGORT>0001</LGORT>

<CHARG>7212T145NA</CHARG>

<LFIMG>1.000</LFIMG>

<VRKME>CS</VRKME>

<BTGEW>12.000</BTGEW>

<NTGEW>11.600</NTGEW>

<GEWEI>KGM</GEWEI>

<VOLUM>91.210</VOLUM>

<VOLEH>DMQ</VOLEH>

<ARKTX>NESPRS Pro DspbPapCup 40(55x175ml/6oz)XI</ARKTX>

<EANNR>7640145295027</EANNR>

<STAWN>48236910</STAWN>

<EXART>1</EXART>

<HERKL>CN</HERKL>

<DOKPF>X</DOKPF>

</E1EDP09>

<E1EDP09 SEGMENT="1">

<VBELN>0820046682</VBELN>

<POSNR>900002</POSNR>

<MATNR>000000000012268362</MATNR>

<WERKS>8819</WERKS>

<LGORT>0001</LGORT>

<CHARG>7218T145NA</CHARG>

<LFIMG>1.000</LFIMG>

<VRKME>CS</VRKME>

<BTGEW>12.000</BTGEW>

<NTGEW>11.600</NTGEW>

<GEWEI>KGM</GEWEI>

<VOLUM>91.210</VOLUM>

<VOLEH>DMQ</VOLEH>

<ARKTX>NESPRS Pro DspbPapCup 40(55x175ml/6oz)XI</ARKTX>

<EANNR>7640145295027</EANNR>

<STAWN>48236910</STAWN>

<EXART>1</EXART>

<HERKL>CN</HERKL>

<DOKPF>X</DOKPF>

</E1EDP09>

</E1EDP07>

<E1EDP07 SEGMENT="1">

<BSTNK>LDP5877367</BSTNK>

<POSEX>000050</POSEX>

<E1EDP09 SEGMENT="1">

<VBELN>0820046682</VBELN>

<POSNR>000050</POSNR>

<MATNR>000000000012268443</MATNR>

<WERKS>8819</WERKS>

<LGORT>0001</LGORT>

<CHARG>6363T145NA</CHARG>

<LFIMG>1.000</LFIMG>

<VRKME>CS</VRKME>

<BTGEW>6.120</BTGEW>

<NTGEW>6.120</NTGEW>

<GEWEI>KGM</GEWEI>

<VOLUM>88.510</VOLUM>

<VOLEH>DMQ</VOLEH>

<ARKTX>NESPRS TakeAwayCupLid36(50x350ml/12oz)XI</ARKTX>

<EANNR>7630030309182</EANNR>

<STAWN>39235090</STAWN>

<EXART>1</EXART>

<HERKL>CN</HERKL>

<GRWRT>44.09</GRWRT>

<DOKPF>X</DOKPF>

</E1EDP09>

</E1EDP07>

<E1EDP07 SEGMENT="1">

<BSTNK>LDP5877367</BSTNK>

<POSEX>000060</POSEX>

<E1EDP09 SEGMENT="1">

<VBELN>0820046682</VBELN>

<POSNR>000060</POSNR>

<MATNR>000000000012268357</MATNR>

<WERKS>8819</WERKS>

<LGORT>0001</LGORT>

<CHARG>7125T145NA</CHARG>

<LFIMG>2.000</LFIMG>

<VRKME>CS</VRKME>

<BTGEW>22.800</BTGEW>

<NTGEW>20.844</NTGEW>

<GEWEI>KGM</GEWEI>

<VOLUM>151.160</VOLUM>

<VOLEH>DMQ</VOLEH>

<ARKTX>NESPRS Pro DspbPapCup 54(55x100ml/4oz)XI</ARKTX>

<EANNR>7640145295003</EANNR>

<STAWN>48236990</STAWN>

<EXART>1</EXART>

<HERKL>CN</HERKL>

<GRWRT>70.71</GRWRT>

<DOKPF>X</DOKPF>

</E1EDP09>

</E1EDP07>

<E1EDP07 SEGMENT="1">

<BSTNK>LDP5877367</BSTNK>

<POSEX>000070</POSEX>

<E1EDP09 SEGMENT="1">

<VBELN>0820046682</VBELN>

<POSNR>000070</POSNR>

<MATNR>000000000012268440</MATNR>

<WERKS>8819</WERKS>

<LGORT>0001</LGORT>

<CHARG>6360T145NA</CHARG>

<LFIMG>1.000</LFIMG>

<VRKME>CS</VRKME>

<BTGEW>2.520</BTGEW>

<NTGEW>2.400</NTGEW>

<GEWEI>KGM</GEWEI>

<VOLUM>34.130</VOLUM>

<VOLEH>DMQ</VOLEH>

<ARKTX>NESPRS Take AwayCupLid24(50x100ml/4oz)XI</ARKTX>

<EANNR>7630030309120</EANNR>

<STAWN>39235090</STAWN>

<EXART>1</EXART>

<HERKL>CN</HERKL>

<GRWRT>16.82</GRWRT>

<DOKPF>X</DOKPF>

</E1EDP09>

</E1EDP07>

<E1EDP07 SEGMENT="1">

<BSTNK>LDP5877367</BSTNK>

<POSEX>000080</POSEX>

<E1EDP09 SEGMENT="1">

<VBELN>0820046682</VBELN>

<POSNR>000080</POSNR>

<MATNR>000000000012346813</MATNR>

<WERKS>8819</WERKS>

<LGORT>0001</LGORT>

<CHARG>0000226876</CHARG>

<LFIMG>192.000</LFIMG>

<VRKME>CS</VRKME>

<BTGEW>345.600</BTGEW>

<NTGEW>341.760</NTGEW>

<GEWEI>KGM</GEWEI>

<VOLUM>2508.864</VOLUM>

<VOLEH>DMQ</VOLEH>

<ARKTX>NESPRS Case Corr Carton 100Caps 20Pc XI</ARKTX>

<EANNR>7630039622237</EANNR>

<STAWN>48191000</STAWN>

<EXART>1</EXART>

<HERKL>CH</HERKL>

<GRWRT>769.01</GRWRT>

</E1EDP09>

</E1EDP07>

<E1EDP07 SEGMENT="1">

<BSTNK>LDP5877367</BSTNK>

<POSEX>000090</POSEX>

<E1EDP09 SEGMENT="1">

<VBELN>0820046682</VBELN>

<POSNR>000090</POSNR>

<MATNR>000000000012346816</MATNR>

<WERKS>8819</WERKS>

<LGORT>0001</LGORT>

<LFIMG>60.000</LFIMG>

<VRKME>CS</VRKME>

<BTGEW>258.000</BTGEW>

<NTGEW>256.800</NTGEW>

<GEWEI>KGM</GEWEI>

<VOLUM>1946.280</VOLUM>

<VOLEH>DMQ</VOLEH>

<ARKTX>NESPRS Case Corr Carton 300Caps 20Pc XI</ARKTX>

<EANNR>7630039622312</EANNR>

<STAWN>48191000</STAWN>

<EXART>1</EXART>

<HERKL>CH</HERKL>

<GRWRT>536.09</GRWRT>

</E1EDP09>

</E1EDP07>

<E1EDP07 SEGMENT="1">

<BSTNK>LDP5877367</BSTNK>

<POSEX>000100</POSEX>

<E1EDP09 SEGMENT="1">

<VBELN>0820046682</VBELN>

<POSNR>000100</POSNR>

<MATNR>000000000012268003</MATNR>

<WERKS>8819</WERKS>

<LGORT>0001</LGORT>

<CHARG>7206T158M3</CHARG>

<LFIMG>7.000</LFIMG>

<VRKME>CS</VRKME>

<BTGEW>42.000</BTGEW>

<NTGEW>30.240</NTGEW>

<GEWEI>KGM</GEWEI>

<VOLUM>417.970</VOLUM>

<VOLEH>DMQ</VOLEH>

<ARKTX>NESPRS Recycling Bin (6Pc) XI</ARKTX>

<EANNR>7640128875529</EANNR>

<STAWN>39249000</STAWN>

<EXART>1</EXART>

<HERKL>CN</HERKL>

<GRWRT>490.76</GRWRT>

<DOKPF>X</DOKPF>

</E1EDP09>

</E1EDP07>

<E1EDP07 SEGMENT="1">

<BSTNK>LDP5877367</BSTNK>

<POSEX>000110</POSEX>

<E1EDP09 SEGMENT="1">

<VBELN>0820046682</VBELN>

<POSNR>000110</POSNR>

<MATNR>000000000012344018</MATNR>

<WERKS>8819</WERKS>

<LGORT>0001</LGORT>

<CHARG>0000226590</CHARG>

<LFIMG>408.000</LFIMG>

<VRKME>CS</VRKME>

<BTGEW>530.400</BTGEW>

<NTGEW>514.080</NTGEW>

<GEWEI>KGM</GEWEI>

<VOLUM>2443.920</VOLUM>

<VOLEH>DMQ</VOLEH>

<ARKTX>NESPRS Case Corr Wedge 50Caps 30Pc XI</ARKTX>

<EANNR>7630039697693</EANNR>

<STAWN>48191000</STAWN>

<EXART>1</EXART>

<HERKL>CH</HERKL>

<GRWRT>1602.73</GRWRT>

</E1EDP09>

</E1EDP07>

<E1EDP07 SEGMENT="1">

<BSTNK>LDP5877367</BSTNK>

<POSEX>000120</POSEX>

<E1EDP09 SEGMENT="1">

<VBELN>0820046682</VBELN>

<POSNR>000120</POSNR>

<MATNR>000000000012344319</MATNR>

<WERKS>8819</WERKS>

<LGORT>0001</LGORT>

<CHARG>7269T158M3</CHARG>

<LFIMG>1.000</LFIMG>

<VRKME>CS</VRKME>

<BTGEW>4.970</BTGEW>

<NTGEW>3.040</NTGEW>

<GEWEI>KGM</GEWEI>

<VOLUM>43.435</VOLUM>

<VOLEH>DMQ</VOLEH>

<ARKTX>NESPRS VERTUO Discovery Empty Box 4Pc XI</ARKTX>

<EANNR>7630030358791</EANNR>

<STAWN>39249000</STAWN>

<EXART>1</EXART>

<HERKL>FR</HERKL>

<GRWRT>57.09</GRWRT>

<DOKPF>X</DOKPF>

</E1EDP09>

</E1EDP07>

<E1EDP07 SEGMENT="1">

<BSTNK>LDP5877367</BSTNK>

<POSEX>000130</POSEX>

<E1EDP09 SEGMENT="1">

<VBELN>0820046682</VBELN>

<POSNR>000130</POSNR>

<MATNR>000000000012331366</MATNR>

<WERKS>8819</WERKS>

<LGORT>0001</LGORT>

<LFIMG>0.000</LFIMG>

<VRKME>CS</VRKME>

<BTGEW>0.000</BTGEW>

<NTGEW>0.000</NTGEW>

<VOLUM>0.000</VOLUM>

<ARKTX>NESPRS Welcome Set Coff 14 Caps C 60PcXI</ARKTX>

<EANNR>7630039666927</EANNR>

<STAWN>09012100</STAWN>

<EXART>1</EXART>

<HERKL>CH</HERKL>

<GRWRT>909.51</GRWRT>

<DOKPF>X</DOKPF>

</E1EDP09>

<E1EDP09 SEGMENT="1">

<VBELN>0820046682</VBELN>

<POSNR>900003</POSNR>

<MATNR>000000000012331366</MATNR>

<WERKS>8819</WERKS>

<LGORT>0001</LGORT>

<CHARG>72518861A2</CHARG>

<LFIMG>7.000</LFIMG>

<VRKME>CS</VRKME>

<BTGEW>56.000</BTGEW>

<NTGEW>30.240</NTGEW>

<GEWEI>KGM</GEWEI>

<VOLUM>415.296</VOLUM>

<VOLEH>DMQ</VOLEH>

<ARKTX>NESPRS Welcome Set Coff 14 Caps C 60PcXI</ARKTX>

<EANNR>7630039666927</EANNR>

<STAWN>09012100</STAWN>

<EXART>1</EXART>

<HERKL>CH</HERKL>

<DOKPF>X</DOKPF>

</E1EDP09>

<E1EDP09 SEGMENT="1">

<VBELN>0820046682</VBELN>

<POSNR>900004</POSNR>

<MATNR>000000000012331366</MATNR>

<WERKS>8819</WERKS>

<LGORT>0001</LGORT>

<CHARG>72598861</CHARG>

<LFIMG>1.000</LFIMG>

<VRKME>CS</VRKME>

<BTGEW>8.000</BTGEW>

<NTGEW>4.320</NTGEW>

<GEWEI>KGM</GEWEI>

<VOLUM>59.328</VOLUM>

<VOLEH>DMQ</VOLEH>

<ARKTX>NESPRS Welcome Set Coff 14 Caps C 60PcXI</ARKTX>

<EANNR>7630039666927</EANNR>

<STAWN>09012100</STAWN>

<EXART>1</EXART>

<HERKL>CH</HERKL>

<DOKPF>X</DOKPF>

</E1EDP09>

</E1EDP07>

<E1EDP07 SEGMENT="1">

<BSTNK>LDP5877367</BSTNK>

<POSEX>000530</POSEX>

<E1EDP09 SEGMENT="1">

<VBELN>0820046682</VBELN>

<POSNR>000520</POSNR>

<MATNR>000000000012268585</MATNR>

<WERKS>8819</WERKS>

<LGORT>0001</LGORT>

<CHARG>7273T140CV</CHARG>

<LFIMG>1.000</LFIMG>

<VRKME>CS</VRKME>

<BTGEW>11.300</BTGEW>

<NTGEW>9.440</NTGEW>

<GEWEI>KGM</GEWEI>

<VOLUM>39.150</VOLUM>

<VOLEH>DMQ</VOLEH>

<ARKTX>NESPRS Pro EspCup 8(12x100ml/4oz) N2 XI</ARKTX>

<EANNR>7640154065543</EANNR>

<STAWN>69111000</STAWN>

<EXART>1</EXART>

<HERKL>PT</HERKL>

<GRWRT>82.63</GRWRT>

<DOKPF>X</DOKPF>

</E1EDP09>

</E1EDP07>

<E1EDP07 SEGMENT="1">

<BSTNK>LDP5877367</BSTNK>

<POSEX>000540</POSEX>

<E1EDP09 SEGMENT="1">

<VBELN>0820046682</VBELN>

<POSNR>000530</POSNR>

<MATNR>000000000012350683</MATNR>

<WERKS>8819</WERKS>

<LGORT>0001</LGORT>

<LFIMG>40.000</LFIMG>

<VRKME>CS</VRKME>

<BTGEW>852.000</BTGEW>

<NTGEW>740.000</NTGEW>

<GEWEI>KGM</GEWEI>

<VOLUM>4080.000</VOLUM>

<VOLEH>DMQ</VOLEH>

<ARKTX>NESPRS Paper Bag Corp Med 2012 (250Pc)XI</ARKTX>

<STAWN>48194000</STAWN>

<EXART>1</EXART>

<HERKL>IT</HERKL>

<GRWRT>2098.77</GRWRT>

<ABRDT>00000000</ABRDT>

<_-GLB_-OGT_E1EDP09_01 SEGMENT="1">

<ORDQTY>40.000</ORDQTY>

<ORDUNIT>CS</ORDUNIT>

<QTYFIELD1>40.000</QTYFIELD1>

<UOMFIELD1>CS</UOMFIELD1>

<QTYFIELD2>40.000</QTYFIELD2>

<UOMFIELD2>CS</UOMFIELD2>

</_-GLB_-OGT_E1EDP09_01>

<_-GLB_-RGT_DYN_SEG_05 SEGMENT="1">

<QUALIFIER>CSPERLAY</QUALIFIER>

<FIELDVAL1>4.000</FIELDVAL1>

</_-GLB_-RGT_DYN_SEG_05>

<_-GLB_-RGT_DYN_SEG_05 SEGMENT="1">

<QUALIFIER>LAYPERPAL</QUALIFIER>

<FIELDVAL1>4.000</FIELDVAL1>

</_-GLB_-RGT_DYN_SEG_05>

</E1EDP09>

</E1EDP07>

</E1EDK08>

<E1EDS02 SEGMENT="1">

<SUMID>001</SUMID>

<SUMME>65</SUMME>

</E1EDS02>

</IDOC>

</_-GLB_-OGTX_DESADV01_CUST_ASN>
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 10 2017
Added on Nov 11 2017
#performance-availability, #xml-db
6 comments
3,485 views