Search Postgresql Archives

Re: Extract values from XML content

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

 



Am 13.11.24 um 15:58 schrieb celati Laurent:
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;


I typically find xmltable() a lot easier to work with, especially if the XML contains namespaces.

I guess the namespaces are the problem in your case, you will have to pass them to the xpath()
function and reference them in the path expression as well

So you will need something like:

xpath('//cit:CI_Responsibility/cit:party/cit:CI_Organisation/cit:name/gco:CharacterString/text()',
       cast(data as xml),
       ARRAY[ARRAY['cit', 'http://...'], array['gco', 'http://...']])

The actual value for the namespace URIs depends on the definition in your XML

Note that xpath() returns an array, so you probably want (xpath(....))[1]









[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