CODEWITHSUNDEEP
sqlxmlpostgresqlselectextract
user1812750
user1812750

Reputation: 27

How to extract xml content in PostgreSQL database raws?

I have database table, with one column containing xml codes. Can I extract information from xml elements? Let's say I have something like this in all my rows (in 1 column).

<?xml version="1.0" encoding="UTF-8"?>
<note>
  <to>Tove</to>
  <from>Jani</from>
  <heading>Reminder</heading>
  <body>Don't forget me this weekend!</body>
</note>

Can I extract 'from' element from all rows?

Upvotes: 1

Views: 1417

Answers (1)

Mureinik
Mureinik

Reputation: 311188

You could cast the column to XML type (if it isn't stored like that already) and then use xpath to extract the value:

SELECT (XPATH('/note/from/text()', my_column::xml))[1]
FROM   my_table

Upvotes: 2

Related Questions