解析xml以在postgresql中创建插入(parse xml for creating inserts in postgresql)

我已经被赋予了创建将层次数据从postres关系表转换为xml和返回的机制的任务。虽然我没有太多的麻烦将表格数据转换为XML，我坚持将实际的XML转换为多个插入。随着postgres提供的文档，我什么也不能做（任何帮助将不胜感激。

xml示例（独立的Button元素和具有子元素的网格）：

解析XML后，我将生成ID为806 806的按钮的插入语句

INSERT INTO TABLE (ID, PARENT_ID,POSITION_IN_PARENT) VALUES (806,NULL,NULL)

然后我要生成一系列GRID及其列（GRDCOL）的插入，

<GRID> 217 <GRID_COLUMNS> <GRDCOL visible="true" vl_order="0" description="some description"> <vl_props>some info</vl_props> <vl_params>some info</vl_svc_params> 226 </GRDCOL> <GRDCOL visible="true" vl_order="1" title="some description"> <vl_props>some info</vl_props> <vl_params_1>some info</vl_svc_params> 218 </GRDCOL> <GRDCOL visible="true" vl_order="2" title="some description"> <vl_props>some info</vl_props> <vl_svc_params>some info</vl_svc_params> 219 </GRDCOL> <GRDCOL visible="true" vl_order="3" title="some description"> <vl_props>some info</vl_props> <vl_params>some info</vl_svc_params> 220 </GRDCOL> </GRID_COLUMNS> </GRID> INSERT INTO TABLE (ID, PARENT_ID,POSITION_IN_PARENT) VALUES (217,NULL,NULL) --grid itself INSERT INTO TABLE (ID, PARENT_ID, POSITION_IN_PARENT) VALUES (226,217,GRID_COLUMNS) INSERT INTO TABLE (ID, PARENT_ID, POSITION_IN_PARENT) VALUES (218,217,GRID_COLUMNS) INSERT INTO TABLE (ID, PARENT_ID, POSITION_IN_PARENT) VALUES (219,217,GRID_COLUMNS) INSERT INTO TABLE (ID, PARENT_ID, POSITION_IN_PARENT) VALUES (220,217,GRID_COLUMNS)

我无法弄清楚遍历xml中每个节点的方式，只能用Postgres生成插入。有任何想法吗？

I've been given a task of creating the mechanism of transforming hierarchical data from postres relational table to xml and back. Whilst I had not much trouble converting table data to xml, I am stuck in converting actual xml to multiple inserts. With the documentation that is provided with postgres I could do nothing(. Any help would be appreciated.

Example of xml (independent Button element and Grid with children):

After parsing xml I am to generate insert statement for button with id 806 806

INSERT INTO TABLE (ID, PARENT_ID,POSITION_IN_PARENT) VALUES (806,NULL,NULL)

Then I am to generate a series of inserts of GRID and its columns (GRDCOL),

<GRID> 217 <GRID_COLUMNS> <GRDCOL visible="true" vl_order="0" description="some description"> <vl_props>some info</vl_props> <vl_params>some info</vl_svc_params> 226 </GRDCOL> <GRDCOL visible="true" vl_order="1" title="some description"> <vl_props>some info</vl_props> <vl_params_1>some info</vl_svc_params> 218 </GRDCOL> <GRDCOL visible="true" vl_order="2" title="some description"> <vl_props>some info</vl_props> <vl_svc_params>some info</vl_svc_params> 219 </GRDCOL> <GRDCOL visible="true" vl_order="3" title="some description"> <vl_props>some info</vl_props> <vl_params>some info</vl_svc_params> 220 </GRDCOL> </GRID_COLUMNS> </GRID> INSERT INTO TABLE (ID, PARENT_ID,POSITION_IN_PARENT) VALUES (217,NULL,NULL) --grid itself INSERT INTO TABLE (ID, PARENT_ID, POSITION_IN_PARENT) VALUES (226,217,GRID_COLUMNS) INSERT INTO TABLE (ID, PARENT_ID, POSITION_IN_PARENT) VALUES (218,217,GRID_COLUMNS) INSERT INTO TABLE (ID, PARENT_ID, POSITION_IN_PARENT) VALUES (219,217,GRID_COLUMNS) INSERT INTO TABLE (ID, PARENT_ID, POSITION_IN_PARENT) VALUES (220,217,GRID_COLUMNS)

I can not figure out the way to traverse through each node in xml and generate inserts with Postgres only. Any ideas?

最满意答案

解析XML文档稍微困难一些，然后生成。但这是可能的。

您可以使用XPATH功能 - 它对于大型文档来说足够快并且很好。

对于像以下文件：

<?xml version="1.0" encoding="windows-1250"?> <enprimeur> <vino> <id>1</id> <nazev>Alter Ego de Palmer</nazev> <vyrobce>63</vyrobce> <rocnik>2012</rocnik> <cena0375>0</cena0375> <cena1500>0</cena1500> <cena3000>0</cena3000> <cena6000>0</cena6000> <cena0750>1425</cena0750> <cenastart>1085</cenastart> <min0375>0</min0375> <min0750>0</min0750> <odrudy>51 % Merlot, 40 % Cabernet Sauvignon,9 % Petit Verdot</odrudy> <bestin>2017 - 2026</bestin> <klas>2</klas> <sklad0375>0</sklad0375> <sklad0750>0</sklad0750> <sklad1500>0</sklad1500> <sklad3000>0</sklad3000> <sklad6000>0</sklad6000> <alk>13,4 %</alk> <remark>Premiant oblasti Margaux Ch. Palmer tentokrát ve svých obou vínech tì.il z dokonale zralého Merlotu, kterého do svých smìsí naládova <rating>Robert Parker: /100 TOPVINO SCORE: 92-94/100 James Suckling: 92-93/100 Wine Spectator: 90-93/100</rating> <zalozeno></zalozeno> <rozloha></rozloha> <stari></stari> <puda></puda> <produkce></produkce> <zrani></zrani> <active>1</active> <stitky> <stitek>8</stitek> <stitek>1</stitek> </stitky> </vino> <vino> ...

你可以使用q查询：

select x[1]::int id, x[2] nazev, x[3] vyrobce, x[4]::int rocnik, x[5]::double precision cena0375, x[6]::double precision cena1500, x[7]::double precision cena3000, x[7]::double precision cena6000, x[8]::double precision cena0750, x[9]::double precision cenastart, x[10]::double precision min0375, x[11]::double precision min0750, x[12] alk, x[13] klas from (select xpath('/vino/id/text()|' '/vino/nazev/text()|/vino/vyrobce/text()|/vino/rocnik/text()|' '/vino/cena0375/text()|/vino/cena1500/text()|/vino/cena3000/text()|' '/vino/cena6000/text()|/vino/cena0750/text()|/vino/cenastart/text()|' '/vino/min0375/text()|/vino/min0750/text()|/vino/alk/text()|' '/vino/klas/text()', v)::text[] x from (select unnest(xpath('/enprimeur/vino', d)) from xmldata) g(v)) s;

The solution I came across is quite straightforward.

Firstly we find out how many children are there in current xml.

SELECT btrim(xpath('count(./*)', p_input_xml, ARRAY [ ARRAY [ 'el', 'http://example.com' ], array [ 'p', 'http://example1.com' ] ]) ::text, '{}') into v_child_count;

Then we iterate through children and then recursively we find the children of the children and so on. Thus we shred the xml into smaller xml.

select replace(btrim(xpath(format('./*[%s]', CURRENT_CHILD_ID), p_input_xml, ARRAY [ ARRAY [ 'el', 'http://example.com' ] ]) ::text, '"{}"'), '\"', '"') ::xml into v_child_xml;

更多推荐

解析xml以在postgresql中创建插入(parse xml for creating inserts in postgresql)

最满意答案

发布评论取消回复

最近发表

热门文章

标签列表