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 · Accepted Answer · 2017-11-23 11:30:39Z

3

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

answered Nov 23, 2017 at 11:30

har07

89.5k12 gold badges87 silver badges143 bronze badges

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

1 Comment

Gottfried Lesigang Over a year ago

I knew, there must be a better approach :-)

Gottfried Lesigang · Accepted Answer · 2017-11-23 11:24:14Z

1

I'm not experienced with postgresql, but this seems to work:

where
    (xpath('count(/root/e/p1)', p_xml)::text[])[1]::int > 0 and 
    (xpath('count(/root/e/p3)', p_xml)::text[])[1]::int = 0

It looks, as if xpath() must be casted to an array of string, where you pick the first element to cast it to int. Not very intuitive.

There might be a better approach...

answered Nov 23, 2017 at 11:24

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related