Exemplos: usar o modo PATH

Artigo
08/29/2023

Aplica-se a: SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure

Os exemplos a seguir ilustram o uso do modo PATH para gerar XML a partir de uma consulta SELECT. Muitas dessas consultas são especificadas em relação a documentos XML de instruções da fabricação de bicicletas que são armazenados na coluna Instructions da tabela ProductModel.

Especificar uma consulta no modo PATH

Esta consulta especifica um FOR XML em modo PATH.

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

O resultado a seguir é XML centrado em elemento, onde cada valor de coluna no conjunto de linhas resultante é encapsulado em um elemento. Como a cláusula SELECT não especifica um alias para os nomes das colunas, os nomes dos elementos filhos gerados são os mesmos que os nomes das colunas correspondentes na cláusula SELECT. Para cada linha no conjunto de linhas, é adicionada uma marca <row>.

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

O resultado a seguir é o mesmo que o da consulta em modo RAW com a opção ELEMENTS especificada. Ele retorna XML centrado em elemento com um elemento <row> padrão para cada linha no conjunto de resultados.

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

Opcionalmente, você pode especificar o nome do elemento da linha para substituir o <row> padrão. Por exemplo, a consulta a seguir retorna o elemento <ProductModel> para cada linha no conjunto de linhas.

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

O XML resultante terá um nome de elemento de linha especificado.

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

Se você especificar uma cadeia de caracteres de comprimento zero, o elemento de encapsulamento não será produzido.

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

Este é o resultado:

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

Especificar nomes de colunas como XPath

Na consulta a seguir, o nome da coluna ProductModelID especificado começa com '@' e não contém uma barra ('/'). Portanto, um atributo do elemento <row> que tem o valor de coluna correspondente é criado no XML resultante.

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

Este é o resultado:

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

Você pode adicionar um único elemento de nível superior por meio da especificação da opção root em FOR XML.

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

Para gerar uma hierarquia, você pode incluir sintaxe como PATH. Por exemplo, altere o nome da coluna Name para "SomeChild/ModelName", e você obterá um XML com hierarquia, conforme mostrado neste resultado:

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

Além da ID do modelo e do nome do produto, a consulta a seguir recupera os locais de instruções de fabricação do modelo do produto. Como a coluna Instructions é do tipo xml, o método query() do tipo de dados xml é especificado para recuperar o local.

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

Este é o resultado parcial. Como a consulta especifica ManuInstr como o nome da coluna, o XML retornado pelo método query() é envolvido em uma marca <ManuInstr>, conforme mostrado a seguir:

<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>

Na consulta FOR XML anterior, talvez você queira incluir namespaces para os elementos <Root> e <ProductModelData>. Isso pode ser feito primeiro definindo o prefixo para a associação do namespace usando WITH XMLNAMESPACES e usando prefixos na consulta FOR XML. Para obter mais informações, consulte Adicionar Namespaces a consultas com 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

O prefixo MI também é definido em WITH XMLNAMESPACES. Como resultado, o método query() do tipo xml especificado não define o prefixo no prólogo da consulta. Este é o resultado:

<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>

Gerar uma lista de valores usando o modo PATH

Para cada modelo de produto, essa consulta constrói uma lista de valores de IDs de produtos. Para cada ID de produto, a consulta também constrói elementos aninhados <ProductName>, conforme mostrado neste fragmento de XML:

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

Esta é a consulta que produz o XML desejado:

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');

Observe o seguinte na consulta anterior:

O primeiro SELECT aninhado retorna uma lista de ProductIDs usando data() como o nome da coluna. Como a consulta especifica uma cadeia de caracteres vazia como o nome do elemento de linha em FOR XML PATH, nenhum elemento é gerado. Em vez disso, a lista de valores é atribuída ao atributo ProductID .
O segundo SELECT aninhado recupera nomes de produtos no modelo do produto. Ele gera elementos <ProductName> que são retornados encapsulados no elemento <ProductNames>, pois a consulta especifica ProductNames como o nome da coluna.

Este é o resultado parcial:

<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>

A subconsulta que constrói os nomes dos produtos retorna o resultado como uma cadeia de caracteres cuja entidade é criada e, em seguida, é adicionada ao XML. Se você adicionar a diretiva de tipo FOR XML PATH (''), type, a subconsulta retornará o resultado como tipo xml e não ocorrerá nenhum definição de entidade.

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');

Adicionar namespaces no XML resultante

Conforme descrito em Adicionando namespaces usando WITH XMLNAMESPACES, é possível usar WITH XMLNAMESPACES para incluir namespaces em consultas em modo PATH. Por exemplo, nomes especificados na cláusula SELECT incluem prefixos de namespace. A consulta do modo PATH a seguir constrói XML com namespaces.

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

O atributo @xml:lang adicionado ao elemento <English> está definido no namespace xml predefinido.

Este é o resultado:

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

A consulta a seguir é semelhante ao exemplo C, exceto pelo fato de que ela usa WITH XMLNAMESPACES para incluir namespaces no resultado XML. Para obter mais informações, consulte Adicionar Namespaces a consultas com 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');

Este é o resultado:

<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>

Confira também

Usar o modo PATH com FOR XML

Compartilhar via

Exemplos: usar o modo PATH

Especificar uma consulta no modo PATH

Especificar nomes de colunas como XPath

Gerar uma lista de valores usando o modo PATH

Adicionar namespaces no XML resultante

Confira também

Comentários

Recursos adicionais