public inbox for [email protected]  

help / color / mirror / Atom feed

From: [email protected]
To: [email protected]
Subject: Re: Extract values from XML content
Date: Wed, 13 Nov 2024 21:07:53 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAHByMH01=A2dH5ASd6tyZeRZgPnBf1CYZOC==r9=U=thYbYpKw@mail.gmail.com>
References: <CAHByMH01=A2dH5ASd6tyZeRZgPnBf1CYZOC==r9=U=thYbYpKw@mail.gmail.com>

Am 13.11.24 um 15:58 schrieb celati Laurent:
> 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;
>

I typically find xmltable() a lot easier to work with, especially if the XML contains namespaces.

I guess the namespaces are the problem in your case, you will have to pass them to the xpath()
function and reference them in the path expression as well

So you will need something like:

xpath('//cit:CI_Responsibility/cit:party/cit:CI_Organisation/cit:name/gco:CharacterString/text()',
        cast(data as xml),
        ARRAY[ARRAY['cit', 'http://...';], array['gco', 'http://...';]])

The actual value for the namespace URIs depends on the definition in your XML

Note that xpath() returns an array, so you probably want (xpath(....))[1]

view thread (2+ messages)

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], [email protected]
  Subject: Re: Extract values from XML content
  In-Reply-To: <[email protected]>

* 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