Message-ID: <44c41d77-8a4f-4929-9827-0c8993a88f14@gmx.net>
Date: Wed, 13 Nov 2024 21:07:53 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Extract values from XML content
To: pgsql-general@lists.postgresql.org
References: <CAHByMH01=A2dH5ASd6tyZeRZgPnBf1CYZOC==r9=U=thYbYpKw@mail.gmail.com>
Content-Language: de-DE, en-US
From: shammat@gmx.net
In-Reply-To: <CAHByMH01=A2dH5ASd6tyZeRZgPnBf1CYZOC==r9=U=thYbYpKw@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: quoted-printable
UI-OutboundReport: notjunk:1;M01:P0:jjZlSoETIcA=;N+oQruevvQSVDze5nOU6LqNao8N
 23hN3mnzxOjvdZIzE0c7nRCMcS1X2D+vRqZXov9DRHRT3Px8efoYGvHFcwAGSQNiJBJOaKME/
 /clkQmjRjnjE6Uowixn12GGo1ZSYF8KeC4EofzbGW1Zoka+ZZ2Ezg8iulMOfBp9f7cKJTxjHV
 RhAjQTxODLX45VU8Z/GI0QBotu6Yn+U8V153BiMWuxohaR1ZhhGGTK4UlZPgszhHvP2SXrP8h
 MScCfXRCGabHyQRgJZg8X+mVy1lhu0nLwppAugx8C0+PlJhYhbF8EXLfDg9oUooZ1/0IASCpa
 iWuWDCCAVyFExJfKedw/jSxWIG4ApGFnIFH5U2ljN1cPBvyIQKyci1sovb4i8H5JPRVwix3ty
 KroeTI7AdMeknuQ6Ahgt3AtS8A4KG9jhN0Wlb46sjnvx8aKobfwDG3HbwVxkIjcWd0U+Krhot
 bKFs+wdrU5iQVAm0MOccTT8E2fC6Oihvx66mHatrLSKTXmKE5Q/HNA11aHTgG9OhZPKExdMN/
 ZfT3oT/MF6BP8aqPMoGj887TniBdDRKhzoOmNBanosyTPcnYLa3CLVbXgu85irgz3d4rVfzzv
 fJYX3nAjpsjAm0+NSU+4i5awG1sgeB+MzhAti7wQg0MuAN23U32M/AobwQUPHzpNcxARTw3S8
 grnhZrDj0jGPObir5Z75STSJDUe9JFUozquJ5N2H/oDRC0xvtnee5FD1v0z043vY/q2b58W9E
 5U9qLpnUdaZJtGdtHlBobXQVgyZBz2gISfNtiriIBYcnY8AmCPRoXgxEWaY/gpq3FDl/1hDBU
 SYXeoJE2AhPQWJqmH51lebQRa5y/lJx5CjMhUvaVp8Rzc3eFVGiYxC2uMvk5htlgdX1jAzKIO
 K3Q2Z/uf2YTjBwGwvcd3hU2Vwo7CQaKHfyzji76qdkbR6B7qLApeF8Ngz
Archived-At: <https://www.postgresql.org/message-id/44c41d77-8a4f-4929-9827-0c8993a88f14%40gmx.net>
Precedence: bulk

Am 13.11.24 um 15:58 schrieb celati Laurent:
> I have a table 'metadata' with 2000 records. With one column 'id' and on=
e 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/Characte=
rString/text()',
>  =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 CAST(data AS XML)) AS organis=
ation_name
> FROM public.metadata;
>

I typically find xmltable() a lot easier to work with, especially if the X=
ML 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]