Oracle中在CLOB类型XML的tdfmt根节点后添加指定文本

我的数据库中有一个CLOB列，存储的内容如下：

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

我希望更新该字段，在根节点<tdfmt>后添加文本“THIS TEXT SHOULD GO FIRST”并跟随一个<br/>，得到如下结果：

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

我尝试使用以下语句更新CLOB，但不确定chr(10)是否是正确的方式，寻求正确的解决方案：

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

你的思路方向没问题，但需要补全缺失的<br/>标签，同时优化语句兼容性：

方案一：兼容旧版本Oracle（使用UPDATEXML） #

UPDATE OIT 
SET INFTXT = UPDATEXML(
    XMLTYPE(OIT.INFTXT),
    '//tdfmt/text()[1]',
    'THIS TEXT SHOULD GO FIRST<br/>' || CHR(10) || EXTRACTVALUE(XMLTYPE(OIT.INFTXT), '//tdfmt/text()[1]')
).getClobVal()
-- 添加WHERE条件限定更新范围，避免全表更新
WHERE ...;

直接在新文本后拼接<br/>和换行符CHR(10)，再跟上原文本内容，完全匹配目标格式。

方案二：Oracle 12c+推荐方案（使用XMLQUERY） #

EXTRACTVALUE已被废弃，用XMLQUERY的插入逻辑更清晰规范：

UPDATE OIT 
SET INFTXT = XMLQUERY(
    'copy $new := $doc modify
        (insert node text{"THIS TEXT SHOULD GO FIRST<br/>"||$NL} as first into $new/tdfmt)
        return $new'
    PASSING XMLTYPE(OIT.INFTXT) AS "doc", CHR(10) AS "NL"
    RETURNING CONTENT
).getClobVal()
WHERE ...;

通过insert node直接在<tdfmt>节点最开头插入指定内容，语义更明确。

验证建议 #

执行更新前先查询验证结果，避免误操作：

SELECT UPDATEXML(
    XMLTYPE(OIT.INFTXT),
    '//tdfmt/text()[1]',
    'THIS TEXT SHOULD GO FIRST<br/>' || CHR(10) || EXTRACTVALUE(XMLTYPE(OIT.INFTXT), '//tdfmt/text()[1]')
).getClobVal() AS updated_inftxt
FROM OIT
WHERE ...;

若CLOB内容存在XML格式不规范的情况，XMLTYPE会报错，需先确保XML合法性。

内容的提问来源于stack exchange，提问作者Rico Strydom