Postgres Pro
Downloads
Home   >   mailing lists

Extract values from XML content - Mailing list pgsql-general

From celati Laurent
Subject Extract values from XML content
Date
Msg-id CAHByMH01=A2dH5ASd6tyZeRZgPnBf1CYZOC==r9=U=thYbYpKw@mail.gmail.com
Whole thread Raw
Responses Re: Extract values from XML content
Re: Extract values from XML content
List pgsql-general
Tree view
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.


pgsql-general by date:

Previous
From: Alvaro Herrera
Date:
Subject: Re: Fwd: A million users
Next
From: jayakumar s
Date:
Subject: DB wal file disabled --_Query