public inbox for [email protected]  

help / color / mirror / Atom feed

Extract values from XML content
2+ messages / 2 participants
[nested] [flat]

* Extract values from XML content
@ 2024-11-13 14:58 celati Laurent <[email protected]>
  2024-11-13 20:07 ` Re: Extract values from XML content [email protected]
  0 siblings, 1 reply; 2+ messages in thread

From: celati Laurent @ 2024-11-13 14:58 UTC (permalink / raw)
  To: 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.


^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Extract values from XML content
  2024-11-13 14:58 Extract values from XML content celati Laurent <[email protected]>
@ 2024-11-13 20:07 ` [email protected]
  0 siblings, 0 replies; 2+ messages in thread

From: [email protected] @ 2024-11-13 20:07 UTC (permalink / raw)
  To: [email protected]

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]









^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2024-11-13 20:07 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-11-13 14:58 Extract values from XML content celati Laurent <[email protected]>
2024-11-13 20:07 ` [email protected]

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