How to retrieve attributes only using xpath from postgres sql

Question

I have a tables countries which is containing the required xml. Assume this table is in the postgres db. below is the xml from the table countries

<config>
   <classification Master="Y" Database="Y">
      <lookup >
           <countries>
               <cities>
                   <city name="Bangalore">Bangalore</city>
                   <city name="Delhi">Delhi</city>
                   <city name="Mumbai">Mumbai</city>
               </cities>
           </countries>
      </lookup>
  </classification>
</config>

I am writing the query to get the result from the table:

select unnest(xpath('/config/classification/lookup', data)),    xpath('/config/classification/attribute::*', data) from  Countries;

| <lookup  >                                                |
|     <countries>                                           |
|          <cities>                                         |
|               <city name="bangalore">Bangalore</city>   |
|               <city name="delhi">Delhi</city>           |
|               <city name="mumbai">Mumbai</city>           |
|          </cities>                                        |
|      </countries>                                         |
|</lookup>                                                  | Y, Y

but i am expecting something to achieve the below result

|<lookup >                                                  |
|      <countries>                                          |
|           <cities>                                        |
|                <city name="bangalore">Bangalore</city>  |
|                <city name="delhi">Delhi</city>          |
|                <city name="mumbai">Mumbai</city>          |
|           </cities>                                       |
|       </countries>                                        |
| </lookup>                                                 |   Master="Y" Database="Y"

I am not sure if it can be achieved, if yes please expecting the query. One condition it can be any number of attributes and i don not want to hard code any attribute name. I want all the attributes name and also the value.

You can select an attribute name with the name() (which would include namespace-prefix) or local-name() functions. — Mads Hansen
– Mads Hansen, Commented Jun 5 at 14:12

Guillaume Outters · Accepted Answer · 2025-06-05 14:17:14Z

2

Contrary to what I thought in my first answer, XPath's @* still holds all information that can be extracted with name().

However name() is scalar, so you have to iterate to extract each attribute's name and value, then combine them back into your desired result (here with PostgreSQL 16's json_object_agg):

select
  unnest(xpath('/config/classification/lookup', data)),
  (
    select json_objectagg((xpath('name(/config/classification/@*['||pos||'])', data))[1]: val)
    from unnest(xpath('/config/classification/@*', data)) with ordinality attr(val, pos)
  )
from  Countries;

unnest                                              | json_objectagg
----------------------------------------------------|---------------------------------------
<lookup>                                            |
    <countries>                                     |
        <cities>                                    |
            <city name="Bangalore">Bangalore</city> |
            <city name="Delhi">Delhi</city>         | { "Master" : "Y", "Database" : "Y" }
            <city name="Mumbai">Mumbai</city>       |
        </cities>                                   |
    </countries>                                    |
</lookup>                                           |

See it working beautifully in this fiddle.

(this second answer comes after I first failed making name() work on attributes, probably due a typo, because when thinking of JSON as an intermediate translation I found this answer that proved I had missed something about name())

answered Jun 5 at 14:17

Guillaume Outters

7,5041 gold badge22 silver badges28 bronze badges

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

2 Comments

Yitzhak Khabinsky Jun 5 at 14:23

Good answer. +1 from my side. It seems that PostgreSQL is badly missing native XQuery support.

jai Jun 6 at 6:18

I would try this option also

Guillaume Outters · Accepted Answer · 2025-06-05 14:20:07Z

As PostgreSQL's XPath doesn't seem to keep track of the full attribute (with its name and namespace) once fetched with attribute::* / @* [EDIT: as shown in my new answer, PostgreSQL can; but this answer still is interesting, with its creative use of regexp to play with XML],
I would unconventionally rely on an awful regexp parsing:

select
  unnest(xpath('/config/classification/lookup', data)),
  (regexp_match(xpath('/config/classification', data)::text, '<[^ >]*(?: ([^>]*))?>'))[1]
from  Countries;

unnest                                                  | regexp_match
--------------------------------------------------------|------------------------
<lookup>                                                |
    <countries>                                         |
        <cities>                                        |
            <city name="Bangalore">Bangalore</city>     |
            <city name="Delhi">Delhi</city>             | Master="Y" Database="Y"
            <city name="Mumbai">Mumbai</city>           |
        </cities>                                       |
    </countries>                                        |
</lookup>                                               |

(which just works)

N.B.: if your attributes may include >s, the regex will have to become a bit more complex, with start and stop "s detection.

Collectives™ on Stack Overflow

How to retrieve attributes only using xpath from postgres sql

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related