Extract values from XML content

From: celati Laurent <laurent(dot)celati(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Extract values from XML content
Date: 2024-11-13 14:58:21
Message-ID: CAHByMH01=A2dH5ASd6tyZeRZgPnBf1CYZOC==r9=U=thYbYpKw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Good afternoon,
I have a table 'metadata' with 2000 records. With one column 'id' and one
column 'data' with XML content.
I need to extract for all records the values regarding the Organisation
names.
I success in querying without error message thanks to this following sql
query :

SELECT id,
xpath('/contact/CI_ResponsibleParty/organisationName/CharacterString/text()',

CAST(data AS XML)) AS organisation_name
FROM public.metadata;

But the values don't appear into the column :

"id" "organisation_name"
16410 "[]"
16411 "[]"
16412 "[]"
16413 "[]"
16414 "[]"
16415 "[]"
16416 "[]"
16423 "[]"
16425 "[]"
16426 "[]"
16427 "[]"
16435 "[]"
2250 "[]"
16587 "[]"
16588 "[]"

If needed, i paste below the FULL extract of the XLM content up to my
section of interest :

<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>

Thanks so much.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message jayakumar s 2024-11-13 16:15:49 DB wal file disabled --_Query
Previous Message Alvaro Herrera 2024-11-13 13:40:16 Re: Fwd: A million users