Search in xml using xpath in postgresql db

Question

I have a database in Postgresql where I put some documents written in xml. I want to search twrough them using XPath, but my code is not working. The document are like this:

<?xml version="1.0" standalone="yes"?>
<TEI xmlns="http://www.tei-c.org/ns/1.0">
    <teiHeader>
        ....
        <revisionDesc>
            <listChange>
                <change when="2017-01-11+01:00" who="person"/>
            </listChange>
        </revisionDesc>
    </teiHeader>
    ...
</TEI>

I'm trying to get the when attribute of the change element. My code so far:

SELECT 

xpath('TEI/n:teiHeader/n:revisionDesc/n:listChange/n:change[@when]/text()',

xml_document , '{{n,http://www.tei-c.org/ns/1.0}}') 

FROM xml_table where xml_id = 5;

It gives me empty result like this: {} and I don't understand why

1. TEI element is also in default namespace. Use prefix for it as well : n:TEI/...... 2. change element doesn't have inner text so /text() won't find anything, and I don't know what you actually want to get from change element — har07
– har07, Commented Jan 13, 2017 at 12:16
The value of the when attribute, in the example 2017-01-11+01:00 — tina
– tina, Commented Jan 13, 2017 at 12:52
'n:TEI/n:teiHeader/n:revisionDesc/n:listChange/n:change/@when' and 'n:TEI/n:teiHeader/n:revisionDesc/n:listChange/n:change[@when]/@when' give the same result {}, did I miss something? — tina
– tina, Commented Jan 13, 2017 at 13:17
@tina prepend them with / to denote that n:TEI is you root element -- or ommit n:TEI completely, because xpath() will search for results relative to the root element (in xpath terms, that is the current node). — pozs
– pozs, Commented Jan 13, 2017 at 14:13

pozs · Accepted Answer · 2017-01-13 15:11:45Z

1

You need to prepend your xpath query with / if you want to reference the root element. Or, you could ommit n:TEI, because the xpath() function will search for results relative to the root element (in xpath terms, that is the current node).

I.e. these xpath queries will find the when attributes' values:

/n:TEI/n:teiHeader/n:revisionDesc/n:listChange/n:change/@when
n:teiHeader/n:revisionDesc/n:listChange/n:change/@when

answered Jan 13, 2017 at 15:11

pozs

36.6k5 gold badges61 silver badges66 bronze badges

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

Collectives™ on Stack Overflow

Search in xml using xpath in postgresql db

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related