get xml or substring value from postgresql table

Question

I have the folowing as a text in the column my_data with a table my_table (id, my_data)..

<gmd:MD_Metadata xmlns:gmd="http://www.isotc211.org/2005/gmd" xmlns:gco="http://www.isotc211.org/2005/gco" xmlns:geonet="http://www.fao.org/geonetwork">
    <gmd:fileIdentifier>
        <gco:CharacterString>abc1234</gco:CharacterString>
    </gmd:fileIdentifier>
    <gmd:title>
        <gco:CharacterString>Plan_num1</gco:CharacterString>
    </gmd:title>
</gmd:MD_Metadata>

I convert the column my_data to xml to try to get the value abc1234 and the value Plan_num1 .. Trying solution found at this forum I was not able to get them (I have 2 name space and I get nothing).. Is-there any solution by using xpath or by using extract string? Thanks

I paste the code but i do not see it in my question (sorry)<gmd:MD_Metadata xmlns:gmd="isotc211.org/2005/gmd" xmlns:gco="isotc211.org/2005/gco" xmlns:geonet="fao.org/geonetwork"> <gmd:fileIdentifier> <gco:CharacterString>abc2301gfh548fh54fh54</gco:CharacterString> </gmd:fileIdentifier> <gmd:title> <gco:CharacterString>Plan_num1</gco:CharacterString> </gmd:title> </gmd:MD_Metadata> — sam
– sam, Commented Feb 16, 2015 at 18:10
What solution did you try? What was the result - any error messages? null return values? — LarsH
– LarsH, Commented Feb 16, 2015 at 19:29
I convert the my_data to xml and tried for exemple SELECT (xpath('/MD_Metadata/fileIdentifier/CharacterString/text()', my_data))[1]::text AS name from my_table; to get the first value abc1234.. but I got no erreur and no data.. — sam
– sam, Commented Feb 16, 2015 at 19:43

LarsH · Accepted Answer · 2015-02-16 20:54:26Z

1

The elements you're trying to select in your XPath expression, such as MD_Metadata and fileIdentifier, are each in a namespace. But your XPath expression asks for /MD_Metadata, which means 'an element named MD_Metadata that is in no namespace'. So your XPath expression doesn't select the elements you're looking for.

In order to select MD_Metadata in the namespace whose URI is "http://www.isotc211.org/2005/gmd", you need to map a prefix to that namespace, use the prefix in your XPath expression, and pass the mapping to Postgres via a third argument to your xpath() function.

E.g.

SELECT (xpath('/gmd:MD_Metadata/fmd:fileIdentifier/gco:CharacterString/text()',
  my_data, ARRAY[ARRAY['gmo', 'http://www.isotc211.org/2005/gmd'],
                ARRAY['gco', 'http://www.isotc211.org/2005/gco']]))[1]::text
    AS name from my_table;

I'm not sure what you intend with the [1], whether you mean it to be part of the XPath expression or not.

See http://www.edankert.com/defaultnamespaces.html (the first two sections) to better understand the nature of the problem in XML/namespaces/XPath.

See section 9.14.3, Processing XML for documentation on doing this with xpath() in PostgreSQL.

answered Feb 16, 2015 at 20:54

LarsH

28.1k9 gold badges99 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sam Over a year ago

thanks alot.. it's works... I got {abc1234}.. How can I get the same value ..without {}.. thanks again

sam Over a year ago

it's me again ... it is ok.. with [1]::text I got the information without the {}..

Collectives™ on Stack Overflow

get xml or substring value from postgresql table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related