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

Why the Java tag? Are you looking for a SQL solution or Java solution? — user330315
– user330315, Commented Dec 1, 2020 at 7:01

score 1 · Accepted Answer · 2020-12-01 07:30:10Z

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

edited Dec 1, 2020 at 7:30

answered Dec 1, 2020 at 7:05

user330315

Sign up to request clarification or add additional context in comments.

3 Comments

sahroni Over a year ago

an ERROR: syntax error at or near "passing"

S-Man Over a year ago

Wow, never knew about xmltable. Thanks for showing.

user330315 Over a year ago

@sahroni then you are using an outdated Postgres version

S-Man · Accepted Answer · 2020-12-01 07:11:09Z

0

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

answered Dec 1, 2020 at 7:11

S-Man

24k9 gold badges51 silver badges78 bronze badges

Collectives™ on Stack Overflow

Postgresql - How to read xmlattributes on xml?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related