4

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.

1 Answer 1

2

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
1
  • I just give You idea - of course I NOT create file, I just parse table with XML column
    – a_vlad
    Nov 14, 2016 at 6:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.