Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tBHrX-002tll-Tb for pgsql-general@arkaria.postgresql.org; Wed, 13 Nov 2024 18:13:07 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tBHrV-00Fxy0-5N for pgsql-general@arkaria.postgresql.org; Wed, 13 Nov 2024 18:13:05 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tBHrU-00Fxxs-Ny for pgsql-general@lists.postgresql.org; Wed, 13 Nov 2024 18:13:05 +0000 Received: from mail-oi1-x229.google.com ([2607:f8b0:4864:20::229]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tBHrQ-001kzr-V2 for pgsql-general@postgresql.org; Wed, 13 Nov 2024 18:13:04 +0000 Received: by mail-oi1-x229.google.com with SMTP id 5614622812f47-3e6104701ffso4420871b6e.0 for ; Wed, 13 Nov 2024 10:13:02 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1731521580; x=1732126380; darn=postgresql.org; h=cc:to:subject:message-id:date:from:references:in-reply-to :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=ezFzmaLNzL4I9jCOiOFV4jDc8qIempX3rh1jwu4jgY8=; b=bPchyX5g9m7kNuftKE5cyQqDmrkXNkVAA8siJg9LousCDk+jSLZYzV56oAOLCueluB nJj+FJeZooHPcn/8bW06s5UkFMSffwga0DawTjItoCG7E0kC6qvBCZY8M/veZLuZ1ylw 3dOQLpCwl5ZmDoMt9I98BLwEvljrc3vqrq/rhJqd95ATSabIx1tBR2PLLzSa0CiT2Aer G6LrF0bpSCbtrqSnOX9wm64ts6FV6KMHcyAL8gNcTnMFw2d5sgOgp4LJm1bH2CkAoe6y RvHcqd5TIXFtOqe/rKoGrv6eCbtQkc+0MEI1KGVd36GpwfLQ1jX5Kh6XfsJLc8860L7q aH+w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1731521580; x=1732126380; h=cc:to:subject:message-id:date:from:references:in-reply-to :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=ezFzmaLNzL4I9jCOiOFV4jDc8qIempX3rh1jwu4jgY8=; b=qZZxR/zrNtD9IBfAUOCsGL03Kg3vCJOe6ybThAAwFmTeYjKAHj62c++lMVII0jOko4 HiXk5zxUp5YZvlnN00G+Ig9mT6JhratqKXp8XeSqysiIxcN4eDgERKcqrXwPwZaZXfZF 77cBlUqPqhoRGpAFW80nQJgIvs9khd0NmZMgWHzVvSQWhT9jh6dfgpHCWXOmXP2OCjpc FibDux5Pql4xd6TV0oxeWn9w6oSndiKgA7Pzl6vEwKWg/k7hnZBaT51W4LXFVqjzV8Mq e6VPm1uWmZ7Mv5pxYHeSL+ttvzLj6kH4kkTqdWDKWrSutt3/izUN8B4IGrV7m1JBKCTV hTLw== X-Gm-Message-State: AOJu0YzB5mw39wD3yG3/fRBov8pjU9+WATxDxde4cM09VMf4Qh/3iL/p t/JkC/ffAz3lpkj8CdCVkULwnwBUgfc66vN0ozR7da2EZw8rMIMsNZdLsfu64QhsQeKdzvqrkbR 4YCQnE2kRq62vLpsuXszS+YKcB+o= X-Google-Smtp-Source: AGHT+IHVxlYM8ROjk1Uilc1L2e+d7vnCXM6lXICwrAO4tyiNsGFeNi0xBS2wZfxU4CRlrRXhCKb5hQfcVlkqp3MdQdA= X-Received: by 2002:a05:6808:1390:b0:3e5:fbdd:e33a with SMTP id 5614622812f47-3e794699381mr20103745b6e.13.1731521580616; Wed, 13 Nov 2024 10:13:00 -0800 (PST) MIME-Version: 1.0 Received: by 2002:a05:6802:27c2:b0:56c:c9af:3ee6 with HTTP; Wed, 13 Nov 2024 10:13:00 -0800 (PST) In-Reply-To: References: From: "David G. Johnston" Date: Wed, 13 Nov 2024 11:13:00 -0700 Message-ID: Subject: Re: Extract values from XML content To: celati Laurent Cc: "pgsql-general@postgresql.org" Content-Type: multipart/alternative; boundary="000000000000bbd4690626cf4882" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000bbd4690626cf4882 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Wednesday, November 13, 2024, celati Laurent wrote: > > > SELECT id, xpath('/contact/CI_ResponsibleParty/organisationName/Character= String/text()', > > CAST(data AS XML)) AS organisation_name > > > > > > > > > Office fran=C3=A7ais de la biodiversit= =C3=A9 > > > I=E2=80=99d look into how to specify namespaces since your XML elements all= have them but you don=E2=80=99t have any in your xpath. David J. --000000000000bbd4690626cf4882 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Wednesday, November 13, 2024, celati Laurent <laurent.celati@gmail.com> wrote:

SELECT id, xpath('= /contact/CI_ResponsibleParty/organisationName/CharacterStrin= g/text()',
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 CAST(data AS X= ML)) AS organisation_name=C2=A0<mri:point= OfContact>
    <cit:CI_Responsi=
bility>
      <=
cit:role>
        <cit:CI_RoleCode codeList=3D"http://standards.iso.org/iso/19115/resources/Codelists/cat/co=
delists.xml#CI_RoleCode" codeListValue=3D"originator=
" />
      </cit:role>
      <=
cit:party>
        <cit:CI_Organisation>
          <cit:name>
            <gco:CharacterString>Office fran=C3=A7ais de la biodi=
versit=C3=A9</gco:CharacterString>
          </cit:name>

I=E2=80=99d lo= ok into how to specify namespaces since your XML elements all have them but= you don=E2=80=99t have any in your xpath.

David J= .
=C2=A0
--000000000000bbd4690626cf4882--