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

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

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