I Have xml :
<?xml version="1.0" encoding="UTF-8"?>
<attachments>
<entry file="cewe_gw.jpg" name="cewe_gw.jpg"/>
<entry file="wp1827515.png" name="wp1827515.png"/>
</attachments>
I want to get list file? ex:
cewe_gw.jpg
wp1827515.png
If you are looking for a Postgres solution, then you can use xmltable for this:
select x.*
from the_table t
cross join xmltable('/attachments/entry'
passing t.the_xml_column
columns file text path '@name') as x
This returns the value of the attribute name, if you want the file attribute, you need to change @name to @file
To select any element or attribute, you can use XPath:
SELECT
xpath('//entry/@file',xml)
FROM mydata
This returns an array of file attributes. You can extract them using unnest()
SELECT
unnest(xpath('//entry/@file',xml))
FROM mydata