1

When I execute below query in Postgres 10.12, it works properly.

SELECT (xpath('./sid/text()', c.node))[1]::text::bigint AS STUDENT_ID,
       (xpath('./name/text()', c.node))[1]::text AS STUDENT_NAME
from (    
  select unnest(xpath('/data', '<data><sid>112233</sid><name>John</name></data>'::xml)) AS node    
) c;

Output:

enter image description here

But When I execute same query in Postgres 11.7, it is not working.

enter image description here

What is the solution to fix this issue?

Nirav Patel
  • 1,304
  • 2
  • 13
  • 30

1 Answers1

3

This is caused by this change:

Correctly handle relative path expressions in xmltable(), xpath(), and other XML-handling functions (Markus Winand)

Per the SQL standard, relative paths start from the document node of the XML input document, not the root node as these functions previously did.

so you need to change it to: 

SELECT (xpath('/data/sid/text()', c.node))[1]::text::bigint AS STUDENT_ID,
       (xpath('/data/name/text()', c.node))[1]::text AS STUDENT_NAME
from (    
  select unnest(xpath('/data', '<data><sid>112233</sid><name>John</name></data>'::xml)) AS node    
) c;

because the inner xpath will return the <data> tag as well: 

select unnest(xpath('/data', '<data><sid>112233</sid><name>John</name></data>'::xml)) AS node

results in: 

<data>
  <sid>112233</sid>
  <name>John</name>
</data>

However, I would use xmltable for this:

select *
from xmltable('/data'
               passing xml('<data><sid>112233</sid><name>John</name></data>')
               columns 
                  student_id bigint path 'sid', 
                  student_name text path 'name')
  • Fiddles demonstrating both behaviours for [pg10](https://dbfiddle.uk/?rdbms=postgres_10&fiddle=f48defc41132e80b3a36d90f73cf2d4b) & [pg11+](https://dbfiddle.uk/?rdbms=postgres_11&fiddle=f48defc41132e80b3a36d90f73cf2d4b) – Peter Vandivier Apr 17 '20 at 07:30
  • Thanks @a_horse_with_no_name, this solution worked for me. Appreciate your recommendation to use correct query using xmltable. – Nirav Patel Apr 17 '20 at 14:01