Extracting data from nested XML PostgreSQL

Question

I have an XML that needs to be inserted into the table. The XML looks something like

<?xml version="1.0" encoding="UTF-8"?>
<root>
   <pats>
      <pat>
         <id>1</id>
         <pat_maps>
            <pat_map>
               <pgid>100</pgid>
               <pgname>test</pgname>
            </pat_map>
            <pat_map>
               <pgid>101</pgid>
               <pgname>test1</pgname>
            </pat_map>
         </pat_maps>
      </pat>
      <pat>
         <id>2</id>
         <pat_maps>
            <pat_map>
               <pgid>102</pgid>
               <pgname>test2</pgname>
            </pat_map>
         </pat_maps>
      </pat>
      <pat>
         <id>3</id>
         <pat_maps>
            <pat_map>
               <pgid>104</pgid>
               <pgname>test6</pgname>
            </pat_map>
            <pat_map>
               <pgid>105</pgid>
               <pgname>test7</pgname>
            </pat_map>
         </pat_maps>
      </pat>
   </pats>
</root>

I want to insert data in the following way

ID  pgid    pgname
1   100     test
1   101     test1
2   102     test2
3   104     test6
3   105     test7

Tried below but it is applying cross join

with x(t) as (select '<?xml version="1.0" encoding="UTF-8"?>
<root>
   <pats>
..............
..........
      </pat>
   </pats>
</root>'::xml AS t
)
, base_id as (SELECT 
    unnest(xpath('/root/pats/pat/id/text()', t)) AS id
from x
), nested_rec as ( select 
    unnest(xpath('pgid/text()', cat_assn_list)) AS pgid,
    unnest(xpath('pgname/text()', cat_assn_list)) AS pgname
from (select unnest(xpath('/root/pats/pat/pat_maps/pat_map', t)) cat_assn_list from x) q
) 
select base_id.*,nested_rec.* from base_id,nested_rec;

******* output *********
ID  PGID    PGNAME  
"1" "100"   "test"
"1" "101"   "test1"
"1" "102"   "test2"
"1" "104"   "test6"
"1" "105"   "test7"
"2" "100"   "test"
"2" "101"   "test1"
"2" "102"   "test2"
"2" "104"   "test6"
"2" "105"   "test7"
"3" "100"   "test"
"3" "101"   "test1"
"3" "102"   "test2"
"3" "104"   "test6"
"3" "105"   "test7"

I haven't found a way, how to nest the XML ID wise and prepare the resultset?. Is there any other way to convert xml data to table without using XPath function in PostgreSQL? Thanks in advance.

XPATH is the way to go, but I don't understand your problem. What do you mean by "nest the XML" or "prepare the resultset"? Why don't you simply insert your result? — Laurenz Albe
– Laurenz Albe, Commented Sep 29, 2020 at 11:04
@LaurenzAlbe " nest the XML ID wise and prepare the resultset" mean iterate the ID element in xml and prepare the PGID and PGNAME sub-elements data. Can you check this section "I want to insert data in the following way" in the description, which is how I need the end-result — Mahesh
– Mahesh, Commented Sep 29, 2020 at 11:25

score 4 · Accepted Answer · 2020-09-29 14:21:37Z

4

The following does what you want and is a bit shorter:

select (xpath('/pat/id/text()', d.pat))[1]::text::int as id,
       (xpath('/pat_map/pgid/text()', m.map))[1]::text::int as pgid,
       (xpath('/pat_map/pgname/text()', m.map))[1]::text as pgname
from x 
  cross join unnest(xpath('/root/pats/pat', x.t)) as d(pat)
  cross join unnest(xpath('/pat/pat_maps/pat_map', d.pat)) as m(map)
;

Online example

With a more modern Postgres version, you could use xmltable():

select d.*
from x
  cross join xmltable ('/root/pats/pat/pat_maps/pat_map' 
                       passing t 
                       columns 
                        id integer path '../../id',
                        pgid integer path 'pgid', 
                        pgname text path 'pgname') as d

edited Sep 29, 2020 at 14:21

answered Sep 29, 2020 at 11:32

user330315

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

2 Comments

Mahesh Over a year ago

Thanks, but it is not working in Postgres 9.5 version. dbfiddle.uk/…

user330315 Over a year ago

@Mahesh: ah, 9.5 requires an absolute path. See my edit

Collectives™ on Stack Overflow

Extracting data from nested XML PostgreSQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related