CODEWITHSUNDEEP
xmlpostgresqlxpath
user2383959
user2383959

Reputation: 81

How to escape ">" character in postgres version 9.2

How do i make the > sign display as is in PG version 9.2 ? I do not want it to show as >.

Query in postgreSQL

SELECT 
XPATH ('/BehaviorReportingRanges/BehaviorReportingRange/Range/text()', 
xmlparse(content 
'<BehaviorReportingRanges> 
  <BehaviorReportingRange> 
   <Range>> 3</Range> 
  </BehaviorReportingRange> 
</BehaviorReportingRanges>'))

Result in version 9.1:

{"> 3"} -- desired result

result in version 9.2.3:

"{"&gt; 3"}" -- not desired result

any pointers please ?

Thanks a lot !

Upvotes: 7

Views: 1465

Answers (1)

Robert M. Lefkowitz
Robert M. Lefkowitz

Reputation: 1505

Well, if you CREATE LANGUAGE plpythonu; , you can then do the following:

create or replace function unescape(x varchar)
    returns varchar language plpythonu as $$
from xml.sax.saxutils import unescape
return unescape(x)
$$;

Then, your query can become:

SELECT unescape( concat( (
XPATH ('/BehaviorReportingRanges/BehaviorReportingRange/Range/text()', 
xmlparse(content 
'<BehaviorReportingRanges> 
  <BehaviorReportingRange> 
   <Range>> 3</Range> 
  </BehaviorReportingRange> 
</BehaviorReportingRanges>')) )[1]
 ) );

You need to add the indexing to select the element from the array result of xpath, and the concat is to convert from xml to string datatype.

You can also apply the unescape function directly -- but the resulting string is the formatted xml array.

Upvotes: 3

Related Questions