SQL. Запрос к вложенным каталогам

Question

есть каталог 'catalogs' с вложенными блоками 'block' исходные данные

declare @id_block bigint = 111 и from catalogs или from block, как вариант - использовать id, parent_id нужно получить таблицу вида:

<catalogs>
    <block>
        <id>111</id>
        <type>vision</type>
        <name>name_1</name>
        <parent_id>222</parent_id>
    </block>
    <block>
        <id>222</id>
        <type>vision</type>
        <name>name_2</name>
        <parent_id>333</parent_id>
    </block>
    <block>
        <id>333</id>
        <type>vision</type>
        <name>name_3</name>
        <parent_id>444</parent_id>
    </block>
    <block>
        <id>444</id>
        <type>vision</type>
        <name>name_4</name>
    </block>
    <block>
        <id>555</id>
        <type>bun</type>
        <name>name_5</name>
    </block>
</catalogs>

Answer 1

Используя XQuery и рекурсивный CTE.

SQL

DECLARE @xml XML =
N'<catalogs>
    <block>
        <id>111</id>
        <type>vision</type>
        <name>name_1</name>
        <parent_id>222</parent_id>
    </block>
    <block>
        <id>222</id>
        <type>vision</type>
        <name>name_2</name>
        <parent_id>333</parent_id>
    </block>
    <block>
        <id>333</id>
        <type>vision</type>
        <name>name_3</name>
        <parent_id>444</parent_id>
    </block>
    <block>
        <id>444</id>
        <type>vision</type>
        <name>name_4</name>
    </block>
    <block>
        <id>555</id>
        <type>bun</type>
        <name>name_5</name>
    </block>
</catalogs>';

DECLARE @id_block INT = 111;

;WITH rs AS
(
    SELECT c.value('(id/text())[1]', 'INT') AS id
        , c.value('(type/text())[1]', 'VARCHAR(20)') AS [type]
        , c.value('(name/text())[1]', 'VARCHAR(20)') AS [name]
        , c.value('(parent_id/text())[1]', 'INT') AS [parent_id]
    FROM @xml.nodes('/catalogs/block') AS t(c)
), cte AS
(
    SELECT * FROM rs
    WHERE rs.id = @id_block
    UNION ALL
    SELECT e.* FROM rs AS e
        INNER JOIN cte AS ecte ON ecte.parent_id = e.id
)
SELECT id, [type], [name]
FROM cte;

БЛОГ НА HUSL

SQL. Запрос к вложенным каталогам

Ответы (1 шт):