CODEWITHSUNDEEP
xmlpostgresqlxpath
Spyros
Spyros

Reputation: 197

Postgres XML XPath returns Invalid XPath expression

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'

Upvotes: 0

Views: 2092

Answers (2)

Jim Jones
Jim Jones

Reputation: 19613

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'

Upvotes: 1

har07
har07

Reputation: 89285

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()

Upvotes: 1

Related Questions