Формирование XML-кода с вложенными запросами FOR XML
В следующем примере к таблице Production.Product выполняется запрос, чтобы получить значения ListPrice и StandardCost указанного продукта. Чтобы сделать пример более информативным, обе цены возвращаются как элемент <Price> и у каждого элемента <Price> имеется атрибут PriceType.
Пример
Это прогнозируемая форма XML:
<xsd:schema xmlns:schema="urn:schemas-microsoft-com:sql:SqlRowSet2" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="https://schemas.microsoft.com/sqlserver/2004/sqltypes" targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet2" elementFormDefault="qualified">
<xsd:import namespace="https://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="https://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
<xsd:element name="Production.Product" type="xsd:anyType" />
</xsd:schema>
<Production.Product xmlns="urn:schemas-microsoft-com:sql:SqlRowSet2" ProductID="520">
<Price PriceType="ListPrice">133.34</Price>
<Price PriceType="StandardCost">98.77</Price>
</Production.Product>
Это вложенный запрос FOR XML:
USE AdventureWorks2008R2;
GO
SELECT Product.ProductID,
(SELECT 'ListPrice' AS PriceType,
CAST(CAST(ListPrice AS NVARCHAR(40)) AS XML)
FROM Production.Product Price
WHERE Price.ProductID=Product.ProductID
FOR XML AUTO, TYPE),
(SELECT 'StandardCost' AS PriceType,
CAST(CAST(StandardCost as NVARCHAR(40)) AS XML)
FROM Production.Product Price
WHERE Price.ProductID=Product.ProductID
FOR XML AUTO, TYPE)
FROM Production.Product
WHERE ProductID = 520
for XML AUTO, TYPE, XMLSCHEMA;
В приведенном запросе обратите внимание на следующие моменты.
Внешняя инструкция SELECT строит элемент <Product>, который имеет атрибут ProductID и два дочерних элемента <Price>.
Две внутренние инструкции SELECT создают два элемента <Price>, каждый с атрибутом PriceType, а также XML, который возвращает цену продукта.
Директива XMLSCHEMA во внешней инструкции SELECT создает встроенную XSD-схему, которая описывает форму результирующего XML.
Чтобы сделать запрос более информативным, можно составить запрос FOR XML, а затем подготовить запрос XQuery к результату для переформирования XML, как показано в следующем запросе:
SELECT ProductID,
( SELECT p2.ListPrice, p2.StandardCost
FROM Production.Product p2
WHERE Product.ProductID = p2.ProductID
FOR XML AUTO, ELEMENTS XSINIL, type ).query('
for $p in /p2/*
return
<Price PriceType = "{local-name($p)}">
{ data($p) }
</Price>
')
FROM Production.Product
WHERE ProductID = 520
FOR XML AUTO, TYPE;
В предыдущем примере метод query() типа данных xml используется, чтобы запросить XML, возвращаемый внутренним запросом FOR XML, и построить прогнозируемый результат.
Результат:
<Production.Product ProductID="520">
<Price PriceType="ListPrice">133.3400</Price>
<Price PriceType="StandardCost">98.7700</Price>
</Production.Product>