Horton
Menu

Re: Extract values from XML content

David G. Johnston <david.g.johnston@gmail.com>

View thread
From: "David G. Johnston" <david.g.johnston@gmail.com>
To: celati Laurent <laurent.celati@gmail.com>
Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Date: 2024-11-13T18:13:00Z
Lists: pgsql-general 
On Wednesday, November 13, 2024, celati Laurent <laurent.celati@gmail.com>
wrote:
>
>
> SELECT id, xpath('/contact/CI_ResponsibleParty/organisationName/CharacterString/text()',
>
>             CAST(data AS XML)) AS organisation_name <mri:pointOfContact>
>
>     <cit:CI_Responsibility>
>       <cit:role>
>         <cit:CI_RoleCode codeList="http://standards.iso.org/iso/19115/resources/Codelists/cat/codelists.xml#CI_RoleCode" codeListValue="originator" />
>       </cit:role>
>       <cit:party>
>         <cit:CI_Organisation>
>           <cit:name>
>             <gco:CharacterString>Office français de la biodiversité</gco:CharacterString>
>           </cit:name>
>
>
I’d look into how to specify namespaces since your XML elements all have
them but you don’t have any in your xpath.

David J.