Postgres XML XPath returns Invalid XPath expression

Question

Good evening,

I've been trying to query a table with xml data. The table has a report_id (int) and xml_document (xml) as its fields.

The xml document is built like this:

<document>
<type>INVESTMENT_REQUEST</type>
<cro>
    <userId>1</userId>
    <surname>SUR1</surname>
    <name>NAM1</name>
    <phone>8563</phone>
</cro>
<ma>
    <userId>10</userId>
    <surname>SUR10</surname>
    <name>NAM10</name>
    <phone>6763</phone>
</ma>
<customer>
    <customer_ssn>14</customer_ssn>
    <surname>CSUR2</surname>
    <name>CNAME2</name>
    <birthdate>1985-10-10</birthdate>
    <account_date_opened>2016-05-09</account_date_opened>
    <investment_profiles>
        <investment_profile>
            <profile>4</profile>
            <date_profiled>2016-05-09</date_profiled>
        </investment_profile>
            <investment_profile>
            <profile>3</profile><date_profiled>2017-05-09</date_profiled>
        </investment_profile>
            <investment_profile>
            <profile>3</profile><date_profiled>2017-01-09</date_profiled>
        </investment_profile>
    </investment_profiles>
</customer>
<text>Customer is not averse to risks, and has a huge portfolio of investments.Please advise</text>
</document>

I've been trying to use XPapth to get various values from fields, and the tutorial I've been reading lead to the following command, which should bring me all the customer ssn:

SELECT UNNEST(xpath('//customer/customer_ssn()',xml_document))::text AS XMLDATA FROM consultation_report;

This does not work however, and it returns an Invalid XPATH expression. There is something I do not understand, and I cannot understand what it is. I've followed the example letter by letter.

If I run the command without the (),

SELECT UNNEST(xpath('//customer/customer_ssn',xml_document))::text AS XMLDATA FROM consultation_report;

It returns:

<customer_ssn>12</customer_ssn>
<customer_ssn>13</customer_ssn>
<customer_ssn>14</customer_ssn>

which are unusable as values.

What have I misunderstood? Also, where do I start if I want to filter the results, p.ex bring only the xml for customer_ssn 14?

(Edit) I've managed to make a succesful query:

SELECT * FROM consultation_report WHERE (xpath('//document/customer/customer_ssn',xml_document))[1]::text = '<customer_ssn>14</customer_ssn>'

but I still get an error with:

    SELECT * FROM consultation_report WHERE (xpath('//document/customer/customer_ssn()',xml_document))[1]::text = '14'

har07 · Accepted Answer · 2018-02-07 02:47:10Z

1

There is no function named customer_ssn, and only node test functions are callable in path step in XPath 1.0 anyway, so customer_ssn() will not be accepted. That said, to return text nodes inside customer_ssn you should add a node test text() instead :

//customer/customer_ssn/text()

answered Feb 7, 2018 at 2:47

har07

89.5k12 gold badges87 silver badges143 bronze badges

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

1 Comment

Spyros Over a year ago

The examples I had seen called the actual attribute text (<foo><bar><text></text></bar></foo>, so I got the completely wrong idea! Thank you so much, works like a charm!

Jim Jones · Accepted Answer · 2018-02-07 09:39:12Z

1

but I still get an error with:

SELECT * FROM consultation_report WHERE (xpath('//document/customer/customer_ssn()',xml_document))[1]::text = '14'

You're missing the text() function in your xpath expression:

SELECT * 
FROM consultation_report 
WHERE (xpath('//document/customer/customer_ssn/text()',xml_document))[1]::text = '14'

answered Feb 7, 2018 at 9:39

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

1 Comment

Spyros Over a year ago

Thank you for the answer, you are indeed correct. However I can only accept 1 answer, and I'll go with @har07 since he was earlier and explained what the issue was.

Collectives™ on Stack Overflow

Postgres XML XPath returns Invalid XPath expression

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related