Yanxu Yanxu - 1 year ago 70
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.


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

XML content like:

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

Answer Source

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 :

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