Postgresql - How to read xmlattributes on xml?

Question

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

Answer 1

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

Online example

Answer 2

demo:db<>fiddle

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