PostgreSQL - Insert XML element by updating a row

Question

Let's say I have a table named "content" and a column named "images" with data type xml.

I have a row in which its column "images" already has a value:

<Pictures>
  <Picture ID="1">
    <big>../srcs/Big_Buck_Bunny.jpg</big>
  </Picture>
  <Picture ID="2">
    <big>../srcs/pic_jpeg.jpeg</big>
  </Picture>
</Pictures>

and I would like to add the elements below as the last child of < Pictures>

<Picture ID="3">
  <big>../srcs/pic_gif.gif</big>
</Picture>

In DB2, I understand it can be done as shown in Update 11 of IBM's Update XML in DB2 but how do I do this using PostgreSQL?

I also found a similar question to this but no one has answered it.

score 0 · Answer 1 · edited Aug 17 '15 at 16:41

0

Select everything in the images column and then in your php, add the "...". Then update content set images= katong new xml na added na ang katong picture with id=3 where id=id sa row na imong iupdate.

edited Aug 17 '15 at 16:41

HaveNoDisplayName

8,291
106
37
47

answered Aug 17 '15 at 14:50

Josephine

1

I like the idea but is there any way to do this in Postgres? – knightowl Aug 17 '15 at 14:59

score 0 · Answer 2 · answered Aug 17 '15 at 16:19

If the structure of the XML content of the 'images' column is predefined, in your sql statement, you can make use of every new entry as 'xmlelement' and then operating on xml type in postgres, you can make use of the xml processing functions such as xmlconcat(see documentation). It's all about operating on proper types supported by the RDBMS of your choice.

score 0 · Answer 3 · answered Sep 28 '17 at 14:16

I had the problem today, 2 years later, and work out something like this:

SELECT xmlelement(name "Pictures",
              xmlagg(t1.x), 
    '<Picture ID="3"><big>../srcs/pic_gif.gif</big></Picture>'::xml) xrec   -- option 1
     --xmlelement(name "Picture", xmlattributes(3 as id), xmlelement(name big,'../srcs/pic_gif.gif'))) xrex        -- option 2     
FROM (       
SELECT unnest(XPATH('/Pictures/Picture', images)) X
FROM content WHERE UNQKEY = 8) t1;

There are two options to add the new element, either via a string or via another element; options 1 and 2. (Un)Comment the line you want to use.

PostgreSQL - Insert XML element by updating a row

3 Answers3