Using Oracle 11g R2 and SQL Developer, I have the following XML file:
<?xml version='1.0' encoding='UTF-8'?><S:Envelope xmlns:S="http://schemas.xmlsoap.org/soap/envelope/"><S:Body><ns0:downloadObjectResponse xmlns:ns0="https://ws.exporter.XXXX.YYY.ZZZ/"><return>JVBERi0xLjYNCjUgMCBvYmoNCjw8DQovVHlwZSAvUGFnZQ0KL1BhcmVudCAzIDAgUg0KL1Jlc291cmNlcyA0IDAgUg0KL0NvbnRlbnRzIDYgMCBSDQovTWVkaWFCb3hbIDAgMCA3OTIuMCA2MTIuMCBdDQovQ3JvcEJveFsgMCAwIDc5Mi4wIDYxMi4wIF0NCi9Sb3RhdGUgMA0KPj4NCmVuZG9iag0KNiAwIG9iag0KPDwgL0xlbmd0aCAxNTgwIC9GaWx0ZXIgL0ZsYXRlRGVjb2RlID4+DQpzdHJlYW0NCnictVldb9s2FH0PkP/Ap8EDZo3fH49JGhcD2sTIvBV7dBMldeGPQHVW5N/vUqJjkiJVU9tSoK19D6XDey/PPVIwwvDn7n37D0bN0/nZ5eL8jLSfCGKEVUoiIUmFsUaLzfnZrzNyfoaIRIvH87PJ3Sd0U39Hs5ftw2r7hP5c1d9/Xnw9XoBXAglMK4mlW03tat6tvnppmnq7PyxvV1YGC1QZQeEvQy216BvL8Ro42ktzbirDLU/4wGSlBWpqBJfGw/uCVYgrXSnFPFq6Y4UuZnMU7KKFS1NR0cEnf9XLpowtk29kOS4jy0nFA7aTHsEO4jNcrPbruoyikn5GZQlFTaKETmxJQ44dxud4tXsopEgI/RdVJ0SlaAYsHSZI5etzKU3Oj9U2hSRhgS03PpKcN7unZrkp5GCYlyrBizgY3SbK43CJfkJ3ZQwoVx4DXpQFKmjMALLwtb7fF3IwdDwHo2IOn2a3hcee+TkgmJU1A+M07lj0oX5a3r+i2+ahboLWdWC/ddHNy+ZzXShV3GZqrFZxGYvV5Wq9Xn52YhThfK63898KeWri5VayIp5axomd757RdaQGDhak9N1yXygHgvPRR1HweFZNLnevobI6jM/yYlN4UAQc+bEHRUJbxxyXIUUH8SnOm/rvJEiYSrGx+5Dcl71S8ZVc/3AnHSSYY1+eUDlRw440WVXUvRKKFZ+ymGYH8WneQb6LaSqgxbzeLSOqwJiouHtDog6Sat7AfoG4ESM8C+rcGsVEhjKoKyICvPVLC5kwIz4GY0Nwygr4ILYIEd2s9hEKww8lIawbpz6MC0w4WOqQlZt6Ia32J4SZHjGYLFpxlZoHPuwGLsUwER/n8B+hU5Ic3BywKT0MUkKm766vplERnCQFFGm7k0qZ1KH3kVPR37M7dgG7VMP/6Dqu3Xole+N1cC4UeggLQBEEGgL98tbzfCBm5ScftfY1H7VeZeCu8CEfBe+eDzJRqXyQDe1VAqf8WisOOM8YzEN+rfWn+fvacTSQR8hy/sp23A5kyuCKnVJbpKDZpQX4IehrmQnBdwI0MooFrsNOI+BujPo/noYZhsmpqbOZxJ6Hk5+GGcUVkTzxNOx89ygrSbB+Y2V4ESlCGEB9WvBg/viMyh+9KaZecowpIEGxinIzQdZ+FY9RENKgQLqABMOyT2KJWv9UTIRj4yejrCac8BSTUf5HYD66YQU2vd4AHqMMjsTKr0w7809nIglNNAhKORgGbYiZ7DuYC2Z+x7H1aJvfW9H3Oa43fUx4Edc3PmBKImviKupjCKXhKO+SHUDCydvloA/oFEBZqaYaBi6jVhbBZlA4hJ5yZoPYDIUVFdbp5cL22A+sFpgNEbMHdmC17Zoh5jZoe0pAUFH7di4cFoxC8TKxw0WjWDxIKDSdYToxSOwb2Yv1ene/3K92W/Rhta2/lU8TokUlWsG0pAjspWCcELCoBP/346St2YEZb51+ATMCFWlXHMkl3vYeUBqEQfOxL3yJIK2SOKrKeowSqkLBo1bIdNTkIVA/L2cS2rSEB8Win7LR04dKAV8eybDC1qKKWPPWYzNqAjFqbC4OXGBAF3FhjIPP61EZNYSYwUFjg/qXcTEyUaXdCHsAmzrmRLPCA8aZ6eXkcOBny/sRLgHmMePHc6S6659MSND2+SU68VYb0dVuswG7XfqaTPoNDBKBixIkYSj1GviqqTulLn+1KEGgjkJdxETbsR4Smb2s1+hmual75oUoEDCmTjMvTj+9JX334vTNx0QvOZzw+IiefTnogQ+K/Ys7pwEkBLjDk0ccushHLJrl9ttj3aDHZrdBSbjUUBcd/PYCdflC+116iTDQ7iS1JHppQvv7ltOLP97HL2JcmX3gx9V6XTe/oMVu+wrK2ay+7XfPX6JfJrRF56DWh1UDhfZhuUL7mHShfUS6yj5imilzhElXehB0KIUPOqHYDJ76tSkqNnhAXlDsIEFDxfaBg8XuFJ+1igZmUzDWvg9h0CtvRld3lisTbg1rLmjFUmWj1pjk18LD2NB97cAcuDFMDWzlMLea6qE9u8fRXBSMncyHrcXJb7oz+JhXWsEwUyay/t2yZESAJ4ALJiIg/yyO/APGZcUoDQplbmRzdHJlYW0NCmVuZG9iag0KMSAwIG9iag0KPDwNCi9UeXBlIC9DYXRhbG9nDQovUGFnZXMgMyAwIFINCj4+DQplbmRvYmoNCjIgMCBvYmoNCjw8DQovVHlwZSAvSW5mbw0KL1Byb2R1Y2VyIChPcmFjbGUgQkkgUHVibGlzaGVyIDExLjEuMS43LjE2MDcxOSkNCj4+DQplbmRvYmoNCjMgMCBvYmoNCjw8DQovVHlwZSAvUGFnZXMNCi9LaWRzIFsNCjUgMCBSDQpdDQovQ291bnQgMQ0KPj4NCmVuZG9iag0KNCAwIG9iag0KPDwNCi9Qcm9jU2V0IFsgL1BERiAvVGV4dCBdDQovRm9udCA8PCANCi9GMSA3IDAgUg0KL0YyIDggMCBSDQo+Pg0KPj4NCmVuZG9iag0KNyAwIG9iag0KPDwNCi9UeXBlIC9Gb250DQovU3VidHlwZSAvVHlwZTENCi9CYXNlRm9udCAvSGVsdmV0aWNhDQovRW5jb2RpbmcgL1dpbkFuc2lFbmNvZGluZw0KPj4NCmVuZG9iag0KOCAwIG9iag0KPDwNCi9UeXBlIC9Gb250DQovU3VidHlwZSAvVHlwZTENCi9CYXNlRm9udCAvSGVsdmV0aWNhLUJvbGQNCi9FbmNvZGluZyAvV2luQW5zaUVuY29kaW5nDQo+Pg0KZW5kb2JqDQo5IDAgb2JqDQpbIDUgMCBSIC9YWVogNzIuMCA5Ny43MzIgbnVsbCBdDQplbmRvYmoNCjEwIDAgb2JqDQpbIDUgMCBSIC9YWVogNzIuMCA5Ny43MzIgbnVsbCBdDQplbmRvYmoNCnhyZWYNCjAgMTENCjAwMDAwMDAwMDAgNjU1MzUgZg0KMDAwMDAwMTgyNyAwMDAwMCBuDQowMDAwMDAxODgyIDAwMDAwIG4NCjAwMDAwMDE5NjkgMDAwMDAgbg0KMDAwMDAwMjAzNyAwMDAwMCBuDQowMDAwMDAwMDEwIDAwMDAwIG4NCjAwMDAwMDAxNjggMDAwMDAgbg0KMDAwMDAwMjEyNCAwMDAwMCBuDQowMDAwMDAyMjI5IDAwMDAwIG4NCjAwMDAwMDIzMzkgMDAwMDAgbg0KMDAwMDAwMjM4OSAwMDAwMCBuDQp0cmFpbGVyDQo8PA0KL1NpemUgMTENCi9Sb290IDEgMCBSDQovSW5mbyAyIDAgUg0KL0lEIFs8YzRmOWYxMDFkNWY5MjAwNTAyMGZiYjcxM2FkMTAzMjg+PGM0ZjlmMTAxZDVmOTIwMDUwMjBmYmI3MTNhZDEwMzI4Pl0NCj4+DQpzdGFydHhyZWYNCjI0NDANCiUlRU9GDQo=</return></ns0:downloadObjectResponse></S:Body></S:Envelope>
I had been trying to use a dbms_lob.substr to get the contents of the xml file that are inclusive to the <return> </return> tag but found that when this exceeded 32k, I could not extract it out.
Can anyone show me the proper syntax for using an XMLTABLE in a select to extract this out? The data is a base64 representation of a pdf being returned from a web service call..
Thank you,
Tony MIller
Los Alamos, NM