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 :
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;
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.