Генерировать структурированный (xml) документ из данных иерархической таблицы (T-SQL)

У меня есть таблица, как это (упрощенно):

ID   |   Name  |   Parent
---------------------------------
1    |  IND    |   NULL
2    |  INS    |   5
3    |  CON    |   NULL
4    |  AUT    |   1
5    |  FIN    |   NULL
6    |  PHA    |   1
7    |  CFIN   |   5
8    |  CMRKT  |   7

DDL:

CREATE TABLE [dbo].[tblIndustryCodes](
        [IdIndustry] [int] IDENTITY(1,1) NOT NULL,
        [IndustryCode] [nvarchar](5) NULL,
        [IndustryName] [nvarchar](50) NULL,
        [ParentId] [int] NULL,
 CONSTRAINT [PK_tblIndustryCodes] PRIMARY KEY CLUSTERED (       [IdIndustry] ASC))

Вставки:

INSERT INTO [tblIndustryCodes]
           ([IndustryCode]
           ,[IndustryName]
           ,[ParentId])
     VALUES
           ('IND','Industry',NULL),
           ('PHARM','Pharmacy',1),
           ('FIN','Finance',NULL),
           ('CFIN','Corporate Finance',3),
           ('CMRKT','Capital Markets',4)

И я хотел бы создать файл XML из него, который структурирован в соответствии с родительскими идентификаторами

как это (упрощенно)

<IND>
   <AUT>
   <PHA>
<CON>
<FIN>
   <CFIN>
      <CMRKT>

Я верю, что это возможно с помощью некоторой рекурсии или чего-то в этом роде, но я не знаю как. Любая помощь очень ценится!

редактировать: это SQL Server Express 2008

Мне все равно, является ли это действительным XML или нет, потому что я использую его только для заполнения элемента управления в виде дерева.

edit2: я бы, вероятно, использовал "FOR XML EXPLICIT", но я не совсем понимаю синтаксис, когда нет фиксированной максимальной глубины дерева.

edit3: для облегчения понимания задачи, я добавил DDL для таблицы

Источник

2 ответа

Решение

Основываясь на ответе Реджепа (см. Комментарии), я создал следующее решение этой проблемы:

1. Создать рекурсивную функцию

CREATE function SelectChild(@key as int)
returns xml
begin
    return (
        select 
            IdIndustry as "@key", 
            ParentId as "@parentkey",
            IndustryCode as "@Code",
            IndustryName as "@Name",
            dbo.SelectChild(IdIndustry)
        from tblIndustryCodes
        where ParentId = @key
        for xml path('record'), type
    )
end

2. Создайте оператор SELECT, который вызывает функцию

SELECT 
    IdIndustry AS "@key", 
    '' AS "@parentkey",
    IndustryCode as "@Code",
    IndustryName as "@Name",
    dbo.SelectChild(IdIndustry)     
FROM dbo.tblIndustryCodes
WHERE ParentId is null
FOR XML PATH ('record')

Это создает иерархический XML, независимо от того, насколько глубоко дерево на самом деле:

<record key="1" parentkey="" Code="IND" Name="Industry">
  <record key="2" parentkey="1" Code="AUTO" Name="Automotive" />
  <record key="3" parentkey="1" Code="PHARM" Name="Pharmaceuticals" />
</record>
<record key="4" parentkey="" Code="FIN" Name="Finance">
  <record key="5" parentkey="4" Code="CFIN" Name="Corporate Finance">
    <record key="6" parentkey="5" Code="CMRKT" Name="Capital Markets" />
  </record>
</record>
<record key="7" parentkey="" Code="CON" Name="Cosulting">
  <record key="8" parentkey="7" Code="IMPL" Name="Implementation" />
  <record key="9" parentkey="7" Code="STRAT" Name="Strategy" />
</record>
Источник

Вы также можете сделать это без создания отдельной функции, включив подзапрос в качестве дополнительного столбца, возвращающего XML. Например, следующее вернет иерархический XML-документ, содержащий пользователей и связанный с ними список ролей:

SELECT 
    FirstName, LastName,
    CONVERT(XML, 
      (SELECT r.UserID, r.RoleID
       FROM global.[UserRole] r
       WHERE r.USerID = [user].UserID
       FOR XML RAW ('Role'), ELEMENTS, root('Roles')
      ))
FROM global.[user]
FOR XML RAW ('User'), ELEMENTS, root('Users')
Источник
Другие вопросы по тегам