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