XML стал общепринятой формой представления и обмена данными в современной информационной эпохе. SQL Server ввел XML-центрические возможности в SQL Server 2000. Эта функциональность была расширена в последующих версиях. Одним из аспектов работы с XML является создание XML из реляционных данных, что достигается с помощью оператора FOR XML в SQL Server.

Оператор FOR XML

Оператор FOR XML – это мощный инструмент в SQL Server, который позволяет представлять результаты запроса в формате XML. Он может быть добавлен в конец стандартного оператора SELECT для преобразования вывода в формат XML. Оператор FOR XML имеет четыре режима: RAW, AUTO, EXPLICIT и PATH.

Режим RAW

Режим RAW является самым простым и прямолинейным способом создания XML с помощью оператора FOR XML. Он генерирует элемент с именем “row” для каждой строки результирующего набора, причем каждый столбец строки представлен в виде атрибута элемента row. Вот пример:

SELECT e.EmployeeID,
	 FirstName,
	 LastName,
	 AddressLine1,
	 City,
	 sp.Name as 'StateOrProvince',
	 PostalCode
  FROM HumanResources.Employee e(NOLOCK)
  JOIN Person.Contact c (NOLOCK)
    ON e.ContactID = c.ContactID
  JOIN HumanResources.EmployeeAddress ea(NOLOCK)
    ON e.EmployeeID = ea.EmployeeID
  JOIN Person.Address a (NOLOCK)
    ON ea.AddressID = a.AddressID
  JOIN Person.StateProvince sp (NOLOCK)
    ON a.StateProvinceID = sp.StateProvinceID
FOR XML RAW

Результатом будет строка Unicode, содержащая элементы row, каждый из которых представляет строку из результирующего набора. Каждый столбец результирующего набора будет представлен в виде атрибута элемента row.

Режим AUTO

Режим AUTO предоставляет уровень иерархического значения для вывода XML. Вместо того, чтобы иметь столбцы в качестве атрибутов или подэлементов на одном уровне, столбцы из разных таблиц становятся вложенными элементами в соответствии с порядком столбцов в операторе SELECT. Вот пример:

SELECT e.EmployeeID,
	 FirstName,
	 LastName,
	 AddressLine1,
	 City,
	 sp.Name as 'StateOrProvince',
	 PostalCode
  FROM HumanResources.Employee e(NOLOCK)
  JOIN Person.Contact c (NOLOCK)
    ON e.ContactID = c.ContactID
  JOIN HumanResources.EmployeeAddress ea(NOLOCK)
    ON e.EmployeeID = ea.EmployeeID
  JOIN Person.Address a (NOLOCK)
    ON ea.AddressID = a.AddressID
  JOIN Person.StateProvince sp (NOLOCK)
    ON a.StateProvinceID = sp.StateProvinceID
FOR XML AUTO, ROOT ('Employees')

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

Режим EXPLICIT

Режим EXPLICIT предоставляет больший контроль над стилизацией результирующего XML. Он позволяет указать иерархию и вложенность элементов с помощью запроса UNION ALL. Вот пример:

SELECT 1            AS Tag,
       NULL         AS Parent,
       e.EmployeeID AS [Employee!1!EmployeeID],
       c.FirstName  AS [Employee!1!FirstName],
       c.LastName   AS [Employee!1!LastName],
       NULL         AS [Address!2!AddressLine1],
       NULL         AS [Address!2!AddressLine2],
       NULL         AS [Address!2!City],
       NULL         AS [Address!2!StateOrProvince],
       NULL         AS [Address!2!PostalCode]
 FROM  HumanResources.Employee e(NOLOCK)
 JOIN  Person.Contact c (NOLOCK)
   ON  e.ContactID = c.ContactID
UNION ALL	
SELECT 2            AS Tag,
       1            AS Parent,
       e.EmployeeID AS [Employee!1!EmployeeID],
       NULL         AS [Employee!1!FirstName],
       NULL         AS [Employee!1!LastName],
       AddressLine1 AS [Address!2!AddressLine1],
       AddressLine2 AS [Address!2!AddressLine2],
       City         AS [Address!2!City],
       StateProvinceCode AS [Address!2!StateOrProvince],
       PostalCode   AS [Address!2!PostalCode]
 FROM  HumanResources.Employee e(NOLOCK)
 JOIN  HumanResources.EmployeeAddress ea(NOLOCK)
   ON  e.EmployeeID = ea.EmployeeID
 JOIN  Person.Address a (NOLOCK)
   ON  ea.AddressID = a.AddressID  
 JOIN  Person.StateProvince s (NOLOCK)
   ON  a.StateProvinceID = s.StateProvinceID
ORDER BY e.EmployeeID, Tag
FOR XML EXPLICIT, ROOT('Employees')

Режим EXPLICIT позволяет явно определить иерархию и вложенность элементов с помощью столбцов Tag и Parent в запросе UNION ALL. Он обеспечивает детальный контроль над структурой вывода XML.

Режим PATH

Режим PATH является самым новым и гибким способом генерации XML в SQL Server. Он позволяет указать иерархию и вложенность элементов с помощью выражений XPath в операторе SELECT. Вот пример:

SELECT Employee.EmployeeID  AS '@EmployeeID',
       Contact.FirstName    AS 'FirstName',
       Contact.LastName     AS 'LastName',
       AddressLine1         AS 'Address/AddressLine1',
       AddressLine2         AS 'Address/AddressLine2',
       City                 AS 'Address/City',
       StateProvinceCode    AS 'Address/StateOrProvince',
       PostalCode           AS 'Address/PostalCode'
 FROM  HumanResources.Employee Employee(NOLOCK)
 JOIN  Person.Contact Contact (NOLOCK)
   ON  Employee.ContactID = Contact.ContactID
 JOIN  HumanResources.EmployeeAddress ea(NOLOCK)
   ON  Employee.EmployeeID = ea.EmployeeID
 JOIN  Person.Address Address (NOLOCK)
   ON  ea.AddressID = Address.AddressID  
 JOIN  Person.StateProvince s (NOLOCK)
   ON  Address.StateProvinceID = s.StateProvinceID
FOR XML PATH ('Employee'), ROOT('Employees')

Режим PATH позволяет указать иерархию и вложенность элементов с помощью выражений XPath в псевдонимах столбцов. Он предоставляет более простой синтаксис по сравнению с режимом EXPLICIT и предлагает больший контроль над структурой вывода XML.

Заключение

Оператор FOR XML в SQL Server – это мощный инструмент для создания XML из реляционных данных. Четыре режима: RAW, AUTO, EXPLICIT и PATH, предоставляют разные уровни сложности и гибкости в стилизации вывода XML. Понимание этих режимов и их синтаксиса поможет вам эффективно преобразовывать результаты запросов в формат XML.