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.

James · Accepted Answer · 2015-08-17 16:19:06Z

0

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.

answered Aug 17, 2015 at 16:19

James

11 gold badge1 silver badge1 bronze badge

Sign up to request clarification or add additional context in comments.

Comments

HaveNoDisplayName · Accepted Answer · 2015-08-17 16:41:39Z

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, 2015 at 16:41

HaveNoDisplayName

8,537106 gold badges41 silver badges50 bronze badges

answered Aug 17, 2015 at 14:50

Josephine

1

1 Comment

knightowl Over a year ago

I like the idea but is there any way to do this in Postgres?

Nico Botha · Accepted Answer · 2017-09-28 14:16:54Z

0

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.

answered Sep 28, 2017 at 14:16

Nico Botha

211 silver badge4 bronze badges

Collectives™ on Stack Overflow

PostgreSQL - Insert XML element by updating a row

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related