Search Postgresql Archives

Extract values from XML content

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux