public inbox for [email protected]  

help / color / mirror / Atom feed
From: celati Laurent <[email protected]>
To: [email protected]
Subject: Extract values from XML content
Date: Wed, 13 Nov 2024 15:58:21 +0100
Message-ID: <CAHByMH01=A2dH5ASd6tyZeRZgPnBf1CYZOC==r9=U=thYbYpKw@mail.gmail.com> (raw)

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.


view thread (2+ messages)  latest in thread

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected]
  Subject: Re: Extract values from XML content
  In-Reply-To: <CAHByMH01=A2dH5ASd6tyZeRZgPnBf1CYZOC==r9=U=thYbYpKw@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox