Примеры. Использование режима PATH

Статья
01/02/2025

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

В следующих примерах показано использование режима PATH при формировании XML из запроса SELECT. Многие из этих запросов являются запросами к XML-документам с инструкциями по производству велосипедов, хранящимся в столбце Instructions таблицы ProductModel.

Указание запроса режима PATH

Этот запрос указывает режим FOR XML PATH.

USE AdventureWorks2022;
GO
SELECT
       ProductModelID,
       Name
FROM Production.ProductModel
WHERE ProductModelID=122 OR ProductModelID=119
FOR XML PATH;
GO

Следующий результат представляет собой элементный XML, в котором значение каждого столбца в итоговом наборе строк образует элемент. SELECT Так как предложение не указывает псевдонимы для имен столбцов, созданные дочерние имена элементов совпадают с соответствующими именами столбцов в предложенииSELECT. Для каждой строки в наборе <row> строк добавляется тег.

<row>
  <ProductModelID>122</ProductModelID>
  <Name>All-Purpose Bike Stand</Name>
</row>
<row>
  <ProductModelID>119</ProductModelID>
  <Name>Bike Wash</Name>
</row>

Следующий результат совпадает с результатом запроса в режиме RAW с указанным параметром ELEMENTS . Он возвращает XML с элементом по умолчанию <row> для каждой строки в результирующем наборе.

USE AdventureWorks2022;
GO
SELECT ProductModelID,
       Name
FROM Production.ProductModel
WHERE ProductModelID=122 OR ProductModelID=119
FOR XML RAW, ELEMENTS;

При необходимости можно указать имя элемента строки, чтобы перезаписать значение по умолчанию <row>. Например, следующий запрос возвращает <ProductModel> элемент для каждой строки в наборе строк.

USE AdventureWorks2022;
GO
SELECT ProductModelID,
       Name
FROM Production.ProductModel
WHERE ProductModelID=122 or ProductModelID=119
FOR XML PATH ('ProductModel');
GO

Итоговый XML-документ будет иметь указанное имя элемента строки.

<ProductModel>
  <ProductModelID>122</ProductModelID>
  <Name>All-Purpose Bike Stand</Name>
</ProductModel>
<ProductModel>
  <ProductModelID>119</ProductModelID>
  <Name>Bike Wash</Name>
</ProductModel>

Если указать строку нулевой длины, элемент оболочки не создается.

USE AdventureWorks2022;
GO
SELECT ProductModelID,
       Name
FROM Production.ProductModel
WHERE ProductModelID=122 OR ProductModelID=119
FOR XML PATH ('');
GO

Результат:

<ProductModelID>122</ProductModelID>
<Name>All-Purpose Bike Stand</Name>
<ProductModelID>119</ProductModelID>
<Name>Bike Wash</Name>

Указание имен столбцов, таких как XPath

В следующем запросе ProductModelID указанное имя столбца начинается с "@" и не содержит знак косой черты ('/). Таким образом, в результирующем XML создается атрибут <row> элемента, имеющего соответствующее значение столбца.

USE AdventureWorks2022;
GO
SELECT ProductModelID AS "@id",
       Name
FROM Production.ProductModel
WHERE ProductModelID=122 OR ProductModelID=119
FOR XML PATH ('ProductModelData');
GO

Результат:

<ProductModelData id="122">
  <Name>All-Purpose Bike Stand</Name>
</ProductModelData>
<ProductModelData id="119">
  <Name>Bike Wash</Name>
</ProductModelData>

Указав в запросе root параметр FOR XML, можно добавить один элемент верхнего уровня.

SELECT ProductModelID AS "@id",
       Name
FROM Production.ProductModel
WHERE ProductModelID=122 or ProductModelID=119
FOR XML PATH ('ProductModelData'), root ('Root');
GO

Для формирования иерархии можно добавить синтаксис, подобный PATH. Например, измените имя столбца Name на "SomeChild/ModelName", и вы получите XML с иерархией, как показано в этом результате:

<Root>
  <ProductModelData id="122">
    <SomeChild>
      <ModelName>All-Purpose Bike Stand</ModelName>
    </SomeChild>
  </ProductModelData>
  <ProductModelData id="119">
    <SomeChild>
      <ModelName>Bike Wash</ModelName>
    </SomeChild>
  </ProductModelData>
</Root>

Следующий запрос извлекает не только код модели продукта и его имя, но и расположения производственных инструкций для модели продукции. Так как столбец "Инструкции" имеет тип XML , query() для получения расположения указывается метод xml-данных .

SELECT ProductModelID AS "@id",
       Name,
       Instructions.query('declare namespace MI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
                /MI:root/MI:Location
              ') AS ManuInstr
FROM Production.ProductModel
WHERE ProductModelID = 7
FOR XML PATH ('ProductModelData'), root ('Root');
GO

Частичный результат. Так как запрос указывает ManuInstr в качестве имени столбца, XML, возвращаемый query() методом, упаковывается в <ManuInstr> тег, как показано в следующем примере:

<Root>
  <ProductModelData id="7">
    <Name>HL Touring Frame</Name>
    <ManuInstr>
      <MI:Location xmlns:MI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions"
        <MI:step>...</MI:step>...
      </MI:Location>
      ...
    </ManuInstr>
  </ProductModelData>
</Root>

В предыдущем запросе FOR XML может потребоваться включить пространства имен для <Root> элементов и <ProductModelData> элементов. Для этого следовало сначала определить префикс пространства имен, создать привязку с помощью предложения WITH XMLNAMESPACES и затем использовать префиксы в запросе FOR XML. Дополнительные сведения с. в разделе Добавление пространств имен в запросы с помощью WITH XMLNAMESPACES.

USE AdventureWorks2022;
GO
WITH XMLNAMESPACES (
   'uri1' AS ns1,
   'uri2' AS ns2,
   'https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions' as MI)
SELECT ProductModelID AS "ns1:ProductModelID",
       Name           AS "ns1:Name",
       Instructions.query('
                /MI:root/MI:Location
              ')
FROM Production.ProductModel
WHERE ProductModelID=7
FOR XML PATH ('ns2:ProductInfo'), root('ns1:root');
GO

Префикс MI также определен в элементе WITH XMLNAMESPACES. В результате query() метод указанного типа XML не определяет префикс в прологе запроса. Результат:

<ns1:root xmlns:MI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions" xmlns="uri2" xmlns:ns2="uri2" xmlns:ns1="uri1">
  <ns2:ProductInfo>
    <ns1:ProductModelID>7</ns1:ProductModelID>
    <ns1:Name>HL Touring Frame</ns1:Name>
    <MI:Location xmlns:MI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions" LaborHours="2.5" LotSize="100" MachineHours="3" SetupHours="0.5" LocationID="10" xmlns="">
    <MI:step>
      Insert <MI:material>aluminum sheet MS-2341</MI:material> into the <MI:tool>T-85A framing tool</MI:tool>.
    </MI:step>
    ...
    </MI:Location>
    ...
  </ns2:ProductInfo>
</ns1:root>

Создание списка значений с помощью режима PATH

Данный запрос строит список значений кодов продуктов для каждой модели продукции. Для каждого идентификатора продукта запрос также создает <ProductName> вложенные элементы, как показано в этом фрагменте XML:

<ProductModelData ProductModelID="7" ProductModelName="..." ProductIDs="product id list in the product model">
  <ProductName>...</ProductName>
  <ProductName>...</ProductName>
  ...
</ProductModelData>

Это запрос, создающий желаемый XML:

USE AdventureWorks2022;
GO
SELECT ProductModelID     AS "@ProductModelID",
       Name               AS "@ProductModelName",
      (SELECT ProductID AS "data()"
       FROM   Production.Product
       WHERE  Production.Product.ProductModelID =
              Production.ProductModel.ProductModelID
       FOR XML PATH ('')) AS "@ProductIDs",
       (SELECT Name AS "ProductName"
       FROM   Production.Product
       WHERE  Production.Product.ProductModelID =
              Production.ProductModel.ProductModelID
        FOR XML PATH ('')) AS "ProductNames"
FROM   Production.ProductModel
WHERE  ProductModelID= 7 or ProductModelID=9
FOR XML PATH('ProductModelData');

Обратите внимание на следующие данные из предыдущего запроса:

Первая вложенная инструкция SELECT возвращает список ProductID, для чего использует значение data() в качестве имени столбца. Поскольку в режиме FOR XML PATHзапрос указывает для имени элемента строки пустую строку, формирование элемента не происходит. Вместо этого список значений назначается атрибуту ProductID .
Вторая вложенная инструкция SELECT извлекает названия продуктов для модели продукта. Он создает <ProductName> элементы, возвращаемые в <ProductNames> элемент, так как запрос указывает ProductNames в качестве имени столбца.

Частичный результат:

<ProductModelData PId="7" ProductModelName="HL Touring Frame" ProductIDs="885 887 ...">
  <ProductNames>
    <ProductName>HL Touring Frame - Yellow, 60</ProductName>
    <ProductName>HL Touring Frame - Yellow, 46</ProductName>
  </ProductNames>
  ...
</ProductModelData>
<ProductModelData PId="9" ProductModelName="LL Road Frame" ProductIDs="722 723 724 ...">
  <ProductNames>
    <ProductName>LL Road Frame - Black, 58</ProductName>
    <ProductName>LL Road Frame - Black, 60</ProductName>
    <ProductName>LL Road Frame - Black, 62</ProductName>
    ...
  </ProductNames>
</ProductModelData>

Вложенный запрос, формирующий имена продуктов, возвращает результат в виде строки, которая преобразуется в сущность и затем добавляется в XML-документ. Если добавить директиву типа FOR XML PATH (''), type, вложенный запрос возвращает результат как тип xml , а преобразования в сущность не происходит.

USE AdventureWorks2022;
GO
SELECT ProductModelID AS "@ProductModelID",
      Name AS "@ProductModelName",
      (SELECT ProductID AS "data()"
       FROM   Production.Product
       WHERE  Production.Product.ProductModelID =
              Production.ProductModel.ProductModelID
       FOR XML PATH ('')
       ) AS "@ProductIDs",
       (
       SELECT Name AS "ProductName"
       FROM   Production.Product
       WHERE  Production.Product.ProductModelID =
              Production.ProductModel.ProductModelID
       FOR XML PATH (''), type
       ) AS "ProductNames"

FROM Production.ProductModel
WHERE ProductModelID= 7 OR ProductModelID=9
FOR XML PATH('ProductModelData');

Добавление пространств имен в результирующем XML-коде

Как описано в разделе Добавление пространств имен с помощью предложения WITH XMLNAMESPACES, предложение WITH XMLNAMESPACES может использоваться для включения пространств имен в запросы в режиме PATH. Например, имена, указанные в предложении SELECT, имеют префиксы пространства имен. Следующий запрос в режиме PATH строит XML-документ с пространствами имен.

SELECT 'en'    as "English/@xml:lang",
       'food'  as "English",
       'ger'   as "German/@xml:lang",
       'Essen' as "German"
FOR XML PATH ('Translation')
GO

Атрибут, @xml:lang добавленный в <English> элемент, определяется в предопределенном пространстве имен XML.

Результат:

<Translation>
  <English xml:lang="en">food</English>
  <German xml:lang="ger">Essen</German>
</Translation>

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

USE AdventureWorks2022;
GO
WITH XMLNAMESPACES ('uri1' AS ns1,  DEFAULT 'uri2')
SELECT ProductModelID AS "@ns1:ProductModelID",
      Name AS "@ns1:ProductModelName",
      (SELECT ProductID AS "data()"
       FROM   Production.Product
       WHERE  Production.Product.ProductModelID =
              Production.ProductModel.ProductModelID
       FOR XML PATH ('')
       ) AS "@ns1:ProductIDs",
       (
       SELECT ProductID AS "@ns1:ProductID",
              Name AS "@ns1:ProductName"
       FROM   Production.Product
       WHERE  Production.Product.ProductModelID =
              Production.ProductModel.ProductModelID
       FOR XML PATH , type
       ) AS "ns1:ProductNames"
FROM Production.ProductModel
WHERE ProductModelID= 7 OR ProductModelID=9
FOR XML PATH('ProductModelData'), root('root');

Результат:

<root xmlns="uri2"
  xmlns:ns1="uri1">
  <ProductModelData ns1:ProductModelID="7" ns1:ProductModelName="HL Touring Frame" ns1:ProductIDs="885 887 888 889 890 891 892 893">
    <ns1:ProductNames>
      <row xmlns="uri2" xmlns:ns1="uri1" ns1:ProductID="885" ns1:ProductName="HL Touring Frame - Yellow, 60" />
      <row xmlns="uri2" xmlns:ns1="uri1" ns1:ProductID="887" ns1:ProductName="HL Touring Frame - Yellow, 46" />
      ...
    </ns1:ProductNames>
  </ProductModelData>
  <ProductModelData ns1:ProductModelID="9" ns1:ProductModelName="LL Road Frame" ns1:ProductIDs="722 723 724 725 726 727 728 729 730 736 737 738">
    <ns1:ProductNames>
      <row xmlns="uri2" xmlns:ns1="uri1" ns1:ProductID="722" ns1:ProductName="LL Road Frame - Black, 58" />
      ...
    </ns1:ProductNames>
  </ProductModelData>
</root>

См. также

Использование режима PATH совместно с FOR XML

Поделиться через

Примеры. Использование режима PATH

Указание запроса режима PATH

Указание имен столбцов, таких как XPath

Создание списка значений с помощью режима PATH

Добавление пространств имен в результирующем XML-коде

См. также

Обратная связь

Дополнительные ресурсы