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 1tBHmJ-002tOa-Gw for pgsql-general@arkaria.postgresql.org; Wed, 13 Nov 2024 18:07:43 +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 1tBHmG-00FsuP-RN for pgsql-general@arkaria.postgresql.org; Wed, 13 Nov 2024 18:07:41 +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 1tBEpL-00EutM-Sj for pgsql-general@lists.postgresql.org; Wed, 13 Nov 2024 14:58:40 +0000 Received: from mail-vk1-xa31.google.com ([2607:f8b0:4864:20::a31]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tBEpE-001jas-Iz for pgsql-general@postgresql.org; Wed, 13 Nov 2024 14:58:39 +0000 Received: by mail-vk1-xa31.google.com with SMTP id 71dfb90a1353d-5142468fa79so1640216e0c.3 for ; Wed, 13 Nov 2024 06:58:33 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1731509912; x=1732114712; darn=postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=ecghDYPq8q3nmmSrKelk68KPnt1iOuWrbS5lZTPFOCQ=; b=jL6Sf6YZyK2xZ83v89P6MSi3GjcME4pH1Z9LSkWYR2+4KB4V5ma4Uzz4uRtB5vozYP 03rJcIJrk1POzrVfAg2HNU8WFJ5Jiz0122HYMl4zzLOb3OEXdBHbYpIlnQcuNMrgXh2n uHqkeQF+ulI6/d0ybf5xxIC6gR/HravJ1THzNQrDMUhsfc0ACk/iuAQ9gFodUdDw2lM6 /RYFp/3ryRJuJFAeHaPQetH20TDhkvRB59PzxfxkvDZE9bQHCUJDmfaVO+QZyprP8ssg BB3d2vMhoTg+3PjUOl8jOwl9Ch3c9Ed/WrxhJkGSEOxoyQX7BugVt5mlB0JxDPcxPClQ 7zpA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1731509912; x=1732114712; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=ecghDYPq8q3nmmSrKelk68KPnt1iOuWrbS5lZTPFOCQ=; b=dyAACZduIUPSXqm5tW+cPkPzOOMYsh/qJMaoo+TczMAs3xZeCQM1qM793vAXnuUZZ9 aO4b7g8A1D59ObuQKxvVS3igqQZGbfAdZlxos9M0KTlgMdBZybJsGR/mlhJVYyP+NUsQ GfvldHhqXcbNHopYepch0ZgMAjAyqB4x0bsUShfccCTbqf7A3Kt7Gi3JFASr2Y/8j7h4 rzQRnDy0gpfzmj/778n6sX5vSSVxXXNXpyxGFA9lHgPlN7W/pVOC3ABxpSf8hgXFAPwU j124bi7ZwLtMlWvjxYNJOF1smFOMCjxcVLGH0JEEfjlDu8OCK+6cQV3Rz5wbuWohjC9t jEOg== X-Gm-Message-State: AOJu0YzKxD7hLAonqC07J78SIcpuhGxeXeYsPVp1FG1HA3elobgR0mix PB67RVRF1dTmxmTxgWwAmLfXSBDxl0my59VZIJVc2RbV798TSNDNuD3y8r6zLjqCEgh3m3mU5p6 mlpEXbz7S/eQ0tc9cXR+UhxovqNL22yKh X-Google-Smtp-Source: AGHT+IHRPstAkaOp+vo4Ck9UByMXS/0Kgey2u3zPVhSyAk99TGHGnuRLJNTOMINFWwUwowgI+Kfhuct1n5rx08EW4PU= X-Received: by 2002:a05:6122:1e08:b0:50d:3ec1:1546 with SMTP id 71dfb90a1353d-51454270425mr3800750e0c.4.1731509911873; Wed, 13 Nov 2024 06:58:31 -0800 (PST) MIME-Version: 1.0 From: celati Laurent Date: Wed, 13 Nov 2024 15:58:21 +0100 Message-ID: Subject: Extract values from XML content To: pgsql-general@postgresql.org Content-Type: multipart/alternative; boundary="00000000000038eea20626cc91fb" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000038eea20626cc91fb Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 : Office fran=C3=A7ais de la biodiversit=C3=A9 Thanks so much. --00000000000038eea20626cc91fb Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Good afternoon,
I have a table 'metadat= a' 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.=C2=A0
I success in querying wi= thout error message thanks to this following sql query :

= SELECT id, xpath('/contact/CI_ResponsibleParty/organisationName/Charact= erString/text()',
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 CAST(da= ta AS XML)) AS organisation_name
FROM public.metadata;

= But the values don't appear into the column :

"id"=C2=A0 =C2=A0 "organisation_name"
16= 410=C2=A0 =C2=A0 "[]"
16411=C2=A0 =C2=A0 "[]"
164= 12=C2=A0 =C2=A0 "[]"
16413=C2=A0 =C2=A0 "[]"
1641= 4=C2=A0 =C2=A0 "[]"
16415=C2=A0 =C2=A0 "[]"
16416= =C2=A0 =C2=A0 "[]"
16423=C2=A0 =C2=A0 "[]"
16425= =C2=A0 =C2=A0 "[]"
16426=C2=A0 =C2=A0 "[]"
16427= =C2=A0 =C2=A0 "[]"
16435=C2=A0 =C2=A0 "[]"
2250 = =C2=A0 =C2=A0 "[]"
16587=C2=A0 =C2=A0 "[]"
16588= =C2=A0 =C2=A0 "[]"


If needed, i past= e below=C2=A0the FULL extract of the XLM content up to my section of intere= st :

<mri:pointOfContact>
    <cit:CI_Responsibility>
      <cit:role>
        <cit:CI_RoleCode codeList=3D"http=
://standards.iso.org/iso/19115/resources/Codelists/cat/codelists.xml#CI_Rol=
eCode" codeListValue=3D"originator" />
      </cit:role>
      <cit:party>
        <cit:CI_Organisation>
          <cit:name>
            <gco:CharacterString&g=
t;Office fran=C3=A7ais de la biodiversit=C3=A9</gco:CharacterString>
          </cit:name>


Thanks so much.


--00000000000038eea20626cc91fb--