Início / coding / Perguntas / 77495542
Accepted
Rico Strydom
Asked: 2023-11-16 22:11:33 +0800 CST

Oracle: Insira texto com </br> logo antes do texto existente

  • 772

Tenho uma coluna CLOB no meu banco de dados que contém conteúdo como este:

<?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>

Gostaria de atualizar este campo adicionando texto logo após a raiz (tdfmt) seguido por outro</br>

Resultando nisso:

<?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>

Tentei atualizar meu clob com isso, mas duvido que o CHR(13) seja o caminho a percorrer.

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()

Espero que meu desejo esteja claro...

oracle

1 respostas

  1. Best Answer

    Você poderia usar XMLQuery com FLWOR em vez de UPDATEXML:

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

    o que lhe dá:

    <?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>

    Ou para preservar (eu acho; principalmente, de qualquer maneira!) sua formatação existente, use XMLSerialise em vez de 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, '&#xa;' ) 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);

    o que lhe dá:

    <?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>

    violino

    Passei a string de texto e <br/>a tag como argumentos supondo que pelo menos a parte do texto será realmente uma variável; você também pode passar o caractere de nova linha, se preferir:

    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);

    violino

    ... embora eu não tenha certeza se você realmente deseja/precisa disso.

    • 0

relate perguntas