Извлечение данных из вложенного XML PostgreSQL

#sql #xml #postgresql #postgresql-9.5

Вопрос:

У меня есть XML, который необходимо вставить в таблицу. XML выглядит примерно так

 <?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>

Я хочу вставить данные следующим образом

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

Попробовал ниже, но он применяет перекрестное соединение

 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"

Я не нашел способа, как вложить идентификатор XML и подготовить результирующий набор?.
Есть ли какой-либо другой способ преобразовать данные xml в таблицу без использования функции XPath в PostgreSQL?
Заранее спасибо.

1. XPATH — это правильный путь, но я не понимаю вашей проблемы. Что вы подразумеваете под «вложением XML» или «подготовкой результирующего набора»? Почему бы вам просто не вставить свой результат?

2. @LaurenzAlbe «вложите идентификатор XML и подготовьте результирующий набор» означает итерацию элемента ID в xml и подготовку данных подэлементов PGID и PGNAME. Можете ли вы проверить этот раздел «Я хочу вставить данные следующим образом» в описании, как мне нужен конечный результат

Ответ №1:

Следующее делает то, что вы хотите, и немного короче:

 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)
;

Онлайн-пример

В более современной версии Postgres вы могли бы использовать 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