Error extracting XML values in PostgreSQL using XPath: SQL Error [42601]: ERROR: syntax error at or near "["

Question

I'm trying to extract values from XML stored in the PostgreSQL DB using XPath. I've got an error:

SQL Error [42601]: ERROR: syntax error at or near "["

My SQL is:

-- Find "e" nodes which have "p1" child nodes and does not have "p3" child nodes
WITH tbl(p_xml) AS (
   SELECT '<root>
    <e id="1">
        <p1>P1</p1>
        <p2>P2</p2>
    </e>
    <e id="2">
        <p1>P1</p1>
        <p3>P2</p3>
    </e>
    <e id="3">
        <p2>P1</p2>
        <p3>P3</p3>
    </e>
</root>'::xml
)
select * 
FROM   tbl
where
    (xpath('count(/root/e/p1)', p_xml)[1]::text)::int > 0 and 
    (xpath('count(/root/e/p3)', p_xml)[1]::text)::int = 0

I've seen plenty of examples on StackOverflow with using squares to get data (as xpath function returns array), but all of them fails with the same error on my PostgreSQL. I tried this on PostgreSQL DB versions 9.6 and 10.1 with no luck.

har07 har07 · Accepted Answer · 2017-11-23T11:30:39

That's because your query lacks a brackets. Anyways, if you want to return just e elements that fulfill that condition you'll need to shred the XML, for example :

WITH tbl(p_xml) AS (
   SELECT '<root>
    <e id="1">
        <p1>P1</p1>
        <p2>P2</p2>
    </e>
    <e id="2">
        <p1>P1</p1>
        <p3>P2</p3>
    </e>
    <e id="3">
        <p2>P1</p2>
        <p3>P3</p3>
    </e>
</root>'::xml
)
SELECT e
FROM tbl
CROSS JOIN LATERAL UNNEST(xpath('//e[p1 and not(p3)]', p_xml)) e

rextester demo: http://rextester.com/FZGP41665

Error extracting XML values in PostgreSQL using XPath: SQL Error [42601]: ERROR: syntax error at or near "["

2 Answers