Nous utilisons des cookies pour vous garantir la meilleure expérience sur notre site. Si vous continuez à utiliser ce dernier, nous considèrerons que vous acceptez l'utilisation des cookies. J'ai compris ! ou En savoir plus !.
banniere

Le portail francophone de la géomatique


Toujours pas inscrit ? Mot de passe oublié ?
Nom d'utilisateur    Mot de passe              Toujours pas inscrit ?   Mot de passe oublié ?

Annonce

Pour sa 21ème année, l’association GeoRezo a toujours besoin de vous !

10€ = 1 mois de frais bancaires ; 15€ = 12 mois de nom de domaine ; 30€ = 1 semaine de location des serveurs …

Faire un don 

Retrouver nos membres bienfaiteurs

Pages: 1

 

#1 Wed 13 November 2024 11:28

image95
Participant assidu
Date d'inscription: 6 Sep 2014
Messages: 259

Postgresql : extraction de valeur au sein d'un champ XML

Bonjour,
Je travaille avec postgresql 14.
J'ai une table 'metadata' avec 2000 enregistrements. Au sein de cette table, il y a notamment un champ 'id' et un champ 'data_c' contenant du XML.
Mon but serait de pouvoir extraire pour tous les enregistrements les valeurs du 'cit:name' que je mets volontairement en gras ci dessous. Je colle un extrait du xml ci dessous :


        <cit:CI_Responsibility>
          <cit:role>
            <cit:CI_RoleCode codeList="http://standards.iso.org/iso/19115/resources/Codelists/cat/codelists.xml#CI_RoleCode" codeListValue="owner" />
          </cit:role>
          <cit:party>
            <cit:CI_Organisation>
              <cit:name>
                <gco:CharacterString>Agence de l'Eau Artois-Picardie</gco:CharacterString>


Lorsque je fais un test :

Code:

SELECT id, unnest(xpath('./cit:name/text()', data_c::xml) AS organism
FROM   public.metadata;

j'obtiens


Code:

ERROR:  syntax error at or near "AS"
LINE 1: ...LECT id, (xpath('./cit:name/text()', data_c::xml) AS organis...
                                                             ^ 

SQL state: 42601
Character: 53

Si une personne pense pouvoir m'aider ? Un grand merci.

Dernière modification par image95 (Wed 13 November 2024 11:39)

Hors ligne

 

#2 Wed 13 November 2024 12:26

Nicolas Ribot
Membre
Lieu: Toulouse
Date d'inscription: 9 Sep 2005
Messages: 1554

Re: Postgresql : extraction de valeur au sein d'un champ XML

Bonjour

Il faut fermer une parenthèse apres "...::xml) " je pense:

::xml))

Pour écrire le SQL, je vous conseille d'utiliser un client qui affiche les ouvertures/fermetures de blocs (comme les parenthèses, les crochets, etc) comme par ex pgadmin, dbeaver, ou mieux IntelliJ

En général les messages d'erreurs SQL sont clairs: là PG vous dit qu'il y a une erreur de syntaxe juste avant AS

Nicolas

Hors ligne

 

#3 Wed 13 November 2024 14:43

image95
Participant assidu
Date d'inscription: 6 Sep 2014
Messages: 259

Re: Postgresql : extraction de valeur au sein d'un champ XML

Bonjour,
Merci. En effet. je n'i plus de message d'erreur désormais. Mais les valeurs n'apparaissent pas dans la colonne. Ma nouvelle requete SQL est :

Code:

SELECT id,
    xpath('/contact/CI_Responsability/party/CI_Organisation/name/CharacterString/text()', 
            CAST(data_c AS XML)) AS organisation_name
            FROM public.metadata;

Et voici ce que j'obtiens (pas de valeurs récupérées) :


"id"    "organisation_name"
16410    "[]"
16411    "[]"
16412    "[]"
16413    "[]"
16414    "[]"
16415    "[]"
16416    "[]"
16423    "[]"
16425    "[]"
16426    "[]"
16427    "[]"
16435    "[]"
2250     "[]"
16587    "[]"
16588    "[]"

Dernière modification par image95 (Wed 13 November 2024 14:44)

Hors ligne

 

#4 Wed 13 November 2024 15:17

Nicolas Ribot
Membre
Lieu: Toulouse
Date d'inscription: 9 Sep 2005
Messages: 1554

Re: Postgresql : extraction de valeur au sein d'un champ XML

Bonjour,

Vous pouvez demander en xpath tous les élements d'un certain tag, par exemple:

Code:

with xml_data as (
    select 1 as id, $$
<CI_Responsibility>
    <role>
        <CI_RoleCode codeList="http://standards.iso.org/iso/19115/resources/Codelists/cat/codelists.xml#CI_RoleCode" codeListValue="owner" />
    </role>
    <party>
        <CI_Organisation>
            <name>
                <CharacterString>Agence de l'Eau Artois-Picardie</CharacterString>
            </name>
        </CI_Organisation>
        <CI_Organisation>
            <name>
                <CharacterString>Agence de l'Eau Seine Normandie</CharacterString>
            </name>
        </CI_Organisation>
        <CI_Organisation>
            <name>
                <CharacterString>Agence de l'Eau Adour Garonne</CharacterString>
            </name>
        </CI_Organisation>
    </party>
</CI_Responsibility>
$$::xml as doc
)
select id, unnest(xpath('//CharacterString/text()', doc)) as orga_name
from xml_data;

+--+-------------------------------+
|id|orga_name                      |
+--+-------------------------------+
|1 |Agence de l'Eau Artois-Picardie|
|1 |Agence de l'Eau Seine Normandie|
|1 |Agence de l'Eau Adour Garonne  |
+--+-------------------------------+

Nicolas

Hors ligne

 

#5 Wed 13 November 2024 15:37

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 Jul 2010
Messages: 1166

Re: Postgresql : extraction de valeur au sein d'un champ XML

Bonjour,

je crois qu'il y a une erreur de typo :

CI_Responsability dans votre requête xpath, CI_Responsibility dans votre xml.

Dernière modification par tumasgiu (Wed 13 November 2024 15:38)

Hors ligne

 

#6 Wed 13 November 2024 15:47

image95
Participant assidu
Date d'inscription: 6 Sep 2014
Messages: 259

Re: Postgresql : extraction de valeur au sein d'un champ XML

Merci pour vos réponses. Afin d'etre plus clair et complet, je me permets de coller l'intégralité de mon contenu XML jusqu'à la balise pour laquelle j'aimerais pouvoir récupérer les valeurs de mes 2000 enregistrements. J'aimerais pouvoir récupérer les infos situées au niveau de :

   

Code:

<gmd:organisationName>
        <gco:CharacterString>SANDRE</gco:CharacterString>

Ci dessous le contenu XML jusqu'à la balise d'intéret :

Code:

<gmd:MD_Metadata xmlns:gmd="http://www.isotc211.org/2005/gmd" xmlns:gco="http://www.isotc211.org/2005/gco" xmlns:gml="http://www.opengis.net/gml/3.2" xmlns:gts="http://www.isotc211.org/2005/gts" xmlns:geonet="http://www.fao.org/geonetwork" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.isotc211.org/2005/gmd http://schemas.opengis.net/csw/2.0.2/profiles/apiso/1.0.0/apiso.xsd">
  <gmd:fileIdentifier>
    <gco:CharacterString>1f5b7076-5bff-4394-8c7e-691a047e2af5</gco:CharacterString>
  </gmd:fileIdentifier>
  <gmd:language>
    <gco:CharacterString>fre</gco:CharacterString>
  </gmd:language>
  <gmd:characterSet>
    <gmd:MD_CharacterSetCode codeListValue="utf8" codeList="http://standards.iso.org/iso/19139/resources/gmxCodelists.xml#MD_CharacterSetCode" />
  </gmd:characterSet>
  <gmd:parentIdentifier>
    <gco:CharacterString>3409c9c3-9836-43be-bac3-b110c82b3a25</gco:CharacterString>
  </gmd:parentIdentifier>
  <gmd:hierarchyLevel>
    <gmd:MD_ScopeCode codeListValue="dataset" codeList="http://standards.iso.org/iso/19139/resources/gmxCodelists.xml#MD_ScopeCode" />
  </gmd:hierarchyLevel>
  <gmd:hierarchyLevelName>
    <gco:CharacterString>dataset</gco:CharacterString>
  </gmd:hierarchyLevelName>
  <gmd:contact>
    <gmd:CI_ResponsibleParty>
      <gmd:individualName gco:nilReason="missing">
        <gco:CharacterString />
      </gmd:individualName>
      <gmd:organisationName>
        <gco:CharacterString>SANDRE</gco:CharacterString>
      </gmd:organisationName>

Hors ligne

 

#7 Wed 13 November 2024 16:05

Nicolas Ribot
Membre
Lieu: Toulouse
Date d'inscription: 9 Sep 2005
Messages: 1554

Re: Postgresql : extraction de valeur au sein d'un champ XML

Un document xml n'est parsable que s'il est complet, pas juste le début du doc.

Vous pouvez soit utiliser xpath sur un doc xml complet et valide dans PG, ou utiliser des fonctions de recherche style regex sur une colonne text, le doc xml n'a pas à être valide dans ce cas.

Je vous conseillerais de faire avec xpath et dans votre cas, il faut je pense déclarer les namespaces dans la fonction xpath (https://www.postgresql.org/docs/current … SING-XPATH).

Un path du type "//gmd:organisationName/gco:CharacterString/text()" devrait extraire tous les textes pour tous les tags CharacterString enfants d'un tag organisationName, peu importe leur niveau d'imbrication (//)
(mais bon, je suis nul en xpath donc pas sûr smile Il y a des tutos bien faits sur xpath pour comprendre la navigation et les accesseurs)

Ils viennent d'un service OGC du Sandre ces XML ? Vous avez la source d'un doc complet ?

Nicolas

Hors ligne

 

#8 Wed 13 November 2024 16:20

image95
Participant assidu
Date d'inscription: 6 Sep 2014
Messages: 259

Re: Postgresql : extraction de valeur au sein d'un champ XML

Un grand merci pour votre aide. Il n'y a pas plus novice que moi sur ce sujet. Je découvre xpath et la manipulation/extraction d'info xml depuis une bdd postgresql. Je vais donc suivre vos conseils. Ces xml viennent du catalogue de l'ofb. Il peut y avoir des fiches 'locales' et des fiches moissonnées. Je vous partage un fichier xml complet si besoin :

Code:

<?xml version="1.0" encoding="UTF-8"?>
<mdb:MD_Metadata xmlns:mdb="http://standards.iso.org/iso/19115/-3/mdb/2.0" xmlns:cat="http://standards.iso.org/iso/19115/-3/cat/1.0" xmlns:gfc="http://standards.iso.org/iso/19110/gfc/1.1" xmlns:cit="http://standards.iso.org/iso/19115/-3/cit/2.0" xmlns:gcx="http://standards.iso.org/iso/19115/-3/gcx/1.0" xmlns:gex="http://standards.iso.org/iso/19115/-3/gex/1.0" xmlns:lan="http://standards.iso.org/iso/19115/-3/lan/1.0" xmlns:srv="http://standards.iso.org/iso/19115/-3/srv/2.1" xmlns:mas="http://standards.iso.org/iso/19115/-3/mas/1.0" xmlns:mcc="http://standards.iso.org/iso/19115/-3/mcc/1.0" xmlns:mco="http://standards.iso.org/iso/19115/-3/mco/1.0" xmlns:mda="http://standards.iso.org/iso/19115/-3/mda/1.0" xmlns:mds="http://standards.iso.org/iso/19115/-3/mds/2.0" xmlns:mdt="http://standards.iso.org/iso/19115/-3/mdt/2.0" xmlns:mex="http://standards.iso.org/iso/19115/-3/mex/1.0" xmlns:mmi="http://standards.iso.org/iso/19115/-3/mmi/1.0" xmlns:mpc="http://standards.iso.org/iso/19115/-3/mpc/1.0" xmlns:mrc="http://standards.iso.org/iso/19115/-3/mrc/2.0" xmlns:mrd="http://standards.iso.org/iso/19115/-3/mrd/1.0" xmlns:mri="http://standards.iso.org/iso/19115/-3/mri/1.0" xmlns:mrl="http://standards.iso.org/iso/19115/-3/mrl/2.0" xmlns:mrs="http://standards.iso.org/iso/19115/-3/mrs/1.0" xmlns:msr="http://standards.iso.org/iso/19115/-3/msr/2.0" xmlns:mdq="http://standards.iso.org/iso/19157/-2/mdq/1.0" xmlns:mac="http://standards.iso.org/iso/19115/-3/mac/2.0" xmlns:gco="http://standards.iso.org/iso/19115/-3/gco/1.0" xmlns:gml="http://www.opengis.net/gml/3.2" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://standards.iso.org/iso/19115/-3/mdb/2.0 https://schemas.isotc211.org/19115/-3/mdb/2.0/mdb.xsd">
  <mdb:metadataIdentifier>
    <mcc:MD_Identifier>
      <mcc:code>
        <gco:CharacterString>34831b8d-9da1-4a19-b425-482f982381d9</gco:CharacterString>
      </mcc:code>
      <mcc:codeSpace>
        <gco:CharacterString>urn:uuid</gco:CharacterString>
      </mcc:codeSpace>
    </mcc:MD_Identifier>
  </mdb:metadataIdentifier>
  <mdb:defaultLocale>
    <lan:PT_Locale id="FR">
      <lan:language>
        <lan:LanguageCode codeList="http://www.loc.gov/standards/iso639-2/" codeListValue="fre" />
      </lan:language>
      <lan:characterEncoding>
        <lan:MD_CharacterSetCode codeList="http://standards.iso.org/iso/19115/resources/Codelists/cat/codelists.xml#MD_CharacterSetCode" codeListValue="utf8" />
      </lan:characterEncoding>
    </lan:PT_Locale>
  </mdb:defaultLocale>
  <mdb:metadataScope>
    <mdb:MD_MetadataScope>
      <mdb:resourceScope>
        <mcc:MD_ScopeCode codeList="http://standards.iso.org/iso/19115/resources/Codelists/cat/codelists.xml#MD_ScopeCode" codeListValue="dataset" />
      </mdb:resourceScope>
      <mdb:name>
        <gco:CharacterString>Collection de données</gco:CharacterString>
      </mdb:name>
    </mdb:MD_MetadataScope>
  </mdb:metadataScope>
  <mdb:contact>
    <cit:CI_Responsibility>
      <cit:role>
        <cit:CI_RoleCode codeList="http://standards.iso.org/iso/19115/resources/Codelists/cat/codelists.xml#CI_RoleCode" codeListValue="pointOfContact" />
      </cit:role>
      <cit:party>
        <cit:CI_Organisation>
          <cit:name>
            <gco:CharacterString>Office français de la biodiversité</gco:CharacterString>
          </cit:name>
          <cit:contactInfo>
            <cit:CI_Contact>
              <cit:address>
                <cit:CI_Address>
                  <cit:electronicMailAddress>
                    <gco:CharacterString>sig@ogb.gouv.fr</gco:CharacterString>
                  </cit:electronicMailAddress>
                </cit:CI_Address>
              </cit:address>
            </cit:CI_Contact>
          </cit:contactInfo>
          <cit:individual>
            <cit:CI_Individual>
              <cit:name>
                <gco:CharacterString>Délégation de façade Atlantique - Pôle géomatique</gco:CharacterString>
              </cit:name>
              <cit:positionName>
                <gco:CharacterString>Délégation de façade Atlantique</gco:CharacterString>
              </cit:positionName>
            </cit:CI_Individual>
          </cit:individual>
        </cit:CI_Organisation>
      </cit:party>
    </cit:CI_Responsibility>
  </mdb:contact>
  <mdb:dateInfo>
    <cit:CI_Date>
      <cit:date>
        <gco:DateTime>2020-10-22T13:06:46Z</gco:DateTime>
      </cit:date>
      <cit:dateType>
        <cit:CI_DateTypeCode codeList="http://standards.iso.org/iso/19115/resources/Codelists/cat/codelists.xml#CI_DateTypeCode" codeListValue="creation" />
      </cit:dateType>
    </cit:CI_Date>
  </mdb:dateInfo>
  <mdb:dateInfo>
    <cit:CI_Date>
      <cit:date>
        <gco:DateTime>2021-09-30T08:10:14Z</gco:DateTime>
      </cit:date>
      <cit:dateType>
        <cit:CI_DateTypeCode codeList="http://standards.iso.org/iso/19115/resources/Codelists/cat/codelists.xml#CI_DateTypeCode" codeListValue="revision" />
      </cit:dateType>
    </cit:CI_Date>
  </mdb:dateInfo>
  <mdb:metadataStandard>
    <cit:CI_Citation>
      <cit:title>
        <gco:CharacterString>ISO 19115-3:2018</gco:CharacterString>
      </cit:title>
      <cit:edition>
        <gco:CharacterString>1.0</gco:CharacterString>
      </cit:edition>
    </cit:CI_Citation>
  </mdb:metadataStandard>
  <mdb:metadataLinkage>
    <cit:CI_OnlineResource>
      <cit:linkage>
        <gco:CharacterString>https://data.ofb.fr/geonetwork/srv/api/records/34831b8d-9da1-4a19-b425-482f982381d9</gco:CharacterString>
      </cit:linkage>
      <cit:function>
        <cit:CI_OnLineFunctionCode codeList="http://standards.iso.org/iso/19115/resources/Codelists/cat/codelists.xml#CI_OnLineFunctionCode" codeListValue="completeMetadata" />
      </cit:function>
    </cit:CI_OnlineResource>
  </mdb:metadataLinkage>
  <mdb:spatialRepresentationInfo>
    <msr:MD_GridSpatialRepresentation>
      <msr:numberOfDimensions>
        <gco:Integer>2</gco:Integer>
      </msr:numberOfDimensions>
      <msr:cellGeometry>
        <msr:MD_CellGeometryCode codeList="http://standards.iso.org/iso/19115/resources/Codelists/cat/codelists.xml#MD_CellGeometryCode" codeListValue="area" />
      </msr:cellGeometry>
      <msr:transformationParameterAvailability>
        <gco:Boolean>1</gco:Boolean>
      </msr:transformationParameterAvailability>
    </msr:MD_GridSpatialRepresentation>
  </mdb:spatialRepresentationInfo>
  <mdb:referenceSystemInfo>
    <mrs:MD_ReferenceSystem>
      <mrs:referenceSystemIdentifier>
        <mcc:MD_Identifier>
          <mcc:code>
            <gcx:Anchor xlink:href="http://www.opengis.net/def/crs/EPSG/0/2154">EPSG:2154</gcx:Anchor>
          </mcc:code>
          <mcc:description>
            <gco:CharacterString>RGF93 / Lambert-93 (EPSG:2154)</gco:CharacterString>
          </mcc:description>
        </mcc:MD_Identifier>
      </mrs:referenceSystemIdentifier>
    </mrs:MD_ReferenceSystem>
  </mdb:referenceSystemInfo>
  <mdb:identificationInfo>
    <mri:MD_DataIdentification gco:isoType="gmd:MD_DataIdentification">
      <mri:citation>
        <cit:CI_Citation>
          <cit:title>
            <gco:CharacterString>Zones de production ou de parcage conchylicole - Métropole (SANDRE, 10/2017) - [polygone]</gco:CharacterString>
          </cit:title>
          <cit:date>
            <cit:CI_Date>
              <cit:dateType>
                <cit:CI_DateTypeCode codeList="http://standards.iso.org/iso/19115/resources/Codelists/cat/codelists.xml#CI_DateTypeCode" codeListValue="creation" />
              </cit:dateType>
            </cit:CI_Date>
          </cit:date>
          <cit:identifier>
            <mcc:MD_Identifier>
              <mcc:code>
                <gco:CharacterString>us_met_conchy_zone_production_sandre_201710_pol_wgs84</gco:CharacterString>
              </mcc:code>
            </mcc:MD_Identifier>
          </cit:identifier>
        </cit:CI_Citation>
      </mri:citation>
      <mri:abstract>
        <gco:CharacterString>Les zones de production conchylicole sont identifiées au titre du paquet européen hygiène (CE/854/2004) et de l'arrêté du 21 mai 1999 relatif au classement de salubrité et à la surveillance des zones de production et des zones de reparcage des coquillages vivants.
L'ensemble des zones de production de coquillages (zones d’élevage et de pêche professionnelle) fait ainsi l’objet d’un classement sanitaire, défini par arrêté préfectoral.

Celui-ci est établi sur la base d’analyses des coquillages présents : analyses microbiologiques utilisant Escherichia coli (E. coli) comme indicateur de contamination (en nombre d’E. coli pour 100 g de chair et de liquide intervalvaire - CLI) et dosage de la contamination en métaux lourds (plomb, cadmium et mercure), exprimé en mg/kg de chair humide. 

Le classement et le suivi des zones de production de coquillages distingue 3 groupes de coquillages au regard de leur physiologie :

groupe 1 : les gastéropodes (bulots etc.), les échinodermes (oursins) et les tuniciers (violets) ; plus généralement des coquillages sauvages de gisements naturels,
groupe 2 : les bivalves fouisseurs, c’est-à-dire les mollusques bivalves filtreurs dont l’habitat est constitué par les sédiments (palourdes, coques...) ; plus généralement des coquillages sauvages de gisements naturels,
groupe 3 : les bivalves non fouisseurs, c’est-à-dire les autres mollusques bivalves filtreurs (huîtres, moules...) ; plus généralement des coquillages d'élevage.

Ce concept est bien distinct de celui de 'Zone de qualité des eaux conchylicoles'.
Le contour de la Zone de production conchylicole ne correspond pas au cadastre conchylicole (= cadastre des établissements de culture marine).

Les différents classements des groupes sont les suivants :

&gt; Zones A : Zones dans lesquelles les coquillages peuvent être récoltés et mis directement sur le marché pour la consommation humaine directe après passage par un centre d'expédition agréé.

&gt; Zones B : Zones dans lesquelles les coquillages peuvent être récoltés mais ne peuvent être mis sur le marché pour la consommation humaine qu'après avoir été traités dans un centre de purification agréé ou après reparcage dans une zone spécifiquement agréée pour cette opération.

&gt; Zones C : Zones dans lesquelles les coquillages peuvent être récoltés mais ne peuvent être mis sur le marché pour la consommation humaine qu'après un reparcage de longue durée dans une zone agréée à cet effet ou après traitement thermique dans un établissement agréé.

&gt; Zones NC (zones non classées) : en l'absence de classement sanitaire, les activités de pêche ou d’élevage n'y sont pas autorisées. Seuls les pectinidés (coquilles Saint-Jacques, pétoncles), les gastéropodes non filtreurs (notamment bulots, ormeaux, patelles) et les échinodermes peuvent y être récoltés, sauf spécifications contraires.

&gt; Zones à exploitation occasionnelle (EO) dites "à éclipses" : zones dans lesquelles la récolte et la commercialisation de coquillages sont soumises à autorisation préalable et sous conditions particulières (arrêté préfectoral spécifique lors de l'exploitation).

&gt; Zones Interdites (I) : Zones d'activités portuaires et/ou zones polluées (zones autour d'émissaires de rejets ...), dans lesquelles aucune activité de pêche, de production ou de récolte de coquillage ne peut être pratiquée, quel que soit le groupe.



La table attributaire est composée de 13 champs :
- id : Identifiant
- gid : ?
- codenatzon : Ce code est l’identifiant national de la Zone de production conchylicole.
- typezone : Classification de la zone 
- nomzone : Nom de la zone de production conchylicole tel que défini par la DDTM.
- stzone : Le statut de la zone est défini dans la nomenclature 390
- datetexter : Ce texte correspond à la date de l'arrêté de délimitation
- urltexter : URL du texte réglementaire
- typeclasse : Code du Type de classement de coquillage
- gp1 : classement du groupe 1
- gp2 : classement du groupe 2
- gp3 : classement du groupe 3
- datemajzon</gco:CharacterString>
      </mri:abstract>
      <mri:purpose gco:nilReason="missing">
        <gco:CharacterString />
      </mri:purpose>
      <mri:credit>
        <gco:CharacterString>AAMP/PNMI, 2016</gco:CharacterString>
      </mri:credit>
      <mri:status>
        <mcc:MD_ProgressCode codeList="http://standards.iso.org/iso/19115/resources/Codelists/cat/codelists.xml#MD_ProgressCode" codeListValue="completed" />
      </mri:status>
      <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="owner" />
          </cit:role>
          <cit:party>
            <cit:CI_Organisation>
              <cit:name>
                <gco:CharacterString>Office français de la biodiversité</gco:CharacterString>
              </cit:name>
              <cit:contactInfo>
                <cit:CI_Contact>
                  <cit:address>
                    <cit:CI_Address>
                      <cit:electronicMailAddress>
                        <gco:CharacterString>sig@ogb.gouv.fr</gco:CharacterString>
                      </cit:electronicMailAddress>
                    </cit:CI_Address>
                  </cit:address>
                </cit:CI_Contact>
              </cit:contactInfo>
              <cit:individual>
                <cit:CI_Individual>
                  <cit:name>
                    <gco:CharacterString>Pôle géomatique</gco:CharacterString>
                  </cit:name>
                  <cit:positionName gco:nilReason="missing">
                    <gco:CharacterString />
                  </cit:positionName>
                </cit:CI_Individual>
              </cit:individual>
            </cit:CI_Organisation>
          </cit:party>
        </cit:CI_Responsibility>
      </mri:pointOfContact>
      <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="custodian" />
          </cit:role>
          <cit:party>
            <cit:CI_Organisation>
              <cit:name>
                <gco:CharacterString>Office français de la biodiversité</gco:CharacterString>
              </cit:name>
              <cit:contactInfo>
                <cit:CI_Contact>
                  <cit:address>
                    <cit:CI_Address>
                      <cit:deliveryPoint gco:nilReason="missing">
                        <gco:CharacterString />
                      </cit:deliveryPoint>
                      <cit:city>
                        <gco:CharacterString>Nantes</gco:CharacterString>
                      </cit:city>
                      <cit:postalCode>
                        <gco:CharacterString>44100</gco:CharacterString>
                      </cit:postalCode>
                      <cit:country>
                        <gco:CharacterString>FRANCE</gco:CharacterString>
                      </cit:country>
                      <cit:electronicMailAddress>
                        <gco:CharacterString>melanie.odion@ofb.gouv.fr</gco:CharacterString>
                      </cit:electronicMailAddress>
                    </cit:CI_Address>
                  </cit:address>
                </cit:CI_Contact>
              </cit:contactInfo>
              <cit:individual>
                <cit:CI_Individual>
                  <cit:name>
                    <gco:CharacterString>ODION Mélanie</gco:CharacterString>
                  </cit:name>
                  <cit:positionName>
                    <gco:CharacterString>Délégation de façade Atlantique</gco:CharacterString>
                  </cit:positionName>
                </cit:CI_Individual>
              </cit:individual>
            </cit:CI_Organisation>
          </cit:party>
        </cit:CI_Responsibility>
      </mri:pointOfContact>
      <mri:topicCategory>
        <mri:MD_TopicCategoryCode>environment</mri:MD_TopicCategoryCode>
      </mri:topicCategory>
      <mri:extent>
        <gex:EX_Extent>
          <gex:description>
            <gco:CharacterString>France</gco:CharacterString>
          </gex:description>
          <gex:geographicElement>
            <gex:EX_GeographicBoundingBox>
              <gex:westBoundLongitude>
                <gco:Decimal>-5.05085</gco:Decimal>
              </gex:westBoundLongitude>
              <gex:eastBoundLongitude>
                <gco:Decimal>6.66009</gco:Decimal>
              </gex:eastBoundLongitude>
              <gex:southBoundLatitude>
                <gco:Decimal>41.7877</gco:Decimal>
              </gex:southBoundLatitude>
              <gex:northBoundLatitude>
                <gco:Decimal>51.31309</gco:Decimal>
              </gex:northBoundLatitude>
            </gex:EX_GeographicBoundingBox>
          </gex:geographicElement>
          <gex:temporalElement>
            <gex:EX_TemporalExtent>
              <gex:extent>
                <gml:TimePeriod gml:id="N1037D">
                  <gml:beginPosition />
                  <gml:endPosition />
                </gml:TimePeriod>
              </gex:extent>
            </gex:EX_TemporalExtent>
          </gex:temporalElement>
        </gex:EX_Extent>
      </mri:extent>
      <mri:resourceMaintenance>
        <mmi:MD_MaintenanceInformation>
          <mmi:maintenanceAndUpdateFrequency>
            <mmi:MD_MaintenanceFrequencyCode codeList="http://standards.iso.org/iso/19115/resources/Codelists/cat/codelists.xml#MD_MaintenanceFrequencyCode" codeListValue="unknown" />
          </mmi:maintenanceAndUpdateFrequency>
          <mmi:updateScope>
            <mcc:MD_ScopeCode codeList="http://standards.iso.org/iso/19115/resources/Codelists/cat/codelists.xml#MD_ScopeCode" codeListValue="attribute" />
          </mmi:updateScope>
          <mmi:updateScope>
            <mcc:MD_ScopeCode codeList="http://standards.iso.org/iso/19115/resources/Codelists/cat/codelists.xml#MD_ScopeCode" codeListValue="feature" />
          </mmi:updateScope>
        </mmi:MD_MaintenanceInformation>
      </mri:resourceMaintenance>
      <mri:descriptiveKeywords>
        <mri:MD_Keywords>
          <mri:keyword>
            <gco:CharacterString>Classement sanitaire</gco:CharacterString>
          </mri:keyword>
          <mri:keyword>
            <gco:CharacterString>Zone de production</gco:CharacterString>
          </mri:keyword>
          <mri:keyword>
            <gco:CharacterString>Conchyliculture</gco:CharacterString>
          </mri:keyword>
          <mri:type>
            <mri:MD_KeywordTypeCode codeList="http://standards.iso.org/iso/19115/resources/Codelists/cat/codelists.xml#MD_KeywordTypeCode" codeListValue="theme" />
          </mri:type>
        </mri:MD_Keywords>
      </mri:descriptiveKeywords>
      <mri:descriptiveKeywords>
        <mri:MD_Keywords>
          <mri:keyword>
            <gco:CharacterString>France</gco:CharacterString>
          </mri:keyword>
          <mri:keyword gco:nilReason="missing">
            <gco:CharacterString />
          </mri:keyword>
          <mri:keyword gco:nilReason="missing">
            <gco:CharacterString />
          </mri:keyword>
          <mri:keyword gco:nilReason="missing">
            <gco:CharacterString />
          </mri:keyword>
          <mri:type>
            <mri:MD_KeywordTypeCode codeList="http://standards.iso.org/iso/19115/resources/Codelists/cat/codelists.xml#MD_KeywordTypeCode" codeListValue="place" />
          </mri:type>
        </mri:MD_Keywords>
      </mri:descriptiveKeywords>
      <mri:descriptiveKeywords>
        <mri:MD_Keywords>
          <mri:keyword>
            <gco:CharacterString>Conchyliculture</gco:CharacterString>
          </mri:keyword>
          <mri:keyword>
            <gco:CharacterString>mer</gco:CharacterString>
          </mri:keyword>
          <mri:type>
            <mri:MD_KeywordTypeCode codeList="http://standards.iso.org/iso/19115/resources/Codelists/cat/codelists.xml#MD_KeywordTypeCode" codeListValue="theme" />
          </mri:type>
          <mri:thesaurusName>
            <cit:CI_Citation>
              <cit:title>
                <gco:CharacterString>GEMET</gco:CharacterString>
              </cit:title>
              <cit:date>
                <cit:CI_Date>
                  <cit:date>
                    <gco:Date>2011-07-13</gco:Date>
                  </cit:date>
                  <cit:dateType>
                    <cit:CI_DateTypeCode codeList="http://standards.iso.org/iso/19115/resources/Codelists/cat/codelists.xml#CI_DateTypeCode" codeListValue="publication" />
                  </cit:dateType>
                </cit:CI_Date>
              </cit:date>
            </cit:CI_Citation>
          </mri:thesaurusName>
        </mri:MD_Keywords>
      </mri:descriptiveKeywords>
      <mri:descriptiveKeywords>
        <mri:MD_Keywords>
          <mri:keyword>
            <gco:CharacterString>Installations agricoles et aquacoles</gco:CharacterString>
          </mri:keyword>
          <mri:keyword>
            <gco:CharacterString>Zones de gestion, de restriction ou de réglementation et unités de déclaration</gco:CharacterString>
          </mri:keyword>
          <mri:type>
            <mri:MD_KeywordTypeCode codeList="http://standards.iso.org/iso/19115/resources/Codelists/cat/codelists.xml#MD_KeywordTypeCode" codeListValue="theme" />
          </mri:type>
          <mri:thesaurusName>
            <cit:CI_Citation>
              <cit:title>
                <gco:CharacterString>INSPIRE Feature Concept Dictionary</gco:CharacterString>
              </cit:title>
              <cit:date>
                <cit:CI_Date>
                  <cit:date>
                    <gco:Date>2008-12-05</gco:Date>
                  </cit:date>
                  <cit:dateType>
                    <cit:CI_DateTypeCode codeList="http://standards.iso.org/iso/19115/resources/Codelists/cat/codelists.xml#CI_DateTypeCode" codeListValue="publication" />
                  </cit:dateType>
                </cit:CI_Date>
              </cit:date>
            </cit:CI_Citation>
          </mri:thesaurusName>
        </mri:MD_Keywords>
      </mri:descriptiveKeywords>
      <mri:resourceConstraints>
        <mco:MD_LegalConstraints>
          <mco:useLimitation>
            <gco:CharacterString>Utilisation interdite pour un usage commercial. Modification et diffusion soumise à accord du distributeur.</gco:CharacterString>
          </mco:useLimitation>
          <mco:accessConstraints>
            <mco:MD_RestrictionCode codeList="http://standards.iso.org/iso/19115/resources/Codelists/cat/codelists.xml#MD_RestrictionCode" codeListValue="copyright" />
          </mco:accessConstraints>
          <mco:useConstraints>
            <mco:MD_RestrictionCode codeList="http://standards.iso.org/iso/19115/resources/Codelists/cat/codelists.xml#MD_RestrictionCode" codeListValue="intellectualPropertyRights" />
          </mco:useConstraints>
          <mco:otherConstraints>
            <gco:CharacterString>Citation obligatoire sur les cartes de la référence suivante : "OFB/SANDRE, 2017"</gco:CharacterString>
          </mco:otherConstraints>
          <mco:otherConstraints>
            <gco:CharacterString>Citation obligatoire dans la bibliographie de la référence suivante : "Office français de la biodiversité/Service d'Administration nationale des données et référentiels sur l'eau"</gco:CharacterString>
          </mco:otherConstraints>
        </mco:MD_LegalConstraints>
      </mri:resourceConstraints>
      <mri:resourceConstraints>
        <mco:MD_Constraints>
          <mco:useLimitation>
            <gco:CharacterString>Conditions inconnues</gco:CharacterString>
          </mco:useLimitation>
        </mco:MD_Constraints>
      </mri:resourceConstraints>
      <mri:defaultLocale>
        <lan:PT_Locale>
          <lan:language>
            <lan:LanguageCode codeList="http://www.loc.gov/standards/iso639-2/" codeListValue="fre" />
          </lan:language>
          <lan:characterEncoding>
            <lan:MD_CharacterSetCode codeList="http://standards.iso.org/iso/19115/resources/Codelists/cat/codelists.xml#MD_CharacterSetCode" codeListValue="utf8" />
          </lan:characterEncoding>
        </lan:PT_Locale>
      </mri:defaultLocale>
      <mri:supplementalInformation>
        <gco:CharacterString>Liste des champs et description</gco:CharacterString>
      </mri:supplementalInformation>
    </mri:MD_DataIdentification>
  </mdb:identificationInfo>
  <mdb:distributionInfo>
    <mrd:MD_Distribution>
      <mrd:distributor>
        <mrd:MD_Distributor>
          <mrd:distributorContact>
            <cit:CI_Responsibility>
              <cit:role>
                <cit:CI_RoleCode codeList="http://standards.iso.org/iso/19115/resources/Codelists/cat/codelists.xml#CI_RoleCode" codeListValue="distributor" />
              </cit:role>
              <cit:party>
                <cit:CI_Organisation>
                  <cit:name>
                    <gco:CharacterString>Office français de la biodiversité</gco:CharacterString>
                  </cit:name>
                  <cit:contactInfo>
                    <cit:CI_Contact>
                      <cit:phone>
                        <cit:CI_Telephone>
                          <cit:number>
                            <gco:CharacterString>0298338767</gco:CharacterString>
                          </cit:number>
                          <cit:numberType>
                            <cit:CI_TelephoneTypeCode codeList="http://standards.iso.org/iso/19115/resources/Codelists/cat/codelists.xml#CI_TelephoneTypeCode" codeListValue="voice" />
                          </cit:numberType>
                        </cit:CI_Telephone>
                      </cit:phone>
                      <cit:phone>
                        <cit:CI_Telephone>
                          <cit:number>
                            <gco:CharacterString>0298338777</gco:CharacterString>
                          </cit:number>
                          <cit:numberType>
                            <cit:CI_TelephoneTypeCode codeList="http://standards.iso.org/iso/19115/resources/Codelists/cat/codelists.xml#CI_TelephoneTypeCode" codeListValue="facsimile" />
                          </cit:numberType>
                        </cit:CI_Telephone>
                      </cit:phone>
                      <cit:address>
                        <cit:CI_Address>
                          <cit:deliveryPoint>
                            <gco:CharacterString>16 quai de la douane BP 42932</gco:CharacterString>
                          </cit:deliveryPoint>
                          <cit:city>
                            <gco:CharacterString>Brest cedex 2</gco:CharacterString>
                          </cit:city>
                          <cit:postalCode>
                            <gco:CharacterString>29229</gco:CharacterString>
                          </cit:postalCode>
                          <cit:country>
                            <gco:CharacterString>FRANCE</gco:CharacterString>
                          </cit:country>
                          <cit:electronicMailAddress>
                            <gco:CharacterString>sig@ofb.gouv.fr</gco:CharacterString>
                          </cit:electronicMailAddress>
                        </cit:CI_Address>
                      </cit:address>
                    </cit:CI_Contact>
                  </cit:contactInfo>
                  <cit:individual>
                    <cit:CI_Individual>
                      <cit:name>
                        <gco:CharacterString>Pôle géomatique</gco:CharacterString>
                      </cit:name>
                      <cit:positionName>
                        <gco:CharacterString>Appui aux politiques publiques</gco:CharacterString>
                      </cit:positionName>
                    </cit:CI_Individual>
                  </cit:individual>
                </cit:CI_Organisation>
              </cit:party>
            </cit:CI_Responsibility>
          </mrd:distributorContact>
        </mrd:MD_Distributor>
      </mrd:distributor>
      <mrd:transferOptions>
        <mrd:MD_DigitalTransferOptions>
          <mrd:onLine>
            <cit:CI_OnlineResource>
              <cit:linkage>
                <gco:CharacterString>https://ofb.gouv.fr/</gco:CharacterString>
              </cit:linkage>
              <cit:protocol>
                <gco:CharacterString>WWW:LINK-1.0-http--link</gco:CharacterString>
              </cit:protocol>
              <cit:name>
                <gco:CharacterString>Office français de la biodiversité</gco:CharacterString>
              </cit:name>
              <cit:description>
                <gco:CharacterString>Site internet de l'Office français de la biodiversité</gco:CharacterString>
              </cit:description>
              <cit:function>
                <cit:CI_OnLineFunctionCode codeList="http://standards.iso.org/iso/19115/resources/Codelists/cat/codelists.xml#CI_OnLineFunctionCode" codeListValue="information" />
              </cit:function>
            </cit:CI_OnlineResource>
          </mrd:onLine>
        </mrd:MD_DigitalTransferOptions>
      </mrd:transferOptions>
    </mrd:MD_Distribution>
  </mdb:distributionInfo>
  <mdb:dataQualityInfo>
    <mdq:DQ_DataQuality>
      <mdq:scope>
        <mcc:MD_Scope>
          <mcc:level>
            <mcc:MD_ScopeCode codeListValue="dataset" codeList="http://standards.iso.org/iso/19115/resources/Codelists/cat/codelists.xml#MD_ScopeCode" />
          </mcc:level>
        </mcc:MD_Scope>
      </mdq:scope>
      <mdq:report>
        <mdq:DQ_DomainConsistency>
          <mdq:result>
            <mdq:DQ_ConformanceResult>
              <mdq:specification>
                <cit:CI_Citation>
                  <cit:title>
                    <gco:CharacterString>INSPIRE Implementing rules</gco:CharacterString>
                  </cit:title>
                  <cit:date>
                    <cit:CI_Date>
                      <cit:date>
                        <gco:Date>2008-08-11</gco:Date>
                      </cit:date>
                      <cit:dateType>
                        <cit:CI_DateTypeCode codeList="http://standards.iso.org/iso/19115/resources/Codelists/cat/codelists.xml#CI_DateTypeCode" codeListValue="publication" />
                      </cit:dateType>
                    </cit:CI_Date>
                  </cit:date>
                  <cit:identifier>
                    <mcc:MD_Identifier>
                      <mcc:code>
                        <gco:CharacterString>-- identifiant de la spécification                          --</gco:CharacterString>
                      </mcc:code>
                      <mcc:codeSpace>
                        <gco:CharacterString>INSPIRE</gco:CharacterString>
                      </mcc:codeSpace>
                    </mcc:MD_Identifier>
                  </cit:identifier>
                </cit:CI_Citation>
              </mdq:specification>
              <mdq:explanation>
                <gco:CharacterString>Non évalué</gco:CharacterString>
              </mdq:explanation>
              <mdq:pass>
                <gco:Boolean>true</gco:Boolean>
              </mdq:pass>
            </mdq:DQ_ConformanceResult>
          </mdq:result>
        </mdq:DQ_DomainConsistency>
      </mdq:report>
      <mdq:report>
        <mdq:DQ_ThematicClassificationCorrectness />
      </mdq:report>
    </mdq:DQ_DataQuality>
  </mdb:dataQualityInfo>
  <mdb:resourceLineage>
    <mrl:LI_Lineage>
      <mrl:statement>
        <gco:CharacterString>DESCRIPTION DE LA GÉNÉALOGIE
Plusieurs événements peuvent être détaillés plus bas (respecter la chronologie)</gco:CharacterString>
      </mrl:statement>
      <mrl:scope>
        <mcc:MD_Scope>
          <mcc:level>
            <mcc:MD_ScopeCode codeList="http://standards.iso.org/iso/19115/resources/Codelists/cat/codelists.xml#MD_ScopeCode" codeListValue="dataset" />
          </mcc:level>
        </mcc:MD_Scope>
      </mrl:scope>
      <mrl:processStep>
        <mrl:LI_ProcessStep>
          <mrl:description>
            <gco:CharacterString>1/ ETAPE 1 : détailler</gco:CharacterString>
          </mrl:description>
          <mrl:processor>
            <cit:CI_Responsibility>
              <cit:role>
                <cit:CI_RoleCode codeList="http://standards.iso.org/iso/19115/resources/Codelists/cat/codelists.xml#CI_RoleCode" codeListValue="processor" />
              </cit:role>
              <cit:party>
                <cit:CI_Individual>
                  <cit:name gco:nilReason="missing">
                    <gco:CharacterString />
                  </cit:name>
                </cit:CI_Individual>
              </cit:party>
            </cit:CI_Responsibility>
          </mrl:processor>
        </mrl:LI_ProcessStep>
      </mrl:processStep>
      <mrl:processStep>
        <mrl:LI_ProcessStep>
          <mrl:description>
            <gco:CharacterString>2/ ETAPE 2 : détailler</gco:CharacterString>
          </mrl:description>
          <mrl:processor>
            <cit:CI_Responsibility>
              <cit:role>
                <cit:CI_RoleCode codeList="http://standards.iso.org/iso/19115/resources/Codelists/cat/codelists.xml#CI_RoleCode" codeListValue="processor" />
              </cit:role>
              <cit:party>
                <cit:CI_Individual>
                  <cit:name gco:nilReason="missing">
                    <gco:CharacterString />
                  </cit:name>
                </cit:CI_Individual>
              </cit:party>
            </cit:CI_Responsibility>
          </mrl:processor>
        </mrl:LI_ProcessStep>
      </mrl:processStep>
      <mrl:processStep>
        <mrl:LI_ProcessStep>
          <mrl:description>
            <gco:CharacterString>3/ ETAPE 3 : détailler</gco:CharacterString>
          </mrl:description>
          <mrl:processor>
            <cit:CI_Responsibility>
              <cit:role>
                <cit:CI_RoleCode codeList="http://standards.iso.org/iso/19115/resources/Codelists/cat/codelists.xml#CI_RoleCode" codeListValue="processor" />
              </cit:role>
              <cit:party>
                <cit:CI_Individual>
                  <cit:name gco:nilReason="missing">
                    <gco:CharacterString />
                  </cit:name>
                </cit:CI_Individual>
              </cit:party>
            </cit:CI_Responsibility>
          </mrl:processor>
        </mrl:LI_ProcessStep>
      </mrl:processStep>
    </mrl:LI_Lineage>
  </mdb:resourceLineage>
</mdb:MD_Metadata>

Hors ligne

 

#9 Wed 13 November 2024 16:45

Nicolas Ribot
Membre
Lieu: Toulouse
Date d'inscription: 9 Sep 2005
Messages: 1554

Re: Postgresql : extraction de valeur au sein d'un champ XML

Ok, dans un document xml avec des namespaces, il faut redéfinir les namespaces dans la fonction xpath, pour que PG puisse parser le doc.

Dans votre doc exemple, je n'ai pas trouvé les tags:

<gmd:organisationName>
        <gco:CharacterString>SANDRE</gco:CharacterString>

Mais pour extraire par exemple les names des CI_Organisation, vous pouvez faire:

Code:

select id, unnest(xpath(
        '//cit:CI_Organisation/cit:name/gco:CharacterString/text()',
        doc,
        ARRAY[
            ARRAY['cit', 'http://standards.iso.org/iso/19115/-3/cit/2.0'],
            ARRAY['gco', 'http://standards.iso.org/iso/19115/-3/gco/1.0']
        ])) as orga_name
from table_avec_xml;

les namespaces utilisés dans le xpath doivent etre définis dans la fonction, sous la forme d'arrays.
Vous trouvez ces valeurs dans le tag root de votre document, ou chaque namespace apparaissant dans le document (par ex gco:) est défini dans une propriété xmlns:gco=<valeur>, par ex: xmlns:gco="http://standards.iso.org/iso/19115/-3/gco/1.0"

Vous pourriez utiliser du SQL pour extraire les namespaces du document et créer les tableaux nécessaires pour la fonction xpath.

Nicolas

Dernière modification par Nicolas Ribot (Wed 13 November 2024 16:46)

Hors ligne

 

#10 Wed 13 November 2024 17:05

image95
Participant assidu
Date d'inscription: 6 Sep 2014
Messages: 259

Re: Postgresql : extraction de valeur au sein d'un champ XML

Un grand merci pour votre aide précieuse.

Hors ligne

 

#11 Wed 13 November 2024 17:21

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 Jul 2010
Messages: 1166

Re: Postgresql : extraction de valeur au sein d'un champ XML

comme expliqué par Nicolas dans le post précédent, il faut rajouter les namespaces comme troisième paramètre :

Code:

select xpath(
            '//cit:CI_Organisation/cit:name/gco:CharacterString',
            '<xml>...</xml>'::xml,
           ARRAY
[
ARRAY['mdb','http://standards.iso.org/iso/19115/-3/mdb/2.0'],
ARRAY['cat','http://standards.iso.org/iso/19115/-3/cat/1.0'],
ARRAY['gfc','http://standards.iso.org/iso/19110/gfc/1.1'],
ARRAY['cit','http://standards.iso.org/iso/19115/-3/cit/2.0'],
ARRAY['gcx','http://standards.iso.org/iso/19115/-3/gcx/1.0'],
ARRAY['gex','http://standards.iso.org/iso/19115/-3/gex/1.0'],
ARRAY['lan','http://standards.iso.org/iso/19115/-3/lan/1.0'],
ARRAY['srv','http://standards.iso.org/iso/19115/-3/srv/2.1'],
ARRAY['mas','http://standards.iso.org/iso/19115/-3/mas/1.0'],
ARRAY['mcc','http://standards.iso.org/iso/19115/-3/mcc/1.0'],
ARRAY['mco','http://standards.iso.org/iso/19115/-3/mco/1.0'],
ARRAY['mda','http://standards.iso.org/iso/19115/-3/mda/1.0'],
ARRAY['mds','http://standards.iso.org/iso/19115/-3/mds/2.0'],
ARRAY['mdt','http://standards.iso.org/iso/19115/-3/mdt/2.0'],
ARRAY['mex','http://standards.iso.org/iso/19115/-3/mex/1.0'],
ARRAY['mmi','http://standards.iso.org/iso/19115/-3/mmi/1.0'],
ARRAY['mpc','http://standards.iso.org/iso/19115/-3/mpc/1.0'],
ARRAY['mrc','http://standards.iso.org/iso/19115/-3/mrc/2.0'],
ARRAY['mrd','http://standards.iso.org/iso/19115/-3/mrd/1.0'],
ARRAY['mri','http://standards.iso.org/iso/19115/-3/mri/1.0'],
ARRAY['mrl','http://standards.iso.org/iso/19115/-3/mrl/2.0'],
ARRAY['mrs','http://standards.iso.org/iso/19115/-3/mrs/1.0'],
ARRAY['msr','http://standards.iso.org/iso/19115/-3/msr/2.0'],
ARRAY['mdq','http://standards.iso.org/iso/19157/-2/mdq/1.0'],
ARRAY['mac','http://standards.iso.org/iso/19115/-3/mac/2.0'],
ARRAY['gco','http://standards.iso.org/iso/19115/-3/gco/1.0'],
ARRAY['gml','http://www.opengis.net/gml/3.2'],
ARRAY['xlink','http://www.w3.org/1999/xlink'],
ARRAY['xsi','http://www.w3.org/2001/XMLSchema-instance']
]

La requête complète peut être trouvée ici : https://dbfiddle.uk/RTirgBnU

Dernière modification par tumasgiu (Wed 13 November 2024 17:23)

Hors ligne

 

#12 Wed 13 November 2024 17:36

Nicolas Ribot
Membre
Lieu: Toulouse
Date d'inscription: 9 Sep 2005
Messages: 1554

Re: Postgresql : extraction de valeur au sein d'un champ XML

Pour fabriquer le tableau des namespaces, vous pouvez parser le doc et splitter sur la chaine "xmlns:"
En virant le premier et dernier élement du tableau, vous pouvez construire le tableau de tableaux de namespaces attendu par xmlpath.

Par exemple (un peu crad...):

Code:

with tmp1 as (
    select id, regexp_split_to_array(unnest(r[2:array_length(r, 1) - 1]), '=') as xmlns, doc
    from xml_data,
         regexp_split_to_array(replace(doc::text, '"', ''), ' xmlns:') r
), tmp2 as (
    select id, array_agg(xmlns) as xmlns
    from tmp1
    group by id
) select x.id, t.xmlns
from tmp2 t join xml_data x using(id);

Et ensuite l'utiliser dans la fonction xpath:

(il faut virer les " pour créer des namespaces valides, raison de l'echec précédent, cf replace(doc::text, '"', '') dans la requête)

Code:

with tmp1 as (
    select id, regexp_split_to_array(
            unnest(r[2:array_length(r, 1) - 1]), '=') as xmlns, doc
    from xml_data,
         regexp_split_to_array(replace(doc::text, '"', ''), ' xmlns:') r
), tmp2 as (
    select id, array_agg(xmlns) as xmlns
    from tmp1
    group by id
)
select x.id,unnest(xpath(
                 '//cit:CI_Organisation/cit:name/gco:CharacterString/text()',
                 doc,
                 t.xmlns)) as orga_name
from tmp2 t join xml_data x using(id);

+--+----------------------------------+
|id|orga_name                         |
+--+----------------------------------+
|1 |Office français de la biodiversité|
|1 |Office français de la biodiversité|
|1 |Office français de la biodiversité|
|1 |Office français de la biodiversité|
+--+----------------------------------+

Nico

Dernière modification par Nicolas Ribot (Wed 13 November 2024 17:57)

Hors ligne

 

#13 Sun 17 November 2024 21:05

image95
Participant assidu
Date d'inscription: 6 Sep 2014
Messages: 259

Re: Postgresql : extraction de valeur au sein d'un champ XML

Bonjour,
Merci. Je viens de tester votre dernière proposition. Mais de mon coté j'ai un message d'erreur.

Code:

with tmp1 as (
    select id, regexp_split_to_array(unnest(r[2:array_length(r, 1) - 1]), '=') as xmlns, data
    from public.metadata,
         regexp_split_to_array(replace(data::text, '"', ''), ' xmlns:') r
), tmp2 as (
    select id, array_agg(xmlns) as xmlns
    from tmp1
    group by id
) select x.id, t.xmlns
from tmp2 t join public.metadata x using(id);

Voici ce que j'obtiens :

Code:

ERROR:  cannot accumulate arrays of different dimensionality 

SQL state: 2202E

Je me permets une petite question additionnelle. Dans notre précédent exemple, dans la fonction xpath, on définissait uniquement un tag :

Code:

//cit:CI_Organisation/cit:name/gco:CharacterString/text()'

. Est il possible de définir plusieurs tags ? Par exemple, d'ajouter ce deuxième tag :

Code:

//gmd:organisationName/gco:CharacterString/text()'

Enfin, je me demandais si il était possible d'utiliser un order by "colonne_data" ASC/DESC pour trier sur les noms des organismes. Et/ou d'utiliser un "select distinct ou un group by afin de récupérer les valeurs uniques ? Juste pour rappel, je colle une requete qui fonctionne de mon coté:

Code:

select id, unnest(xpath(
        '//cit:CI_Organisation/cit:name/gco:CharacterString/text()',
       CAST(data AS XML),
        ARRAY[
            ARRAY['cit', 'http://standards.iso.org/iso/19115/-3/cit/2.0'],
            ARRAY['gco', 'http://standards.iso.org/iso/19115/-3/gco/1.0']
        ])) as orga_name
from public.metadata

Par avance, un grans merci.

Dernière modification par image95 (Sun 17 November 2024 22:26)

Hors ligne

 

#14 Mon 18 November 2024 10:54

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 Jul 2010
Messages: 1166

Re: Postgresql : extraction de valeur au sein d'un champ XML

Merci. Je viens de tester votre dernière proposition. Mais de mon coté j'ai un message d'erreur.


Une requête peut être un peu plus safe (fiddle ici : https://dbfiddle.uk/1KPNV_YC):

Code:

select
  array_agg(
  ARRAY[ substring(b, 7, position('=' in b) - 7),
         replace(substring(b, position('"' in b)), '"', '')
        ]
  )
  from (
     select regexp_matches(xml,  'xmlns:[^\s]+="[^\s]+"'  , 'g' )
   ) foo(b)

edit : le même code qu'au dessus en fonction. Il y a peut être des cas tordus de déclaration de namespaces qui ne passeront pas.

Code:

CREATE FUNCTION extract_xml_namespaces(xml text) returns TEXT[][]  as $$
  select
  array_agg(
  ARRAY[ substring(b, 7, position('=' in b) - 7),
         replace(substring(b, position('"' in b)), '"', '')
        ]
  )
  from (select unnest(regexp_matches(xml,'xmlns:[^\s]+="[^\s]+"', 'g'))) as f(b)
  $$ language SQL immutable
;

. Est il possible de définir plusieurs tags ? Par exemple, d'ajouter ce deuxième tag :


Oui, avec l'opérateur ou  (symbole |) : 

Code:

//cit:CI_Organisation/cit:name/cit:CharacterString/text()|//cit:CI_Organisation/cit:name/gco:CharacterString/text()

Enfin, je me demandais si il était possible d'utiliser un order by "colonne_data" ASC/DESC pour trier sur les noms des organismes. Et/ou d'utiliser un "select distinct ou un group by afin de récupérer les valeurs uniques ? Juste pour rappel, je colle une requete qui fonctionne de mon coté:


Oui, le plus simple est d'essayer et de rajouter un group by ou order by organ_name à votr requête.

Dernière modification par tumasgiu (Tue 19 November 2024 09:33)

Hors ligne

 

#15 Mon 16 December 2024 16:52

image95
Participant assidu
Date d'inscription: 6 Sep 2014
Messages: 259

Re: Postgresql : extraction de valeur au sein d'un champ XML

Bonjour,
Je me permets une petite question additionnelle. Sauriez vous si il est possible de faire un update sur un champ XML  ? Dans notre exemple le fameux champ 'data'  ? Je ne sais pas quelles pistes permettraient de faire des update en XML avec postgresql ? Sinon, concernant le dernier message et un test de order by ou de group by, j'ai un message d'erreur :

Code:

select id, unnest(xpath(
        '//cit:CI_Organisation/cit:name/gco:CharacterString/text()',
       CAST(data AS XML),
        ARRAY[
            ARRAY['cit', 'http://standards.iso.org/iso/19115/-3/cit/2.0'],
            ARRAY['gco', 'http://standards.iso.org/iso/19115/-3/gco/1.0'],
            ARRAY['mdb','http://standards.iso.org/iso/19115/-3/mdb/2.0'],
            ARRAY['cat','http://standards.iso.org/iso/19115/-3/cat/1.0'],
            ARRAY['gfc','http://standards.iso.org/iso/19110/gfc/1.1'],
            ARRAY['cit','http://standards.iso.org/iso/19115/-3/cit/2.0'],
            ARRAY['gcx','http://standards.iso.org/iso/19115/-3/gcx/1.0'],
            ARRAY['gex','http://standards.iso.org/iso/19115/-3/gex/1.0'],
            ARRAY['lan','http://standards.iso.org/iso/19115/-3/lan/1.0'],
            ARRAY['srv','http://standards.iso.org/iso/19115/-3/srv/2.1'],
            ARRAY['mas','http://standards.iso.org/iso/19115/-3/mas/1.0'],
            ARRAY['mcc','http://standards.iso.org/iso/19115/-3/mcc/1.0'],
            ARRAY['mco','http://standards.iso.org/iso/19115/-3/mco/1.0'],
            ARRAY['mda','http://standards.iso.org/iso/19115/-3/mda/1.0'],
            ARRAY['mds','http://standards.iso.org/iso/19115/-3/mds/2.0'],
            ARRAY['mdt','http://standards.iso.org/iso/19115/-3/mdt/2.0'],
            ARRAY['mex','http://standards.iso.org/iso/19115/-3/mex/1.0'],
            ARRAY['mmi','http://standards.iso.org/iso/19115/-3/mmi/1.0'],
            ARRAY['mpc','http://standards.iso.org/iso/19115/-3/mpc/1.0'],
            ARRAY['mrc','http://standards.iso.org/iso/19115/-3/mrc/2.0'],
            ARRAY['mrd','http://standards.iso.org/iso/19115/-3/mrd/1.0'],
            ARRAY['mri','http://standards.iso.org/iso/19115/-3/mri/1.0'],
            ARRAY['mrl','http://standards.iso.org/iso/19115/-3/mrl/2.0'],
            ARRAY['mrs','http://standards.iso.org/iso/19115/-3/mrs/1.0'],
            ARRAY['msr','http://standards.iso.org/iso/19115/-3/msr/2.0'],
            ARRAY['mdq','http://standards.iso.org/iso/19157/-2/mdq/1.0'],
            ARRAY['mac','http://standards.iso.org/iso/19115/-3/mac/2.0'],
            ARRAY['gco','http://standards.iso.org/iso/19115/-3/gco/1.0'],
            ARRAY['gml','http://www.opengis.net/gml/3.2'],
            ARRAY['xlink','http://www.w3.org/1999/xlink'],
            ARRAY['xsi','http://www.w3.org/2001/XMLSchema-instance']
        ])) as orga_name, changedate, createdate, displayorder, doctype, extra, popularity, rating, root, schemaid, title, istemplate, isharvested, harvesturi,
       harvestuuid, groupowner, metadata.owner, metadata.source, uuid
from public.metadata
group by orga_name

J'obtiens

Code:

ERROR:  could not identify an equality operator for type xml
LINE 39: group by orga_name
                  ^ 

SQL state: 42883
Character: 2556

Hors ligne

 

#16 Mon 16 December 2024 17:03

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 Jul 2010
Messages: 1166

Re: Postgresql : extraction de valeur au sein d'un champ XML

Bonjour,
un truc de ce genre :

Code:

group by XMLSERIALIZE(CONTENT orga_name as text)

Voir : https://www.postgresql.fr/docs/current/ … L-CREATING

Hors ligne

 

#17 Mon 16 December 2024 17:18

image95
Participant assidu
Date d'inscription: 6 Sep 2014
Messages: 259

Re: Postgresql : extraction de valeur au sein d'un champ XML

Merci pour votre aide. Voici ce que j'obtiens :

Code:

select id, unnest(xpath(
        '//cit:CI_Organisation/cit:name/gco:CharacterString/text()|//cit:CI_Organisation/cit:name/gco:CharacterString/text()',
       CAST(data AS XML),
        ARRAY[
            ARRAY['cit', 'http://standards.iso.org/iso/19115/-3/cit/2.0'],
            ARRAY['gco', 'http://standards.iso.org/iso/19115/-3/gco/1.0'],
            ARRAY['mdb','http://standards.iso.org/iso/19115/-3/mdb/2.0'],
            ARRAY['cat','http://standards.iso.org/iso/19115/-3/cat/1.0'],
            ARRAY['gfc','http://standards.iso.org/iso/19110/gfc/1.1'],
            ARRAY['cit','http://standards.iso.org/iso/19115/-3/cit/2.0'],
            ARRAY['gcx','http://standards.iso.org/iso/19115/-3/gcx/1.0'],
            ARRAY['gex','http://standards.iso.org/iso/19115/-3/gex/1.0'],
            ARRAY['lan','http://standards.iso.org/iso/19115/-3/lan/1.0'],
            ARRAY['srv','http://standards.iso.org/iso/19115/-3/srv/2.1'],
            ARRAY['mas','http://standards.iso.org/iso/19115/-3/mas/1.0'],
            ARRAY['mcc','http://standards.iso.org/iso/19115/-3/mcc/1.0'],
            ARRAY['mco','http://standards.iso.org/iso/19115/-3/mco/1.0'],
            ARRAY['mda','http://standards.iso.org/iso/19115/-3/mda/1.0'],
            ARRAY['mds','http://standards.iso.org/iso/19115/-3/mds/2.0'],
            ARRAY['mdt','http://standards.iso.org/iso/19115/-3/mdt/2.0'],
            ARRAY['mex','http://standards.iso.org/iso/19115/-3/mex/1.0'],
            ARRAY['mmi','http://standards.iso.org/iso/19115/-3/mmi/1.0'],
            ARRAY['mpc','http://standards.iso.org/iso/19115/-3/mpc/1.0'],
            ARRAY['mrc','http://standards.iso.org/iso/19115/-3/mrc/2.0'],
            ARRAY['mrd','http://standards.iso.org/iso/19115/-3/mrd/1.0'],
            ARRAY['mri','http://standards.iso.org/iso/19115/-3/mri/1.0'],
            ARRAY['mrl','http://standards.iso.org/iso/19115/-3/mrl/2.0'],
            ARRAY['mrs','http://standards.iso.org/iso/19115/-3/mrs/1.0'],
            ARRAY['msr','http://standards.iso.org/iso/19115/-3/msr/2.0'],
            ARRAY['mdq','http://standards.iso.org/iso/19157/-2/mdq/1.0'],
            ARRAY['mac','http://standards.iso.org/iso/19115/-3/mac/2.0'],
            ARRAY['gco','http://standards.iso.org/iso/19115/-3/gco/1.0'],
            ARRAY['gml','http://www.opengis.net/gml/3.2'],
            ARRAY['xlink','http://www.w3.org/1999/xlink'],
            ARRAY['xsi','http://www.w3.org/2001/XMLSchema-instance']
        ])) as orga_name, changedate, createdate, displayorder, doctype, extra, popularity, rating, root, schemaid, title, istemplate, isharvested, harvesturi,
       harvestuuid, groupowner, metadata.owner, metadata.source, uuid
from public.metadata
group by XMLSERIALIZE(CONTENT 'orga_name' as text)

J'obtiens :

Code:

ERROR:  column "metadata.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select id, unnest(xpath(
               ^ 

SQL state: 42803
Character: 8

Hors ligne

 

#18 Tue 17 December 2024 10:05

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 Jul 2010
Messages: 1166

Re: Postgresql : extraction de valeur au sein d'un champ XML

C'est une erreur sql classique : vous essayez de grouper sur le resultat du xpath, mais vous incluez id dans votre clause select. PostgreSQL vous dit que vous ne pouvez utiliser id que dans une fonction d'aggrégat (sum, count, array_agg, etc...) à moins que vous vous ne l'ajoutiez dans votre clause group by (ce qui ne servirait à rien dans votre requête : cela serait équivalent à ne pas mettre de clause group by).

Je n'avais pas vu votre première question : cela m'a l'air un peu difficile en sql pur, il faudrait sans doute passer par un script externe type python (ou pl/python peut-être) où vous avez plus de fonctionnalités sur le xml.

Hors ligne

 

 

Pages: 1

Pied de page des forums

Powered by FluxBB