2 Replies Latest reply: Aug 8, 2013 10:47 AM by Marwim RSS

    Parse XML that is stored in portions in VARCHAR2(4000)

    Marwim

      Hello,

       

      I have to query data from a third party application, so don't blame me for the data model :-)

       

      The XML is stored in a VARCHAR2(4000) column and when it has more then 4000 characters it is split into parts with a sequence to identify the order.

       

      Example:

       

      DROP TABLE junk_xml;
      CREATE TABLE junk_xml(
          id       INTEGER -- content id
         ,nr       INTEGER -- content sequence
         ,fragment VARCHAR2(4000)
         );
      INSERT INTO junk_xml (id,nr,fragment)
      VALUES (0,1,'<?xml version="1.0" encoding="UTF-8"?>
      <Document xmlns="urn:iso:std:iso:20022:tech:xsd:camt.053.001.02" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn:iso:std:iso:20022:tech:xsd:camt.053.001.02 camt.053.001.02.xsd">
          <BkToCstmrStmt>
              <GrpHdr>
                  <MsgId>27632364572</MsgId>
                  <CreDtTm>2008-09-01T19:30:47.0+01:00</CreDtTm>
                  <MsgRcpt>
                      <Id>
                          <OrgId>
                              <Othr>
                                  <Id>BCS45678</Id>
                              </Othr>
                          </OrgId>
                      </Id>
                  </MsgRcpt>
                  <MsgPgntn>
                      <PgNb>1</PgNb>
                      <LastPgInd>true</LastPgInd>
                  </MsgPgntn>
              </GrpHdr>
              <Stmt>
                  <Id>2736482736482</Id>
                  <ElctrncSeqNb>101</ElctrncSeqNb>
                  <LglSeqNb>32</LglSeqNb>
                  <CreDtTm>2008-09-01T17:30:47.0+01:00</CreDtTm>
                  <Acct>
                      <Id>
                          <IBAN>DE62210500001234567890</IBAN>
                      </Id>
                      <Ccy>EUR</Ccy>
                      <Ownr>
                          <Nm>Name Kontoinhaber</Nm>
                      </Ownr>
                      <Svcr>
                          <FinInstnId>
                              <BIC>BANKDEFFXXX</BIC>
                              <Othr>
                                  <Id>123456789</Id>
                                  <Issr>UmsStId</Issr>
                              </Othr>
                          </FinInstnId>
                      </Svcr>
                  </Acct>
                  <Bal>
                      <Tp>
                          <CdOrPrtry>
                              <Cd>PRCD</Cd>
                          </CdOrPrtry>
                      </Tp>
                      <Amt Ccy="EUR">112.72</Amt>
                      <CdtDbtInd>CRDT</CdtDbtInd>
                      <Dt>
                          <Dt>2008-09-01</Dt>
                      </Dt>
                  </Bal>
                  <Bal>
                      <Tp>
                          <CdOrPrtry>
                              <Cd>CLBD</Cd>
                          </CdOrPrtry>
                      </Tp>
                      <Amt Ccy="EUR">158780.32</Amt>
                      <CdtDbtInd>CRDT</CdtDbtInd>
                      <Dt>
                          <Dt>2008-09-01</Dt>
                      </Dt>
                  </Bal>
                  <!-- Beispiel 1: SEPA-Zahlungen (Ueberweisung, Lastschrift, R-Nachricht -->
                  <!--Gutschrift aufgrund eines SEPA-Ueberweisungseinganges-->
                  <Ntry>
                      <Amt Ccy="EUR">100.00</Amt>
                      <CdtDbtInd>CRDT</CdtDbtInd>
                      <Sts>BOOK</Sts>
                      <BookgDt>
                          <Dt>2008-09-01</Dt>
                      </BookgDt>
                      <ValDt>
                          <Dt>2008-09-01</Dt>
                      </ValDt>
                      <AcctSvcrRef>Bankreferenz</AcctSvcrRef>
                      <BkTxCd/>
                      <NtryDtls>
                          <TxDtls>
                              <Refs>
                                  <EndToEndId>');
      INSERT INTO junk_xml (id,nr,fragment)
      VALUES (0,2,'Ende-zu-Ende-Id des Ueberweisenden</EndToEndId>
                              </Refs>
                              <BkTxCd>
                                  <Prtry>
                                      <Cd>NTRF+166</Cd>
                                      <Issr>ZKA</Issr>
                                  </Prtry>
                              </BkTxCd>
                              <RltdPties>
                                  <Dbtr>
                                      <Nm>Herr Ueberweisender</Nm>
                                  </Dbtr>
                                  <DbtrAcct>
                                      <Id>
                                          <IBAN>DE21500500001234567897</IBAN>
                                      </Id>
                                  </DbtrAcct>
                                  <UltmtDbtr>
                                      <Nm>Herr Debtor Reference Party</Nm>
                                  </UltmtDbtr>
                                  <Cdtr>
                                      <Nm>Herr Kontoinhaber</Nm>
                                  </Cdtr>
                                  <UltmtCdtr>
                                      <Nm>Herr Creditor Reference Party</Nm>
                                  </UltmtCdtr>
                              </RltdPties>
                              <Purp>
                                  <Cd>GDDS</Cd>
                              </Purp>
                              <RmtInf>
                                  <Ustrd>Rechnungsnr. 4711 vom 20.08.2008</Ustrd>
                              </RmtInf>
                          </TxDtls>
                      </NtryDtls>
                      <AddtlNtryInf>SEPA GUTSCHRIFT</AddtlNtryInf>
                  </Ntry>
                  <!--Gutschrift aufgrund einer zurueckgekommenen SEPA-Ueberweisung-->
                  <Ntry>
                      <Amt Ccy="EUR">200.00</Amt>
                      <CdtDbtInd>CRDT</CdtDbtInd>
                      <Sts>BOOK</Sts>
                      <BookgDt>
                          <Dt>2008-09-01</Dt>
                      </BookgDt>
                      <ValDt>
                          <Dt>2008-09-01</Dt>
                      </ValDt>
                      <AcctSvcrRef>Bankreferenz</AcctSvcrRef>
                      <BkTxCd/>
                      <NtryDtls>
                          <TxDtls>
                              <Refs>
                                  <EndToEndId>Urspr. E2E-Id der Hintransaktion</EndToEndId>
                              </Refs>
                              <BkTxCd>
                                  <Prtry>
                                      <Cd>NTRF+159++901</Cd>
                                      <Issr>ZKA</Issr>
                                  </Prtry>
                              </BkTxCd>
                              <RmtInf>
                                  <Ustrd>Angabe des urspruenglichen Verwendungszweckes</Ustrd>
                              </RmtInf>
                              <RtrInf>
                                  <OrgnlBkTxCd>
                                      <Prtry>
                                          <Cd>NTRF+116</Cd>
                                          <Issr>ZKA</Issr>
                                      </Prtry>
                                  </OrgnlBkTxCd>
                                  <Orgtr>
                                      <Id>
                                          <OrgId>
                                              <BICOrBEI>BANKDEHH</BICOrBEI>
                                          </OrgId>
                                      </Id>
                                  </Orgtr>
                                  <Rsn>
                                      <Cd>AC01</Cd>
                                  </Rsn>
                                  <AddtlInf>IBAN FEHLERHAFT</AddtlInf>
                              </RtrInf>
                          </TxDtls>');
      INSERT INTO junk_xml (id,nr,fragment)
      VALUES (0,3,'
                      </NtryDtls>
                      <AddtlNtryInf>SEPA RUECKBUCHUNG</AddtlNtryInf>
                  </Ntry>
                  <!--Belastung aufgrund einer SEPA-Lastschrift-->
                  <Ntry>
                      <Amt Ccy="EUR">50.00</Amt>
                      <CdtDbtInd>DBIT</CdtDbtInd>
                      <Sts>BOOK</Sts>
                      <BookgDt>
                          <Dt>2008-09-01</Dt>
                      </BookgDt>
                      <ValDt>
                          <Dt>2008-09-01</Dt>
                      </ValDt>
                      <AcctSvcrRef>Bankreferenz</AcctSvcrRef>
                      <BkTxCd/>
                      <NtryDtls>
                          <TxDtls>
                              <Refs>
                                  <EndToEndId>E2E-Id vergeben vom Glaeubiger</EndToEndId>
                                  <MndtId>Ref. des SEPA-Lastschriftmandats</MndtId>
                              </Refs>
                              <BkTxCd>
                                  <Prtry>
                                      <Cd>NDDT+105</Cd>
                                      <Issr>ZKA</Issr>
                                  </Prtry>
                              </BkTxCd>
                              <RltdPties>
                                  <Dbtr>
                                      <Nm>Herr Zahlungspflichtiger</Nm>
                                  </Dbtr>
                                  <UltmtDbtr>
                                      <Nm>Herr Debtor Reference Party</Nm>
                                  </UltmtDbtr>
                                  <Cdtr>
                                      <Nm>Glaeubigerfirma</Nm>
                                      <Id>
                                          <PrvtId>
                                              <Othr>
                                                  <Id>Cdtr-Id des Glaeubigers</Id>
                                              </Othr>
                                          </PrvtId>
                                      </Id>
                                  </Cdtr>
                              </RltdPties>
                              <Purp>
                                  <Cd>PHON</Cd>
                              </Purp>
                              <RmtInf>
                                  <Ustrd>Telefonrechnung August 2009, Vertragsnummer 3536456345</Ustrd>
                              </RmtInf>
                          </TxDtls>
                      </NtryDtls>
                      <AddtlNtryInf>SEPA LASTSCHRIFT</AddtlNtryInf>
                  </Ntry>
                  <!-- Beispiel 2: DTAUS-Zahlungen (Ueberweisung, Lastschrift, Rueckgabe) -->
                  <!--Gutschrift aufgrund eines DTA-Überweisungseinganges-->
                  <Ntry>
                      <Amt Ccy="EUR">100.00</Amt>
                      <CdtDbtInd>CRDT</CdtDbtInd>
                      <Sts>BOOK</Sts>
                      <BookgDt>
                          <Dt>2008-09-02</Dt>
                      </BookgDt>
                      <ValDt>
                          <Dt>2008-09-02</Dt>
                      </ValDt>
                      <AcctSvcrRef>Bankreferenz aus DTA C-Satz Feld 6</AcctSvcrRef>
                      <BkTxCd/>
                      <NtryDtls>
                          <TxDtls>
                              <BkTxCd>
                                  <Prtry>
                                      <Cd>NTRF+051++000</Cd>
                                      <Issr>ZKA</Issr>
                                  </Prtry>
                              </BkTxCd>
                              <RltdPties>
                                  <Dbtr>');
      INSERT INTO junk_xml (id,nr,fragment)
      VALUES (0,4,'
                                      <Nm>Herr Überweisender</Nm>
                                  </Dbtr>
                                  <DbtrAcct>
                                      <Id>
                                          <Othr>
                                              <Id>1234567890</Id>
                                          </Othr>
                                      </Id>
                                  </DbtrAcct>
                              </RltdPties>
                              <RmtInf>
                                  <Ustrd>Rechnungsnr 4711 - Warenlieferung vom 20.08.2008</Ustrd>
                              </RmtInf>
                          </TxDtls>
                      </NtryDtls>
                      <AddtlNtryInf>ÜBERWEISUNGSGUTSCHRIFT</AddtlNtryInf>
                  </Ntry>
                  <!--Gutschrift aufgrund einer zurückgekommenen DTA-Überweisung-->
                  <Ntry>
                      <Amt Ccy="EUR">200.00</Amt>
                      <CdtDbtInd>CRDT</CdtDbtInd>
                      <Sts>BOOK</Sts>
                      <BookgDt>
                          <Dt>2008-09-02</Dt>
                      </BookgDt>
                      <ValDt>
                          <Dt>2008-09-02</Dt>
                      </ValDt>
                      <AcctSvcrRef>Bankreferenz aus DTA C-Satz Feld 6</AcctSvcrRef>
                      <BkTxCd/>
                      <NtryDtls>
                          <TxDtls>
                              <BkTxCd>
                                  <Prtry>
                                      <Cd>NTRF+059++511</Cd>
                                      <Issr>ZKA</Issr>
                                  </Prtry>
                              </BkTxCd>
                              <RmtInf>
                                  <Ustrd>Angabe des ursprünglichen Verwendungszweckes</Ustrd>
                              </RmtInf>
                              <RtrInf>
                                  <OrgnlBkTxCd>
                                      <Prtry>
                                          <Cd>NTRF+051++000</Cd>
                                          <Issr>ZKA</Issr>
                                      </Prtry>
                                  </OrgnlBkTxCd>
                                  <Orgtr>
                                      <Nm>Herr Überweisungsempfänger</Nm>
                                  </Orgtr>
                                  <Rsn>
                                      <Prtry>512</Prtry>
                                  </Rsn>
                                  <AddtlInf>BLZ 25069674 EXISTIERT NICHT</AddtlInf>
                              </RtrInf>
                          </TxDtls>
                      </NtryDtls>
                      <AddtlNtryInf>RÜCKÜBERWEISUNG</AddtlNtryInf>
                  </Ntry>
                  <!--Belastung aufgrund einer DTA-Lastschrift-->
                  <Ntry>
                      <Amt Ccy="EUR">50</Amt>
                      <CdtDbtInd>DBIT</CdtDbtInd>
                      <Sts>BOOK</Sts>
                      <BookgDt>
                          <Dt>2008-09-02</Dt>
                      </BookgDt>
                      <ValDt>
                          <Dt>2008-09-02</Dt>
                      </ValDt>
                      <AcctSvcrRef>Bankreferenz aus DTA C-Satz Feld 6</AcctSvcrRef>
                      <BkTxCd/>
                      <NtryDtls>
                          <TxDtls>
                              <BkTxCd>
                                  <Prtry>
                                      <Cd>NTRF+005++000</Cd>
                                      <Issr>ZKA</Issr>
                                  </Prtry>
                              </BkTxCd>
                              ');
      INSERT INTO junk_xml (id,nr,fragment)
      VALUES (0,5,'<RltdPties>
                                  <Cdtr>
                                      <Nm>Telefongesellschaft ABC</Nm>
                                  </Cdtr>
                              </RltdPties>
                              <RmtInf>
                                  <Ustrd>Telefonrechnung August 2009, Vertragsnummer 3536456345</Ustrd>
                              </RmtInf>
                          </TxDtls>
                      </NtryDtls>
                      <AddtlNtryInf>LASTSCHRIFT</AddtlNtryInf>
                  </Ntry>
                  <!-- Beispiel 3a: Sammlerdarstellung mit Aufloesung innerhalb der Nachricht -->
                  <!--Belastung aufgrund von SEPA-Lastschriftrueckgaben (Sammelbuchung) mit Sammleraufloesung unter Transaction Details-->
                  <Ntry>
                      <Amt Ccy="EUR">276</Amt>
                      <CdtDbtInd>DBIT</CdtDbtInd>
                      <Sts>BOOK</Sts>
                      <BookgDt>
                          <Dt>2008-09-03</Dt>
                      </BookgDt>
                      <ValDt>
                          <Dt>2008-09-03</Dt>
                      </ValDt>
                      <AcctSvcrRef>Bankreferenz</AcctSvcrRef>
                      <BkTxCd/>
                      <!-- BkTxCd ist Pflichtfeld gemaess ISO, wird jedoch ausschliesslich auf Tx-Ebene verwendet -->
                      <NtryDtls>
                          <Btch>
                              <NbOfTxs>3</NbOfTxs>
                          </Btch>
                          <TxDtls>
                              <!-- Ab hier Aufloesung des Sammlers bestehend aus 3 Einzelumsaetzen -->
                              <Refs>
                                  <EndToEndId>79892</EndToEndId>
                                  <MndtId>10001</MndtId>
                              </Refs>
                              <AmtDtls>
                                  <TxAmt>
                                      <Amt Ccy="EUR">76</Amt>
                                  </TxAmt>
                              </AmtDtls>
                              <BkTxCd>
                                  <Prtry>
                                      <Cd>NTRF+109++901</Cd>
                                      <Issr>ZKA</Issr>
                                  </Prtry>
                              </BkTxCd>
                              <RltdPties>
                                  <Dbtr>
                                      <Nm>Herr Zahlungspflichtiger 1</Nm>
                                  </Dbtr>
                                  <Cdtr>
                                      <Nm>Telefongesellschaft ABC</Nm>
                                      <Id>
                                          <PrvtId>
                                              <Othr>
                                                  <Id>CdtrId des SEPA-Lastschrifteinr.</Id>
                                              </Othr>
                                          </PrvtId>
                                      </Id>
                                  </Cdtr>
                              </RltdPties>
                              <Purp>
                                  <Cd>PHON</Cd>
                              </Purp>
                              <RmtInf>
                                  <Ustrd>Telefonrechnung August 2009, Vertragsnummer 3536456345</Ustrd>
                              </RmtInf>
                          </TxDtls>
                          <TxDtls>
                              <Refs>
                                  <EndToEndId>768768</EndToEndId>
                                  <MndtId>10002');
      INSERT INTO junk_xml (id,nr,fragment)
      VALUES (0,6,'</MndtId>
                              </Refs>
                              <AmtDtls>
                                  <TxAmt>
                                      <Amt Ccy="EUR">80</Amt>
                                  </TxAmt>
                              </AmtDtls>
                              <BkTxCd>
                                  <Prtry>
                                      <Cd>NTRF+109++901</Cd>
                                      <Issr>ZKA</Issr>
                                  </Prtry>
                              </BkTxCd>
                              <RltdPties>
                                  <Dbtr>
                                      <Nm>Herr Zahlungspflichtiger 2</Nm>
                                  </Dbtr>
                                  <Cdtr>
                                      <Nm>Telefongesellschaft ABC</Nm>
                                      <Id>
                                          <PrvtId>
                                              <Othr>
                                                  <Id>CdtrId des SEPA-Lastschrifteinr.</Id>
                                              </Othr>
                                          </PrvtId>
                                      </Id>
                                  </Cdtr>
                              </RltdPties>
                              <Purp>
                                  <Cd>PHON</Cd>
                              </Purp>
                              <RmtInf>
                                  <Ustrd>Telefonrechnung August 2009, Vertragsnummer 3536456888</Ustrd>
                              </RmtInf>
                          </TxDtls>
                          <TxDtls>
                              <Refs>
                                  <EndToEndId>45456465</EndToEndId>
                                  <MndtId>10003</MndtId>
                              </Refs>
                              <AmtDtls>
                                  <TxAmt>
                                      <Amt Ccy="EUR">120</Amt>
                                  </TxAmt>
                              </AmtDtls>
                              <BkTxCd>
                                  <Prtry>
                                      <Cd>NTRF+109++901</Cd>
                                      <Issr>ZKA</Issr>
                                  </Prtry>
                              </BkTxCd>
                              <RltdPties>
                                  <Dbtr>
                                      <Nm>Herr Zahlungspflichtiger 3</Nm>
                                  </Dbtr>
                                  <Cdtr>
                                      <Nm>Telefongesellschaft ABC</Nm>
                                      <Id>
                                          <PrvtId>
                                              <Othr>
                                                  <Id>CdtrId des SEPA-Lastschrifteinr.</Id>
                                              </Othr>
                                          </PrvtId>
                                      </Id>
                                  </Cdtr>
                              </RltdPties>
                              <Purp>
                                  <Cd>PHON</Cd>
                              </Purp>
                              <RmtInf>
                                  <Ustrd>Telefonrechnung August 2009, Vertragsnummer 3536456345</Ustrd>
                              </RmtInf>
                          </TxDtls>
                      </NtryDtls>
                      <AddtlNtryInf>SEPA Direct Debit (Einzelbuchung-Soll, Core)</AddtlNtryInf>
                  </Ntry>
                  <!-- Beispiel 3b: Sammlerdarstellung mit Verweis auf pain-Nachricht und separate camt.054.001.01-Nachricht -->
                  <!--Belastung aufgrund einer SEPA-Ueberweisung (Sammler) mit Verweis auf Original pain-Nachricht-->
                  <Ntry>
                      <Amt Ccy="');
      INSERT INTO junk_xml (id,nr,fragment)
      VALUES (0,7,'EUR">100876.00</Amt>
                      <CdtDbtInd>DBIT</CdtDbtInd>
                      <Sts>BOOK</Sts>
                      <BookgDt>
                          <Dt>2008-09-03</Dt>
                      </BookgDt>
                      <ValDt>
                          <Dt>2008-09-03</Dt>
                      </ValDt>
                      <AcctSvcrRef>Bankreferenz</AcctSvcrRef>
                      <BkTxCd/>
                      <NtryDtls>
                          <Btch>
                              <MsgId>MsgId der pain-Nachricht</MsgId>
                              <PmtInfId>Sammler-Id dieser pain-Nachricht</PmtInfId>
                          </Btch>
                          <TxDtls>
                              <BkTxCd>
                                  <Prtry>
                                      <Cd>NTRF+191</Cd>
                                      <Issr>ZKA</Issr>
                                  </Prtry>
                              </BkTxCd>
                          </TxDtls>
                      </NtryDtls>
                      <AddtlNtryInf>SEPA Credit Transfer (Sammler-Soll)</AddtlNtryInf>
                  </Ntry>
                  <!--Belastung aufgrund von SEPA-Lastschriftrueckgaben (Sammelbuchung) mit Verweis auf separate camt.054.001.01-Nachricht-->
                  <Ntry>
                      <Amt Ccy="EUR">276.00</Amt>
                      <CdtDbtInd>DBIT</CdtDbtInd>
                      <Sts>BOOK</Sts>
                      <BookgDt>
                          <Dt>2008-09-03</Dt>
                      </BookgDt>
                      <ValDt>
                          <Dt>2008-09-03</Dt>
                      </ValDt>
                      <AcctSvcrRef>Bankreferenz</AcctSvcrRef>
                      <BkTxCd/>
                      <AddtlInfInd>
                          <MsgNmId>camt.054.001.01</MsgNmId>
                          <MsgId>054-20090903-00034</MsgId>
                          <!-- siehe Bsp. camt54 Bsp 3b -->
                      </AddtlInfInd>
                      <NtryDtls>
                          <TxDtls>
                              <BkTxCd>
                                  <Prtry>
                                      <Cd>NTRF+109++901</Cd>
                                      <Issr>ZKA</Issr>
                                  </Prtry>
                              </BkTxCd>
                          </TxDtls>
                      </NtryDtls>
                      <AddtlNtryInf>SEPA Direct Debit (Einzelbuchung-Soll, Core)</AddtlNtryInf>
                  </Ntry>
                  <!-- Beispiel 4: USD-Zahlung mit Gutschrift auf einem EUR-Konto -->
                  <!-- USD-Zahlung mit Gutschrift auf einem EUR-Konto -->
                  <Ntry>
                      <Amt Ccy="EUR">259595.60</Amt>
                      <CdtDbtInd>CRDT</CdtDbtInd>
                      <Sts>BOOK</Sts>
                      <BookgDt>
                          <Dt>2008-09-04</Dt>
                      </BookgDt>
                      <ValDt>
                          <Dt>2008-09-04</Dt>
                      </ValDt>
                      <AcctSvcrRef>Bankreferenz</AcctSvcrRef>
                      <BkTxCd/>
                      <NtryDtls>
                          <TxDtls>
                              <AmtDtls>
                                  <InstdAmt>
                                      <Amt Ccy="USD">360873.97</Amt>
                                  ');
      INSERT INTO junk_xml (id,nr,fragment)
      VALUES (0,8,'</InstdAmt>
                                  <TxAmt>
                                      <Amt Ccy="EUR">259595.60</Amt>
                                  </TxAmt>
                                  <CntrValAmt>
                                      <Amt Ccy="EUR">259621.56</Amt>
                                      <CcyXchg>
                                          <SrcCcy>USD</SrcCcy>
                                          <TrgtCcy>EUR</TrgtCcy>
                                          <XchgRate>1.39</XchgRate>
                                      </CcyXchg>
                                  </CntrValAmt>
                              </AmtDtls>
                              <BkTxCd>
                                  <Prtry>
                                      <Cd>NTRF+202</Cd>
                                      <Issr>ZKA</Issr>
                                  </Prtry>
                              </BkTxCd>
                              <Chrgs>
                                  <Amt Ccy="EUR">25.96</Amt>
                              </Chrgs>
                              <RltdPties>
                                  <Dbtr>
                                      <Nm>West Coast Ltd.</Nm>
                                      <PstlAdr>
                                          <Ctry>US</Ctry>
                                          <AdrLine>52, Main Street</AdrLine>
                                          <AdrLine>3733 San Francisco</AdrLine>
                                      </PstlAdr>
                                  </Dbtr>
                                  <DbtrAcct>
                                      <Id>
                                          <Othr>
                                              <Id>546237687</Id>
                                          </Othr>
                                      </Id>
                                  </DbtrAcct>
                              </RltdPties>
                              <RltdAgts>
                                  <DbtrAgt>
                                      <FinInstnId>
                                          <BIC>BANKUSNY</BIC>
                                      </FinInstnId>
                                  </DbtrAgt>
                              </RltdAgts>
                              <RmtInf>
                                  <Ustrd>Invoice No. 4545</Ustrd>
                              </RmtInf>
                          </TxDtls>
                      </NtryDtls>
                      <AddtlNtryInf>AZV-UEBERWEISUNGSGUTSCHRIFT</AddtlNtryInf>
                  </Ntry>
              </Stmt>
          </BkToCstmrStmt>
      </Document>');
      
      COMMIT;
      

       

       

      My first idea was to use LISTAGG to splice the pieces, but I get an ORA-01489: result of string concatenation is too long

       

      SELECT  XMLTYPE(
                  LISTAGG(fragment,'')
                  WITHIN GROUP(ORDER BY nr))
      FROM    junk_xml
      WHERE   id = 0
      GROUP BY id;
      

      I also considered exporting the data into another table using maybe dbms_lob to concatenate the strings, but I'm not allowed to create another table.

       

      Any idea how I can access the data hidden in this application?

       

      Regards

      Marcus

       

      P.S.: The application is not about SEPA-processing, but I don't want to show the real data.