PostgreSQL: xpath function containing a text() expression

Question

In postgresql 9.6 the following works:

SELECT xpath(
  'text()', 
  '<hl7_message_host _valuelabel="HL7 host">10.170.117.86</hl7_message_host>');

      xpath     
-----------------
 {10.170.117.86}
(1 row)

But in postgresql 12 it doesn't:

SELECT xpath(
  'text()', 
  '<hl7_message_host _valuelabel="HL7 host">10.170.117.86</hl7_message_host>');

 xpath
-------
 {}
(1 row)

What am I missing or doing wrong?

The example in this post is much simpler than the real queries in the code: in general the xpath() expression is used to extract/convert (unnest() is often involved) name()+text() or name()+some attributes from the second parameter of xpath, and usually the tagname of the second parameter is not known beforehand, so it can't appear in the first parameter. Also, the second parameter can be many levels nested, but only the 1st one is useful.

Laurenz Albe · Accepted Answer · 2021-10-04 20:18:09Z

1

That is due to a compatibility-breaking fix in v11 (commit e474c2b7e):

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.

From v11 on, you have to do it like this:

SELECT xpath(
  'hl7_message_host/text()', 
  '<hl7_message_host _valuelabel="HL7 host">10.170.117.86</hl7_message_host>');

      xpath      
═════════════════
 {10.170.117.86}
(1 row)

answered Oct 4, 2021 at 20:18

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

2 Comments

MadeR Over a year ago

Suppose I do not know the tag of the next level of the XML node and the node is nested several levels (the real case I have): how can i extract the text() of the top node value r from the example below? something like that: SELECT xpath('*/text()', '<root>r<sub1>s1</sub1><sub2>s2</sub2></root>');

Laurenz Albe Over a year ago

Yes, the asterisk is a wildcard.

Forensic_07 · Accepted Answer · 2021-10-04 20:14:02Z

0

You'll need a // at the beginning to search anywhere in the tree for text nodes, rather than just at the root.

db=# SELECT xpath(
db(#    '//text()',
db(#    '<root>Hello World</root>');
      xpath      
-----------------
 {"Hello World"}
(1 row)

db=# SELECT xpath(
   'text()',
   '<root>Hello World</root>');
 xpath 
-------
 {}
(1 row)

answered Oct 4, 2021 at 20:14

Forensic_07

1,1351 gold badge6 silver badges10 bronze badges

1 Comment

MadeR Over a year ago

again: I do not want to search everywhere, just in the top level of the xml. the real case in my code is not knowing who the xml is structured, I just need to get the text() of the topmost node: '<root>r<sub1>s1</sub1><sub2>s2</sub2></root>' In the example above I want only r, not {r, s1, s2} and I do not know whether the node is root or anything else. so probably I should change the query to: SELECT xpath('*/text()', '<root>r<sub1>s1</sub1><sub2>s2</sub2></root>'); but it's in lots of places in the code :( The could have added another xpath variant for the new syntax

Collectives™ on Stack Overflow

PostgreSQL: xpath function containing a text() expression

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related