Postgresql XML-to-table with nested data

Question

I have some XML that needs to be inserted into two tables, "products" and "barcodes". The XML looks something like

<products>
  <product>
    <code>0001</code>
    <name>Prod1</name>
    <active>t</active>
    <barcodes>
        <barcode>0001666</barcode>
        <barcode>6660001</barcode>
    </barcodes>
  <product>
  <product>
    ... another one
  </product>

and the product table consist of the three attributes and the barcode table is a simple barcode, product code tuple with a relation the the product table. I can do something like

    insert into product (code, name, active) (
    with products(prod_row) as (select-xml-column-from-table)
        select 
            unnest(xpath('//product/code/text()', prod_row)),
            unnest(xpath('//product/name/text()', prod_row)),
            unnest(xpath('//product/active/text()', prod_row))
        from products
);

and it works for the products but is there a sane way of filling the barcode-table with references to the product code in the same statement?

BTW. I haven't been able to find a cast that would work for an insert into the boolean "active", any pointers?

Thanks in advance, Nik

S-Man · Accepted Answer · 2018-10-30 10:19:42Z

demo: db<>fiddle

Test XML:

<products>
    <product>
        <code>0001</code>
        <name>Prod1</name>
        <active>t</active>
        <barcodes>
            <barcode>0001666</barcode>
            <barcode>6660001</barcode>
        </barcodes>
    </product>
    <product>
        <code>0002</code>
        <name>Prod2</name>
        <active>f</active>
        <barcodes>
            <barcode>0000420</barcode>
        </barcodes>
    </product>
</products>

Query:

WITH xmldata AS (
    SELECT '<products><product><code>0001</code><name>Prod1</name><active>t</active><barcodes><barcode>0001666</barcode><barcode>6660001</barcode></barcodes></product><product><code>0002</code><name>Prod2</name><active>f</active><barcodes><barcode>0000420</barcode></barcodes></product></products>'::xml
), insert_products AS (
    INSERT INTO products (code, name, active)
    SELECT 
        unnest(xpath('//product/code/text()', xml)),
        unnest(xpath('//product/name/text()', xml)),
        unnest(xpath('//product/active/text()', xml))::text = 't'
    FROM xmldata
    RETURNING code                                                     -- 1
)
INSERT INTO barcodes (barcode, product_code)
SELECT 
    unnest(xpath('//barcode/text()', xd.barcodes)),                    -- 4
    ip.code
FROM (
    SELECT 
        unnest(xpath('//product/code/text()', xml))::text as code,     -- 2
        unnest(xpath('//product/barcodes', xml)) as barcodes
    FROM xmldata
)xd
JOIN insert_products ip                                                -- 3
ON xd.code = ip.code

With the help of a CTE two chained INSERT statements can be created. So you could get the code as RETURNING value of the first statement.

With that you can search the right barcodes per product code to create the insert data of the second INSERT statement.

Insert the product data as you did. But returning the product code.
Selecting the product code again and the barcode data as XML.
Joining the inserted products against the origin data to assign the inserted codes to the right barcodes.
Extracting the barcode data and insert them into the barcodes table.

Result:

Table products:

code   name    active
0001   Prod1   t
0002   Prod2   f

----------------------

Table barcodes:

barcode   product_code
0001666   0001
6660001   0001
0000420   0002

Technically you could safe the joining part if you don't have any filters in your product insert because in both steps you are working on the whole data of the same XML. The join only makes sense if you want to filter out some products and do not want to store out filtered barcodes.

demo:db<>fiddle without join

demo:db<>fiddle with join and filter

Notice the way how you could solve your boolean problem:

unnest(xpath('//product/active/text()', xml))::text = 't'

Convert the XML content into type text and compare it with your TRUE value. The comparison gives out a boolean.

Edit: In your case this could be done even simpler: You don't need the comparison but only a second cast:

unnest(xpath('//product/active/text()', xml))::text::boolean

Thank you for the exhaustive answer, I never thought the inserts could be chained like that.

ajay kumar mulakala · Accepted Answer · 2021-04-01 08:58:53Z

0

-- TestQuery with sample data to get the table of information from a nested XML

    WITH xmldata AS ( SELECT '<products>
                <product>
                    <code>A001</code>
                    <name>ProductA1</name>
                    <active>t</active>
                </product>
                <product>
                    <code>B002</code>
                    <name>ProductA2</name>
                    <active>f</active>
                </product>
             </products>'::xml)
SELECT  unnest(xpath('//product/code/text()', xml)) as code,
    unnest(xpath('//product/name/text()', xml)) as name,
    unnest(xpath('//product/active/text()', xml))::text = 't' as status
FROM xmldata;

answered Apr 1, 2021 at 8:58

ajay kumar mulakala

112 bronze badges

Collectives™ on Stack Overflow

Postgresql XML-to-table with nested data

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related