Yanxu Yanxu - 6 months ago 28
SQL Question

PostgresSQL xpath match with double quotes

I'm using xpath in PostgresSQL 9.3 to search nodes with content containing double quotes.
I have tried to "\"" and "&quo t;" to replace the """, but none of them were working. "concat" seems not working either. I could not find a working method on the internet. Can anyone tell me how to escape the double quotes.

Query:

select
xpath(E'//book[title = "["a book"]"]/price/text()', xmlparse(document b.content))::text[]
from
booksite b


XML content like:

<book>
<title>["a book"]</title>
<price>29.99</price>
</book>

Answer

XPath 1.0 provides no way of escaping quotes. Use single-quotes as your XPath string delimiter instead, since you can escape single quote in SQL by doubling them :

select
    xpath(E'//book[title = ''["a book"]'']/price/text()', xmlparse(document b.content))::text[]
from
    booksite b