xml parse in postgresSQL

Question

I have a problem with parse simple xml:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <data-set>
        <department>
            <ID>1</ID>
            <Name>IT</Name>
            <ID_CUSTOMER>1</ID_CUSTOMER>
            <ID_CUSTOMER>2</ID_CUSTOMER>
            <ID_CUSTOMER>3</ID_CUSTOMER>
            <ID_CUSTOMER>4</ID_CUSTOMER>
            <ID_CUSTOMER>5</ID_CUSTOMER>
            <Adress>Pražská</Adress>
            <Desc>platba kartou</Desc>
        </department>

    </data-set>

This XML file I parsed with use code:

DO $$
   DECLARE myxml xml;
BEGIN

myxml := XMLPARSE(DOCUMENT convert_from(pg_read_binary_file('MyData.xml'), 'UTF8'));

DROP TABLE IF EXISTS my;
CREATE TABLE my AS 

SELECT 
     (xpath('//ID', x))[1]::text AS ID,
     (xpath('data-set/@Name', x))[1]::text AS Name,

     (xpath('//ID_CUSTOMER', x))[1]::text AS id_customer,
     (xpath('//Adress', x))[1]::text AS Adress,

     (xpath('//Desc', x))[1]::text AS tgen


FROM unnest(xpath('//data-set', myxml)) x
;

END$$;

select * from my

Unfortunately, this parse gives me only first single row in result.

I need create table where are all record in to relevant rows:

Rows1 - ID 1, ID_CUSTOMER 1, Adress Pražská, Desc Platba kartou
Rows2 - ID 1, ID_CUSTOMER 2, Adress Pražská, Desc Platba kartou
Rows3 - ID 1, ID_CUSTOMER 3, Adress Pražský, Desc Platba kartou
Rows4 - ........
Rows5 - ........

Thank you for your tips.

a_vlad · Answer 1 · 2016-11-14T07:10:55.243

if I correct I understand You - Your problem is single row in result You can use unnest function for return all values

EDIT Text of query after questions from author:

DO $$

DECLARE xml_string xml;

BEGIN
xml_string := XMLPARSE(DOCUMENT convert_from(pg_read_binary_file('MyData.xml'), 'UTF8'));



DROP TABLE IF EXISTS my;
CREATE TABLE my AS 
SELECT
    unnest(xpath('//ID/text()', xml_string)) as ID,
    unnest(xpath('//Name/text()', xml_string)) as Name,
    unnest(xpath('//ID_CUSTOMER/text()', xml_string)) as ID_CUSTOMER;

END$$



SELECT * FROM my;

1   IT  1
1   IT  2
1   IT  3
1   IT  4
1   IT  5

xml parse in postgresSQL

1 Answers1