Формирование xml файла через FOR XML PATH

Режим Path с командой FOR XML в SQL Server возвращает набор результатов как элемент XML. В отличие от других режимов XML, этот режим SQL FOR XML PATH обеспечивает контроль над созданным файлом XML. Это связано с тем, что режим FOR XML PATH обрабатывает имена столбцов и псевдонимы как выражение XPath.

В этом примере режима SQL FOR XML PATH мы используем таблицы «Products» «Suppliers» и «Categories», имеющиеся в тестовой базе данных SQL.

В этом примере мы будем использовать тестовую базу данных northwind—pubs(instnwnd.sql)

Прежде чем вы начнете

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

Среда SQL Server Management Studio (SSMS). Чтобы загрузить SSMS, перейдите в раздел Загрузка среды SQL Server Management Studio (SSMS).
SQL Server Data Tools (SSDT) или Visual Studio. Чтобы загрузить SSDT или включить его функции в Visual Studio, перейдите к разделу загрузка и установка SQL Server Data Tools (SSDT) для Visual Studio.

Запуск скриптов в SSMS

Откройте SSMS.
Подключитесь к целевому SQL-серверу.
Откройте скрипт в новом окне запроса.
Запустите скрипт.

SQL FOR XML PATH Пример 1

В этом примере показан основной способ использования режима FOR XML PATH. Самый простой способ использования режима пути — добавить FOR XML PATH после оператора Select.

USE [Northwind]
GO
SELECT [ProductID]
      ,[ProductName]
      ,[SupplierID]
      ,[CategoryID]
      ,[QuantityPerUnit]
      ,[UnitPrice]
  FROM [Northwind].[dbo].[Products] 
  FOR XML PATH

USE [Northwind]

SELECT [ProductID]

,[ProductName]

,[SupplierID]

,[CategoryID]

,[QuantityPerUnit]

,[UnitPrice]

FROM [Northwind].[dbo].[Products]

FOR XML PATH

Результат

Как видно из приведенного выше снимка экрана, запрос создал XML-файл. Щелкните гиперссылку, чтобы просмотреть файл XML.

SQL FOR XML PATH Пример 2

SQL Server предоставляет ключевое слово ELEMENTS для отображения имен столбцов как вложенных элементов. Позвольте мне использовать это ключевое слово вместе с FOR XML PATH.

USE [Northwind]
GO
SELECT [ProductID]
      ,[ProductName]
      ,supl.CompanyName
      ,cat.CategoryName
      ,[QuantityPerUnit]
      ,[UnitPrice]
  FROM [Northwind].[dbo].[Products] prod
  LEFT JOIN [Northwind].[dbo].[Suppliers] supl ON supl.SupplierID = prod.SupplierID
  LEFT JOIN [Northwind].[dbo].[Categories] cat ON cat.CategoryID = prod.CategoryID
  FOR XML PATH,ELEMENTS

USE [Northwind]

SELECT [ProductID]

,[ProductName]

,supl.CompanyName

,cat.CategoryName

,[QuantityPerUnit]

,[UnitPrice]

FROM [Northwind].[dbo].[Products] prod

LEFT JOIN [Northwind].[dbo].[Suppliers] supl ON supl.SupplierID = prod.SupplierID

LEFT JOIN [Northwind].[dbo].[Categories] cat ON cat.CategoryID = prod.CategoryID

FOR XML PATH,ELEMENTS

Результат

Как видите, на выходе нет никакой разницы. Поскольку режим Path уже выполняет эту операцию

SQL FOR XML PATH Пример 3

Если вы посмотрите на приведенный выше снимок экрана, каждая строка разделена элементом <row> по умолчанию. В этом примере мы покажем вам, как заменить строку по умолчанию настраиваемым элементом.

USE [Northwind]
GO
SELECT [ProductID]
      ,[ProductName]
      ,supl.CompanyName
      ,cat.CategoryName
      ,[QuantityPerUnit]
      ,[UnitPrice]
  FROM [Northwind].[dbo].[Products] prod
  LEFT JOIN [Northwind].[dbo].[Suppliers] supl ON supl.SupplierID = prod.SupplierID
  LEFT JOIN [Northwind].[dbo].[Categories] cat ON cat.CategoryID = prod.CategoryID
  FOR XML PATH('Products')

USE [Northwind]

SELECT [ProductID]

,[ProductName]

,supl.CompanyName

,cat.CategoryName

,[QuantityPerUnit]

,[UnitPrice]

FROM [Northwind].[dbo].[Products] prod

LEFT JOIN [Northwind].[dbo].[Suppliers] supl ON supl.SupplierID = prod.SupplierID

LEFT JOIN [Northwind].[dbo].[Categories] cat ON cat.CategoryID = prod.CategoryID

FOR XML PATH('Products')

Результат

Как вы можете видеть на скриншоте ниже, элемент <row> заменен на <Employees>

SQL FOR XML PATH Пример 4

SQL Server FOR XML PATH позволяет вам создать новый корневой элемент, который будет заключать в него все существующие элементы. Чтобы добиться того же, мы должны использовать ключевое слово ROOT вместе с

USE [Northwind]
GO
SELECT [ProductID]
      ,[ProductName]
      ,supl.CompanyName
      ,cat.CategoryName
      ,[QuantityPerUnit]
      ,[UnitPrice]
  FROM [Northwind].[dbo].[Products] prod
  LEFT JOIN [Northwind].[dbo].[Suppliers] supl ON supl.SupplierID = prod.SupplierID
  LEFT JOIN [Northwind].[dbo].[Categories] cat ON cat.CategoryID = prod.CategoryID
  FOR XML PATH('Products'),ROOT;

USE [Northwind]

SELECT [ProductID]

,[ProductName]

,supl.CompanyName

,cat.CategoryName

,[QuantityPerUnit]

,[UnitPrice]

FROM [Northwind].[dbo].[Products] prod

LEFT JOIN [Northwind].[dbo].[Suppliers] supl ON supl.SupplierID = prod.SupplierID

LEFT JOIN [Northwind].[dbo].[Categories] cat ON cat.CategoryID = prod.CategoryID

FOR XML PATH('Products'),ROOT;

Результат

а файл XML:

SQL FOR XML PATH Пример 5

Если вы посмотрите на приведенный выше снимок экрана, в качестве родительского уровня используется элемент <root>. В этом примере sql for xml path мы изменим это имя элемента по умолчанию.

USE [Northwind]
GO
SELECT [ProductID]
      ,[ProductName]
      ,supl.CompanyName
      ,cat.CategoryName
      ,[QuantityPerUnit]
      ,[UnitPrice]
  FROM [Northwind].[dbo].[Products] prod
  LEFT JOIN [Northwind].[dbo].[Suppliers] supl ON supl.SupplierID = prod.SupplierID
  LEFT JOIN [Northwind].[dbo].[Categories] cat ON cat.CategoryID = prod.CategoryID
  FOR XML PATH('Products'),ROOT('ProductDetails')

USE [Northwind]

SELECT [ProductID]

,[ProductName]

,supl.CompanyName

,cat.CategoryName

,[QuantityPerUnit]

,[UnitPrice]

FROM [Northwind].[dbo].[Products] prod

LEFT JOIN [Northwind].[dbo].[Suppliers] supl ON supl.SupplierID = prod.SupplierID

LEFT JOIN [Northwind].[dbo].[Categories] cat ON cat.CategoryID = prod.CategoryID

FOR XML PATH('Products'),ROOT('ProductDetails')

Результат

Теперь вы можете видеть, что элемент <root> заменен на <ProductDetails>

SQL FOR XML PATH Пример 6

Если имя столбца (или псевдонима) начинается с символа @ и не содержит символа /, то этот столбец будет добавлен как атрибут к элементу строки.

Из приведенного ниже фрагмента кода ProductName и ProductID начинаются с @. Это означает, что они оба добавлены в качестве атрибута в строку «Products».

USE [Northwind]
GO
SELECT [ProductID] AS [@ProductID]
      ,[ProductName] AS [@ProductName]
      ,supl.CompanyName
      ,cat.CategoryName
      ,[QuantityPerUnit]
      ,[UnitPrice]
  FROM [Northwind].[dbo].[Products] prod
  LEFT JOIN [Northwind].[dbo].[Suppliers] supl ON supl.SupplierID = prod.SupplierID
  LEFT JOIN [Northwind].[dbo].[Categories] cat ON cat.CategoryID = prod.CategoryID
  FOR XML PATH('Products'),ROOT('ProductDetails');

USE [Northwind]

SELECT [ProductID] AS [@ProductID]

,[ProductName] AS [@ProductName]

,supl.CompanyName

,cat.CategoryName

,[QuantityPerUnit]

,[UnitPrice]

FROM [Northwind].[dbo].[Products] prod

LEFT JOIN [Northwind].[dbo].[Suppliers] supl ON supl.SupplierID = prod.SupplierID

LEFT JOIN [Northwind].[dbo].[Categories] cat ON cat.CategoryID = prod.CategoryID

FOR XML PATH('Products'),ROOT('ProductDetails');

Результат

и файл XML:

SQL FOR XML PATH Пример 7

Если имя столбца (или имя псевдонима) не начинается с символа @, но содержит символ /, тогда это имя столбца указывает на иерархию.

Как показано ниже для фрагмента кода пути XML, первый и последний не начинаются с @, но содержат символ косой черты. Это означает, что создана новая иерархия под названием Product, и атрибуты First, так и атрибут Last добавлены к Product.

USE [Northwind]
GO
SELECT [ProductID] AS 'Product/@ProductID'
      ,[ProductName] AS 'Product/@ProductName'
      ,supl.CompanyName
      ,cat.CategoryName
      ,[QuantityPerUnit]
      ,[UnitPrice]
  FROM [Northwind].[dbo].[Products] prod
  LEFT JOIN [Northwind].[dbo].[Suppliers] supl ON supl.SupplierID = prod.SupplierID
  LEFT JOIN [Northwind].[dbo].[Categories] cat ON cat.CategoryID = prod.CategoryID
  FOR XML PATH('Products'),ROOT('ProductDetails');

USE [Northwind]

SELECT [ProductID] AS 'Product/@ProductID'

,[ProductName] AS 'Product/@ProductName'

,supl.CompanyName

,cat.CategoryName

,[QuantityPerUnit]

,[UnitPrice]

FROM [Northwind].[dbo].[Products] prod

LEFT JOIN [Northwind].[dbo].[Suppliers] supl ON supl.SupplierID = prod.SupplierID

LEFT JOIN [Northwind].[dbo].[Categories] cat ON cat.CategoryID = prod.CategoryID

FOR XML PATH('Products'),ROOT('ProductDetails');

Результат

и файл XML:

SQL FOR XML PATH Пример 8

Вы также можете использовать подстановочные знаки вместе с Sql Server For XML PATH.

USE [Northwind]
GO
SELECT [ProductID] AS [@ProductID]
      ,[ProductName] "*"
      ,supl.CompanyName "*"
      ,cat.CategoryName
      ,[QuantityPerUnit]
      ,[UnitPrice]
  FROM [Northwind].[dbo].[Products] prod
  LEFT JOIN [Northwind].[dbo].[Suppliers] supl ON supl.SupplierID = prod.SupplierID
  LEFT JOIN [Northwind].[dbo].[Categories] cat ON cat.CategoryID = prod.CategoryID
  FOR XML PATH('Products'),ROOT('ProductDetails'), ELEMENTS XSINIL;

USE [Northwind]

SELECT [ProductID] AS [@ProductID]

,[ProductName] "*"

,supl.CompanyName "*"

,cat.CategoryName

,[QuantityPerUnit]

,[UnitPrice]

FROM [Northwind].[dbo].[Products] prod

LEFT JOIN [Northwind].[dbo].[Suppliers] supl ON supl.SupplierID = prod.SupplierID

LEFT JOIN [Northwind].[dbo].[Categories] cat ON cat.CategoryID = prod.CategoryID

FOR XML PATH('Products'),ROOT('ProductDetails'), ELEMENTS XSINIL;

Результат

и файл XML:

FOR XML PATH Пример 9

В этом примере, мы покажем вам, как XML Path обрабатывает безымянные столбцы.

USE [Northwind]
GO
WITH XMLNAMESPACES('blogadminday.ru' as BlogAdmin)  
SELECT [ProductID] AS [@ProductID]
      ,[ProductName] 
      ,supl.CompanyName 
      ,cat.CategoryName
      ,[QuantityPerUnit]
      ,[UnitPrice]
  FROM [Northwind].[dbo].[Products] prod
  LEFT JOIN [Northwind].[dbo].[Suppliers] supl ON supl.SupplierID = prod.SupplierID
  LEFT JOIN [Northwind].[dbo].[Categories] cat ON cat.CategoryID = prod.CategoryID
  FOR XML PATH('Products'),ROOT('ProductDetails'), ELEMENTS XSINIL;

USE [Northwind]

WITH XMLNAMESPACES('blogadminday.ru' as BlogAdmin)

SELECT [ProductID] AS [@ProductID]

,[ProductName]

,supl.CompanyName

,cat.CategoryName

,[QuantityPerUnit]

,[UnitPrice]

FROM [Northwind].[dbo].[Products] prod

LEFT JOIN [Northwind].[dbo].[Suppliers] supl ON supl.SupplierID = prod.SupplierID

LEFT JOIN [Northwind].[dbo].[Categories] cat ON cat.CategoryID = prod.CategoryID

FOR XML PATH('Products'),ROOT('ProductDetails'), ELEMENTS XSINIL;

Результат

и файл XML:

FOR XML PATH Пример 10

Используя ключевое слово XMLNAMESPACES вместе с SQL For XML Path, вы можете назначить собственное пространство имен XML-файлу. В этом примере мы покажем как это сделать.

USE [Northwind]
GO
WITH XMLNAMESPACES('blogadminday.ru' as BlogAdmin)  
SELECT [ProductID] AS [@ProductID]
      ,[ProductName] 
      ,supl.CompanyName 
      ,cat.CategoryName
      ,[QuantityPerUnit]
      ,[UnitPrice]
  FROM [Northwind].[dbo].[Products] prod
  LEFT JOIN [Northwind].[dbo].[Suppliers] supl ON supl.SupplierID = prod.SupplierID
  LEFT JOIN [Northwind].[dbo].[Categories] cat ON cat.CategoryID = prod.CategoryID
  FOR XML PATH('Products'),ROOT('ProductDetails'), ELEMENTS XSINIL;

USE [Northwind]

WITH XMLNAMESPACES('blogadminday.ru' as BlogAdmin)

SELECT [ProductID] AS [@ProductID]

,[ProductName]

,supl.CompanyName

,cat.CategoryName

,[QuantityPerUnit]

,[UnitPrice]

FROM [Northwind].[dbo].[Products] prod

LEFT JOIN [Northwind].[dbo].[Suppliers] supl ON supl.SupplierID = prod.SupplierID

LEFT JOIN [Northwind].[dbo].[Categories] cat ON cat.CategoryID = prod.CategoryID

FOR XML PATH('Products'),ROOT('ProductDetails'), ELEMENTS XSINIL;

Результат

Вы можете увидеть пространство имен, которое мы использовали в приведенном выше запросе.

FOR XML PATH Пример 11

В этом примере, мы покажем вам, как XML Path обрабатывает вложенные коллекции.

USE [Northwind]
GO 
SELECT [ProductID] AS [@ProductID]
      ,[ProductName] 
      ,supl.CompanyName 
      ,cat.CategoryName
      ,[QuantityPerUnit]
      ,[UnitPrice]
	  ,(
		 SELECT od.[UnitPrice]
				,od.Quantity
		 FROM [Northwind].[dbo].[Order Details] od 
		 WHERE od.ProductID = prod.ProductID 
		 FOR  XML PATH('Order'),TYPE
	   ) AS Sales
  FROM [Northwind].[dbo].[Products] prod
  LEFT JOIN [Northwind].[dbo].[Suppliers] supl ON supl.SupplierID = prod.SupplierID
  LEFT JOIN [Northwind].[dbo].[Categories] cat ON cat.CategoryID = prod.CategoryID
  FOR XML PATH('Products'),ROOT('ProductDetails');

USE [Northwind]

SELECT [ProductID] AS [@ProductID]

,[ProductName]

,supl.CompanyName

,cat.CategoryName

,[QuantityPerUnit]

,[UnitPrice]

SELECT od.[UnitPrice]

,od.Quantity

FROM [Northwind].[dbo].[Order Details] od

WHERE od.ProductID = prod.ProductID

FOR XML PATH('Order'),TYPE

) AS Sales

FROM [Northwind].[dbo].[Products] prod

LEFT JOIN [Northwind].[dbo].[Suppliers] supl ON supl.SupplierID = prod.SupplierID

LEFT JOIN [Northwind].[dbo].[Categories] cat ON cat.CategoryID = prod.CategoryID

FOR XML PATH('Products'),ROOT('ProductDetails');

Результат

и файл XML:

Документация microsoft по использованию FOR XML PATH