I have data stored in xml format in my postgresql database. For some records there are several xml but I would like to select only one based on a priority.
| publication_id | xml |
|---|---|
| 162382550 | {<abstract load-source="patent-office"><p num="0000">Text1</p></ abstract>} |
| 162382550 | {<abstract load-source="docdb"><p>Text2</p></abstract>} |
I want to select the record where load-source is patent-office. If this source is not available I want to select docdb. Obviously I don’t want to select both records at the same time.
I have tried that but obviously it picks the 2 records and they are both present.
select publication_id, xpath('/abstract', abs.content) as abstract from xml.t_abstract abs where publication_id = 162382550 and (xpath('abstract/@load-source', abs.content)::text = '{patent-office}' OR xpath('abstract/@load-source', abs.content)::text = '{docdb}');