Add node to XML in CLOB column (Oracle)-CodePudding

In a table we store XML like this inside a CLOB column:

<?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
<characteristics>
  <characteristic id="106">
    <value>6792</value>
  </characteristic>
  <characteristic id="1001">
    <value>2616</value>
  </characteristic>
  <characteristic id="108">
    <value>6795</value>
  </characteristic>
  <characteristic id="10002">
    <value>5589</value>
  </characteristic>
  <characteristic id="105">
    <value>6819</value>
  </characteristic>
  <characteristic id="107">
    <value>6796</value>
  </characteristic>
</characteristics>

I now have to add <characteristic id="10001"> with some value to every row that doesn't have <characteristic id="10001"> already.

I already tried to find all rows that don't already have <characteristic id="10001"> with Oracle XML SQL functions, but I don't get the XPath correct so that only the "wanted" rows get returned.

Same with adding the characteristic and its value to the XML.

Maybe somebody has some examples?

I tried:

where  existsnode(xmltype(characteristics), '/characteristics/characteristic[id = "10001"]') = 1

to get the rows that have <characteristic id="10001"> in their XML column, but doesn't return any rows even if such rows exist.

CodePudding user response：

Your path

'/characteristics/characteristic[id = "10001"]'

is looking for a child node called ID with text 10001. Your XML has an attribute called ID, so you need to use the @ syntax:

'/characteristics/characteristic[@id = "10001"]'

making it:

existsnode(xmltype(characteristics), '/characteristics/characteristic[@id = "10001"]') = 1

The existsnode() function is deprecated, so you might want to switch to xmlexists() instead:

xmlexists('/characteristics/characteristic[@id = "10001"]' passing xmltype(characteristics))

fiddle

Then for the node insertion you could use XMLQuery with a FLOWR expression:

xmlquery(
    'copy $tmp := . modify insert node 
      <characteristic id="10001"><value>42</value></characteristic>
      as first into $tmp/characteristics
      return $tmp'
    passing xmltype(characteristics)
    returning content
  ).getclobval()

giving you something like:

update your_table
set characteristics = xmlserialize(
    document xmlquery(
      'copy $tmp := . modify insert node 
        <characteristic id="10001"><value>42</value></characteristic>
        as first into $tmp/characteristics
        return $tmp'
      passing xmltype(characteristics)
      returning content
    )
    as clob
    indent size=2
  )
where not xmlexists(
    '/characteristics/characteristic[@id = "10001"]'
    passing xmltype(characteristics)
  )

fiddle

And just for fun really, you could do the same thing with XMLPatch:

xmlpatch(
  xmltype(characteristics),
  xmltype('<?xml version="1.0"?>
<xd:xdiff xsi:schemaLocation="http://xmlns.oracle.com/xdb/xdiff.xsd http://xmlns.oracle.com/xdb/xdiff.xsd" xmlns:xd="http://xmlns.oracle.com/xdb/xdiff.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <?oracle-xmldiff operations-in-docorder="true" output-model="snapshot" diff-algorithm="global"?>
  <xd:insert-node-before xd:node-type="element" xd:xpath="/characteristics[1]/characteristic[1]">
    <xd:content>
      <characteristic id="10001">
        <value>42</value>
      </characteristic>
    </xd:content>
  </xd:insert-node-before>
</xd:xdiff>')
  )

fiddle