Postgresql XML-to-table с вложенными данными

демонстрация: db <> рабочий пример

Тестовый 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>

Запрос:

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

С помощью CTE можно создать два связанных оператора INSERT. Таким образом, вы можете получить код как значение RETURNING первого оператора.

Благодаря этому вы можете искать нужные штрих-коды для каждого кода продукта, чтобы создать данные для вставки второго оператора INSERT.

1. Вставьте данные о продукте, как вы это сделали. Но вернул код товара.
2. Повторный выбор кода продукта и данных штрих-кода в формате XML.
3. Объединение вставленных продуктов с данными о происхождении для присвоения вставленных кодов правильным штрих-кодам.
4. Извлечение данных штрих-кода и вставка их в таблицу штрих-кодов.

Результат:

Table products:

code   name    active
0001   Prod1   t
0002   Prod2   f

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

Table barcodes:

barcode   product_code
0001666   0001
6660001   0001
0000420   0002

Технически вы можете защитить присоединяемую часть, если у вас нет фильтров во вставке продукта, потому что на обоих этапах вы работаете со всеми данными одного и того же XML. Объединение имеет смысл только в том случае, если вы хотите отфильтровать некоторые продукты и не хотите сохранять отфильтрованные штрих-коды.

демонстрация: db <> рабочий пример без соединения

демонстрация: db <> рабочий пример с объединением и фильтром

Уведомление способ решения проблемы с boolean:

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

Преобразуйте содержимое XML в тип text и сравните его со своим значением TRUE. Сравнение выдает boolean.

Обновлено: в вашем случае это можно было бы сделать еще проще: вам не нужно сравнение, а только второе приведение:

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

- TestQuery с образцами данных для получения таблицы информации из вложенного 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;