Oracle SQL更新XML列数据：在<a>标签内插入<b>标签包裹原有子节点

列类型为字符串类型（varchar/nvarchar）时 #

UPDATE your_table
SET [XML] = CAST(
  CAST([XML] AS XML).query('
    element a {
      /a/@*,
      element b { /a/node() }
    }
  ') AS NVARCHAR(MAX)
)
WHERE CAST([XML] AS XML).exist('/a') = 1;

MySQL 实现 #

UPDATE your_table
SET `XML` = UpdateXML(
  `XML`, 
  '/a',
  CONCAT(
    '<a ', 
    ExtractValue(`XML`, 'concat(/a/@*,"")'), 
    '><b>', 
    SUBSTRING_INDEX(SUBSTRING_INDEX(`XML`, '>', 2), '<', -1), 
    '</b></a>'
  )
)
WHERE `XML` REGEXP '^<a[^>]*>';

• 用正则匹配筛选以标签开头的行
• 保留原有所有属性，把原有子内容全部封装到标签内

UPDATE your_table
SET "XML" = XMLQuery(
  'copy $tmp := . modify insert node element b {$tmp/a/node()} as first into $tmp/a return $tmp'
  PASSING "XML" RETURNING CONTENT
)
WHERE XMLEXISTS('/a' PASSING "XML");