CODEWITHSUNDEEP
sqloracle-databaseplsqloracle10g
Chetan Kondawle
Chetan Kondawle

Reputation: 338

Update SQL column data by adding text in between data in Oracle SQL

I have two columns

ID XML
1 <a id='1'></a>
2 <a id='2'></a>

Now I want to search data that starts with <a> tag and update the xml entry, I want to add extra tag in between <a> tag for all the rows.

Note : This is sample data. I need all the child nodes present under <a> to come under <b> and the child nodes will vary for every row.

Sample : <a id='1'><b></b></a>

How do I use the update statement to fetch that data and and add <b> tag in between xml and save it.

Upvotes: 0

Views: 622

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

You can use XQuery update:

update your_table
set xml = xmlquery('
  copy $i := $p1
  modify (
    for $j in $i/a
    return insert node $p2 into $j
  )
  return $i'
  passing xml as "p1", xmltype('<b></b>') as "p2"
  returning content)
where xmlexists('$p/a' passing xml as "p")

or with such a simple new node (if it really is):

update your_table
set xml = xmlquery('
  copy $i := $p1
  modify (
    for $j in $i/a
    return insert node $p2 into $j
  )
  return $i'
  passing xml as "p1", xmlelement("b", null) as "p2"
  returning content)
where xmlexists('$p/a' passing xml as "p")

db<>fiddle

Read more about updating XML data in the documentation.

I need all the child nodes present under <a> to come under <b>

You could still do this with an XQuery update; can't help thinking it could be done more simply, but this creates a copy of the b node, then copies all the other nodes under a into it, delete those nodes directly under a, an dinserts the new b - including the copied children - under a instead:

update your_table
set xml = xmlquery('
  copy $i := $p1
  modify (
    for $j in $i/a
    return insert node (
      copy $k := $p2
      modify (
        for $l in $j/*
        return (
          insert node $l into $k/b
        )
      )
      return $k
    )
    into $j,
    delete nodes $i/a/*
  )
  return $i'
  passing xml as "p1", xmltype('<b></b>') as "p2"
  returning content)
where xmlexists('$p/a' passing xml as "p");

db<>fiddle with some expanded sample data with and without child nodes.

You could also do this with regexp_replace, as an advance on Barbaros' suggestion in comments; at least with small XML documents:

update your_table t
set xml = xmltype(regexp_replace(t.xml.getstringval(), '(<a.*?>)(.*?)(</a>)',
  '\1<b>\2</b>\3', 1, 0, 'n'))
where xmlexists('$p/a' passing xml as "p");

db<>fiddle

but for some reason it isn't working with getclobval() - not sure if that's a big in this specific version. However, unless the structure is very well known and controlled this is likely to do something odd at some point, so it's really just for interest. It's safer to manipulate XML as XML rather than as a string.

And then there's XLST... but someone else can make suggestions using that.

Upvotes: 2

Related Questions