Thread

1. How to do an update with XML column ?

   celati Laurent <laurent.celati@gmail.com> — 2024-12-16T16:07:05Z

   Dear all,
   I'm taking the liberty to ask an question. Do you know if there are some
   ways in order to update and/or do group by with an XML column ?
   For instance,
   
   select id, unnest(xpath(
           '//cit:CI_Organisation/cit:name/gco:CharacterString/text()',
          CAST(data AS XML),
           ARRAY[
               ARRAY['cit', 'http://standards.iso.org/iso/19115/-3/cit/2.0'],
               ARRAY['gco', 'http://standards.iso.org/iso/19115/-3/gco/1.0'],
               ARRAY['mdb','http://standards.iso.org/iso/19115/-3/mdb/2.0'],
               ARRAY['cat','http://standards.iso.org/iso/19115/-3/cat/1.0'],
           ])) as orga_name, changedate, createdate, displayorder,
   doctype, extra, popularity, rating, root, schemaid, title, istemplate,
   isharvested, harvesturi,
          harvestuuid, groupowner, metadata.owner, metadata.source, uuid
   from public.metadata
   group by orga_name
   
   
   I obtained :
   
   ERROR:  could not identify an equality operator for type xml
   LINE 39: group by orga_name
                     ^
   
   SQL state: 42883
   Character: 2556
   
   
   Thanks so much
   

2. Re: How to do an update with XML column ?

   Francisco Olarte <folarte@peoplecall.com> — 2024-12-18T12:39:10Z

   Hi:
   
   On Wed, 18 Dec 2024 at 11:00, celati Laurent <laurent.celati@gmail.com> wrote:
   
   > ERROR:  could not identify an equality operator for type xml
   > LINE 39: group by orga_name
   >                   ^
   
   Have you tried the obvious group by cast(orga_name as text) ? Never
   used XML but most types in postgres convert to text injectively ( but
   not bijectively ) ( I mean, same values, same text, but you can have
   different text same values ( like the obvious text->float conversion
   ))
   
   
   Francisco Olarte.