Extract values from XML content
| От | celati Laurent |
|---|---|
| Тема | Extract values from XML content |
| Дата | |
| Msg-id | CAHByMH01=A2dH5ASd6tyZeRZgPnBf1CYZOC==r9=U=thYbYpKw@mail.gmail.com обсуждение исходный текст |
| Ответы |
Re: Extract values from XML content
Re: Extract values from XML content |
| Список | pgsql-general |
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.
В списке pgsql-general по дате отправления: