KargaltsevMikhail 24 июн 2020 в 12:54

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

8 мин

19K

SQL*XML*Microsoft SQL Server*

Небольшая заметка по формированию 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>

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

Теги:

Хабы:

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

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

FOR XML PATH

Публикации

Истории

Ближайшие события