Oracle : Insert text with </br> right before existing text

Question

I have a CLOB column in my database that contains content like this:

<?xml version="1.0"?>
<tdfmt sel-start="218">31 05 2022 Rico Strydom<br/>
Op verzoek (zie e-mail) garanties overplaatsen....<br/>
    <br/>
OVERBOEKING CREDITGELDEN:<br/>BANKCODE: 1171<br/>T.L.V.: VERP. NL99BANK999999999999<br/>T.G.V.: RC NL00BANK000000000000<br/>
BEDRAG: EUR 9999999<br/>BESCHRIJVING: Vrijgave verpanding creditgelden AA98987987987 ivm overzetten zekerheid naar BGF<br/>
    <br/>
</tdfmt>

I would like to update this field by adding text right after the root (tdfmt) followed by another </br>

Resulting in this:

<?xml version="1.0"?>
<tdfmt sel-start="218">THIS TEXT SHOULD GO FIRST</br>
31 05 2022 Rico Strydom<br/>
Op verzoek (zie e-mail) garanties overplaatsen....<br/>
    <br/>
OVERBOEKING CREDITGELDEN:<br/>BANKCODE: 1171<br/>T.L.V.: VERP. NL99BANK999999999999<br/>T.G.V.: RC NL00BANK000000000000<br/>
BEDRAG: EUR 9999999<br/>BESCHRIJVING: Vrijgave verpanding creditgelden AA98987987987 ivm overzetten zekerheid naar BGF<br/>
    <br/>
</tdfmt>

I have tried updating my clob with this but I doubt the CHR(13) is the way to go.

UPDATE OIT SET INFTXT = UPDATEXML(XMLTYPE(OIT.INFTXT),'//tdfmt/text()[1]','THIS TEXT SHOULD GO FIRST' || chr(10)  || EXTRACTVALUE(XMLTYPE(OIT.INFTXT), '//tdfmt/text()[1]', '')).getClobVal()

Hope my wish is clear...

Answer 1

You could use XMLQuery with FLWOR instead of UPDATEXML:

UPDATE OIT SET INFTXT = XMLQUERY(q'^
      copy $i := $d modify (
        for $j in $i/tdfmt/text()[1]
          return insert node ( $text, $br, '
' ) before $j
      )
      return $i
    ^'
    PASSING
      XMLTYPE(OIT.INFTXT) AS "d",
      'THIS TEXT SHOULD GO FIRST' AS "text",
      XMLTYPE('<br/>') AS "br"
    RETURNING CONTENT
  ).getClobVal();

which gives you:

<?xml version="1.0"?><tdfmt sel-start="218">THIS TEXT SHOULD GO FIRST<br/>
31 05 2022 Rico Strydom<br/>
Op verzoek (zie e-mail) garanties overplaatsen....<br/><br/>
OVERBOEKING CREDITGELDEN:<br/>BANKCODE: 1171<br/>T.L.V.: VERP. NL99BANK999999999999<br/>T.G.V.: RC NL00BANK000000000000<br/>
BEDRAG: EUR 9999999<br/>BESCHRIJVING: Vrijgave verpanding creditgelden AA98987987987 ivm overzetten zekerheid naar BGF<br/><br/></tdfmt>

Or to preserve (I think; mostly anyway!) your existing formatting, use XMLSerialise instead of getClobVal:

UPDATE OIT SET INFTXT = XMLSERIALIZE(DOCUMENT XMLQUERY(q'^
      copy $i := $d modify (
        for $j in $i/tdfmt/text()[1]
          return insert node ( $text, $br, '
' ) before $j
      )
      return $i
    ^'
    PASSING
      XMLTYPE(OIT.INFTXT) AS "d",
      'THIS TEXT SHOULD GO FIRST' AS "text",
      XMLTYPE('<br/>') AS "br"
    RETURNING CONTENT
  ) AS CLOB INDENT SIZE=4);

which gives you:

<?xml version="1.0"?>
<tdfmt sel-start="218">THIS TEXT SHOULD GO FIRST<br/>
31 05 2022 Rico Strydom<br/>
Op verzoek (zie e-mail) garanties overplaatsen....<br/>
    <br/>
OVERBOEKING CREDITGELDEN:<br/>BANKCODE: 1171<br/>T.L.V.: VERP. NL99BANK999999999999<br/>T.G.V.: RC NL00BANK000000000000<br/>
BEDRAG: EUR 9999999<br/>BESCHRIJVING: Vrijgave verpanding creditgelden AA98987987987 ivm overzetten zekerheid naar BGF<br/>
    <br/>
</tdfmt>

fiddle

I've passed in the text string and <br/> tag as arguments on the assumption at least the text part will be a variable really; you could pass the newline character in as well if you prefer:

UPDATE OIT SET INFTXT = XMLSERIALIZE(DOCUMENT XMLQUERY('
      copy $i := $d modify (
        for $j in $i/tdfmt/text()[1]
          return insert node ( $text, $br, $newline ) before $j
      )
      return $i
    '
    PASSING
      XMLTYPE(OIT.INFTXT) AS "d",
      'THIS TEXT SHOULD GO FIRST' AS "text",
      XMLTYPE('<br/>') AS "br",
      CHR(10) as "newline"
    RETURNING CONTENT
  ) AS CLOB INDENT SIZE=4);

fiddle

... although I'm not sure whether you really want/need that at all.