T-SQL. Формирование XML со списком значений / forpes.ru

Главная
T-SQL. Формирование XML со списком значений

T-SQL. Формирование XML со списком значений +4

24.06.2020 09:54

KargaltsevMikhail 7 675 Источник

Небольшая заметка по формированию XML

FOR XML PATH

Для формирования структуры XML-документа со списком значений можно воспользоваться режимом PATH для FOR XML в T-SQL.

<root>
    <level1>
        <level2></level2>
        <values>
            <value></value>
            <value></value> 
            <value></value>
            <value></value>
            <value></value>
        </values>
     </level1>
     <level1>
         <level2></level2>
         <values>
             <value></value>
             <value></value>
             <value></value>
             <value></value>
             <value></value>
         </values>
     </level1>
</root>

Ниже представлен sql код создания тестовых таблиц для демонстрации запросов:

if object_id('dbo.ProductClass', 'U') is not null
    drop table dbo.ProductClass;
go

create table dbo.ProductClass(
    ProductClassId int identity
   ,ProductClassName nvarchar(16)
   ,constraint PK_ProductClass primary key(ProductClassId));
go

insert into dbo.ProductClass(ProductClassName)
    values
        ('Class_1')
       ,('Class_2')
       ,('Class_3');
go

select * from dbo.ProductClass;

1	Class_1
2	Class_2
3	Class_3

Запросы будут задействовать две таблицы dbo.ProductClass и dbo.Product со связью «один ко многим»(в одном классе может быть более одного продукта).

if object_id('dbo.Product', 'U') is not null
    drop table dbo.Product;
go

create table dbo.Product(
    ProductClassId int
    ,ProductId int identity
    ,ProductName nvarchar(16)
    ,constraint PK_Product primary key(ProductId)
    ,constraint FK_ProductClass foreign key(ProductClassId)
        references dbo.ProductClass(ProductClassId));
go

insert into dbo.Product(ProductClassId, ProductName)
    values
        (1, 'Product_1')
        ,(2, 'Product_2')
        ,(3, 'Product_3')
        ,(1, 'Product_4')
        ,(2, 'Product_5')
        ,(3, 'Product_6')
        ,(1, 'Product_7')
        ,(3, 'Product_8')
        ,(2, 'Product_9')
        ,(1, 'Product_10');
go

select * from dbo.Product;

1	1	Product_1
2	2	Product_2
3	3	Product_3
1	4	Product_4
2	5	Product_5
3	6	Product_6
1	7	Product_7
3	8	Product_8
2	9	Product_9
1	10	Product_10

Следующий запрос позволяет построить XML документ со списком значений ProductName таблицы dbo.Product для каждого класса продукта(ProductClassName) в одноименных элементах:

select pc.ProductClassName as ProductClassName
    ,(select p.ProductName as ProductName
        from dbo.Product p
    where p.ProductClassId = pc.ProductClassId
        for xml path(''), type) as "Products"
from dbo.ProductClass pc
    for xml path('ProductClass'), type

Список значений в элементе Products формируется подзапросом:

(select p.ProductName as ProductName
    from dbo.Product p
where p.ProductClassId = pc.ProductClassId
    for xml path(''), type) as "Products")

Псевдонимы определяют «имена» XML элементов. Директива TYPE позволяет получить результат запроса в виде типа данных xml. Для того что бы не формировался родительский элемент для списка значений элементов Product.ProductName, значение PATH не указывается(for xml path('')).

Результатом запроса будет следующий XML-документ:

<ProductClass>
    <ProductClassName>Class_1</ProductClassName>
    <Products>
        <ProductName>Product_1</ProductName>
        <ProductName>Product_4</ProductName>
        <ProductName>Product_7</ProductName>
        <ProductName>Product_10</ProductName>
    </Products>
</ProductClass>
<ProductClass>
    <ProductClassName>Class_2</ProductClassName>
    <Products>
        <ProductName>Product_2</ProductName>
        <ProductName>Product_5</ProductName>
        <ProductName>Product_9</ProductName>
    </Products>
</ProductClass>
<ProductClass>
    <ProductClassName>Class_3</ProductClassName>
    <Products>
        <ProductName>Product_3</ProductName>
        <ProductName>Product_6</ProductName>
        <ProductName>Product_8</ProductName>
    </Products>
</ProductClass>

Для создания списка со значениями в атрибутах элемента, псевдонимы указываются в кавычках(одинарные или двойные) с символом '@' в начале:

select pc.ProductClassName as ProductClassName
    ,(select p.ProductId as "@ProductId", p.ProductName as '@ProductName'
        from dbo.Product p
    where p.ProductClassId = pc.ProductClassId
        for xml path('Product'), type) as "Products"
from dbo.ProductClass pc
    for xml path('ProductClass'), type

Значение PATH есть «имя» элемента в котором содержаться атрибуты, результ запроса следующий:

<ProductClass>
    <ProductClassName>Class_1</ProductClassName>
    <Products>
        <Product ProductId="1" ProductName="Product_1" />
        <Product ProductId="4" ProductName="Product_4" />
        <Product ProductId="7" ProductName="Product_7" />
        <Product ProductId="10" ProductName="Product_10" />
    </Products>
</ProductClass>
<ProductClass>
    <ProductClassName>Class_2</ProductClassName>
    <Products>
        <Product ProductId="2" ProductName="Product_2" />
        <Product ProductId="5" ProductName="Product_5" />
        <Product ProductId="9" ProductName="Product_9" />
    </Products>
</ProductClass>
<ProductClass>
    <ProductClassName>Class_3</ProductClassName>
    <Products>
        <Product ProductId="3" ProductName="Product_3" />
        <Product ProductId="6" ProductName="Product_6" />
        <Product ProductId="8" ProductName="Product_8" />
    </Products>
</ProductClass>

Атрибуты можно разместить в отдельных элементах:

select pc.ProductClassName as ProductClassName
    ,(select p.ProductId as "@ProductId", p.ProductName as ProductName
        from dbo.Product p
    where p.ProductClassId = pc.ProductClassId
        for xml path('Product'), type) as "Products"
from dbo.ProductClass pc
    for xml path('ProductClass'), type

Результат запроса:

<ProductClass>
  <ProductClassName>Class_1</ProductClassName>
  <Products>
    <Product ProductId="1">
      <ProductName>Product_1</ProductName>
    </Product>
    <Product ProductId="4">
      <ProductName>Product_4</ProductName>
    </Product>
    <Product ProductId="7">
      <ProductName>Product_7</ProductName>
    </Product>
    <Product ProductId="10">
      <ProductName>Product_10</ProductName>
    </Product>
  </Products>
</ProductClass>
<ProductClass>
  <ProductClassName>Class_2</ProductClassName>
  <Products>
    <Product ProductId="2">
      <ProductName>Product_2</ProductName>
    </Product>
    <Product ProductId="5">
      <ProductName>Product_5</ProductName>
    </Product>
    <Product ProductId="9">
      <ProductName>Product_9</ProductName>
    </Product>
  </Products>
</ProductClass>
<ProductClass>
  <ProductClassName>Class_3</ProductClassName>
  <Products>
    <Product ProductId="3">
      <ProductName>Product_3</ProductName>
    </Product>
    <Product ProductId="6">
      <ProductName>Product_6</ProductName>
    </Product>
    <Product ProductId="8">
      <ProductName>Product_8</ProductName>
    </Product>
  </Products>
</ProductClass>

Product.ProductName как значение элемента Product и Product.ProductId как значение атрибута этого же элемента:

select pc.ProductClassName as ProductClassName
    ,(select p.ProductId as "@ProductId"
	,(select pn.ProductName 
		from dbo.Product pn
	where pn.ProductId = p.ProductId)
        from dbo.Product p
    where p.ProductClassId = pc.ProductClassId
        for xml path('Product'), type) as "Products"
from dbo.ProductClass pc
    for xml path('ProductClass'), type

Результат:

<ProductClass>
  <ProductClassName>Class_1</ProductClassName>
  <Products>
    <Product ProductId="1">Product_1</Product>
    <Product ProductId="4">Product_4</Product>
    <Product ProductId="7">Product_7</Product>
    <Product ProductId="10">Product_10</Product>
  </Products>
</ProductClass>
<ProductClass>
  <ProductClassName>Class_2</ProductClassName>
  <Products>
    <Product ProductId="2">Product_2</Product>
    <Product ProductId="5">Product_5</Product>
    <Product ProductId="9">Product_9</Product>
  </Products>
</ProductClass>
<ProductClass>
  <ProductClassName>Class_3</ProductClassName>
  <Products>
    <Product ProductId="3">Product_3</Product>
    <Product ProductId="6">Product_6</Product>
    <Product ProductId="8">Product_8</Product>
  </Products>
</ProductClass>

Использование конструкции WITH XMLNAMESPACES позволяет добавить пространства имен в XML:

WITH XMLNAMESPACES ('uri1' as lev)  
select pc.ProductClassName as "lev:ProductClassName"
    ,(select p.ProductName as "lev:ProductName"
        from dbo.Product p
    where p.ProductClassId = pc.ProductClassId
        for xml path(''), type) as "lev:Products"
from dbo.ProductClass pc
    for xml path('lev:ProductClass'), type

<lev:ProductClass xmlns:lev="uri1">
  <lev:ProductClassName>Class_1</lev:ProductClassName>
  <lev:Products>
    <lev:ProductName xmlns:lev="uri1">Product_1</lev:ProductName>
    <lev:ProductName xmlns:lev="uri1">Product_4</lev:ProductName>
    <lev:ProductName xmlns:lev="uri1">Product_7</lev:ProductName>
    <lev:ProductName xmlns:lev="uri1">Product_10</lev:ProductName>
  </lev:Products>
</lev:ProductClass>
<lev:ProductClass xmlns:lev="uri1">
  <lev:ProductClassName>Class_2</lev:ProductClassName>
  <lev:Products>
    <lev:ProductName xmlns:lev="uri1">Product_2</lev:ProductName>
    <lev:ProductName xmlns:lev="uri1">Product_5</lev:ProductName>
    <lev:ProductName xmlns:lev="uri1">Product_9</lev:ProductName>
  </lev:Products>
</lev:ProductClass>
<lev:ProductClass xmlns:lev="uri1">
  <lev:ProductClassName>Class_3</lev:ProductClassName>
  <lev:Products>
    <lev:ProductName xmlns:lev="uri1">Product_3</lev:ProductName>
    <lev:ProductName xmlns:lev="uri1">Product_6</lev:ProductName>
    <lev:ProductName xmlns:lev="uri1">Product_8</lev:ProductName>
  </lev:Products>
</lev:ProductClass>

Конструкция data() позволяет сформировать список значений, например требуется перечислить все значения ProductId для каждого класса продукта в элементе ProductClass:

select pc.ProductClassName as "@ProductClassName"
    ,(select pid.ProductId as "data()"
        from dbo.Product pid
    where pid.ProductClassId = pc.ProductClassId
        for xml path ('')) as "@ProductIds"
    ,(select p.ProductId as "@ProductId", p.ProductName as '@ProductName'
        from dbo.Product p
    where p.ProductClassId = pc.ProductClassId
        for xml path('Product'), type) as "Products"
from dbo.ProductClass pc
    for xml path('ProductClass'), type

<ProductClass ProductClassName="Class_1" ProductIds="1 4 7 10">
  <Products>
    <Product ProductId="1" ProductName="Product_1" />
    <Product ProductId="4" ProductName="Product_4" />
    <Product ProductId="7" ProductName="Product_7" />
    <Product ProductId="10" ProductName="Product_10" />
  </Products>
</ProductClass>
<ProductClass ProductClassName="Class_2" ProductIds="2 5 9">
  <Products>
    <Product ProductId="2" ProductName="Product_2" />
    <Product ProductId="5" ProductName="Product_5" />
    <Product ProductId="9" ProductName="Product_9" />
  </Products>
</ProductClass>
<ProductClass ProductClassName="Class_3" ProductIds="3 6 8">
  <Products>
    <Product ProductId="3" ProductName="Product_3" />
    <Product ProductId="6" ProductName="Product_6" />
    <Product ProductId="8" ProductName="Product_8" />
  </Products>
</ProductClass>

Больше информации

Комментарии (7)

Doomsday_nxt
24.06.2020 13:35
#21770314
Одно я заметил — все эти XML-функции ужасно медленные. В моих случаях проще было обработку XML делать на клиенте.
1. KargaltsevMikhail Автор
  24.06.2020 16:04
  #21770972
  Возможно стоит сделать некий замер скорости формирования XML с помощью средств T-SQL и например технологии LINQ to XML в C#
  1. Doomsday_nxt
    24.06.2020 16:39
    #21771160
    И добавить ещё SQLCLR. Да, такой замер был бы интересен (и в обе стороны — в MSSQL есть поля XML, их которых можно извлекать данные).
    
    NewDevLab
    25.06.2020 08:02
    #21773906
    SQLCLR в 2019 сервере какую версию фрейворка использует? не ищется.
    
    KargaltsevMikhail Автор
    25.06.2020 08:09
    #21773934
    Автором комментария имелась ввиду интеграция SQL Server со средой CLR инфраструктуры .NET Framework. Например, формирование XML-документа можно реализовать в виде хранимой процедуры или пользовательской функции для SQL Server на языке C#. Подробнее можно посмотреть тут.
    
    NewDevLab
    25.06.2020 08:18
    #21773956
    Это понятно, я вот и спрашиваю: с какой версией Net Framework интегрируется? В SqlServer 2017 всё еще была версия .Net Framework 2.0. Или как указать целевую версию…
    
    KargaltsevMikhail Автор
    25.06.2020 08:36
    #21774006
    Для SQL Server версия .NET Framework не важна. Код, который вы пишите на С# или VB компилируется в байт-код CIL, общий для CLR.

T-SQL. Формирование XML со списком значений +4

Небольшая заметка по формированию XML

FOR XML PATH

Комментарии (7)

Doomsday_nxt

KargaltsevMikhail Автор

Doomsday_nxt

NewDevLab

KargaltsevMikhail Автор

NewDevLab

KargaltsevMikhail Автор