PostgreSQL: xpath function containing a text() expression-CodePudding

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.

CodePudding user response：

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)

CodePudding user response：

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)