Compartilhar via

  • Artigo

Exemplos: Usando modo PATH

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 Production.ProductModel. Para obter mais informações sobre as instruções XML, consulte Representação de tipo de dados xml no banco de dados do AdventureWorks.

Especificando uma consulta em modo PATH

Esta consulta especifica um FOR XML em modo PATH.

SELECT 
       ProductModelID,
       Name
FROM Production.ProductModel
WHERE ProductModelID=122 or ProductModelID=119
FOR XML PATH
go

O resultado a seguir é XML centrado em elemento em que cada valor de coluna no conjunto de linhas resultante é envolvido em um elemento. Como a cláusula SELECT não especifica nenhum alias para os nomes das colunas, os nomes dos elementos filho gerados são os mesmos que os nomes das colunas correspondentes nas cláusulas 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. Ela retorna XML centrado em elemento com um elemento <row> padrão para cada linha no conjunto de resultados.

SELECT ProductModelID,
       Name
FROM Production.ProductModel
WHERE ProductModelID=122 or ProductModelID=119
FOR XML RAW, ELEMENTS

Opcionalmente, é possível especificar o nome do elemento de linha para substituir a <row>padrão. Por exemplo, a consulta a seguir retorna o elemento <ProductModel> para cada linha no conjunto de linhas.

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.

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>

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

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 especificando a 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á 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 é de tipo xml, o método query() de 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() é encapsulado em uma marca <ManuInstr> conforme mostrado no seguinte:

<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, você pode desejar 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 Adicionando namespaces usando WITH XMLNAMESPACES.

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

Observe que o prefixo MI também está 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" >
       <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>

Gerando uma lista de valores com 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:

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 de <ProductName> que são retornados encapsulados no elemento <ProductNames>, porque a consulta especifica ProductNames como o nome da coluna.

Este é o resultado parcial:

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

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

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

Adicionando 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. O 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 que ela usa WITH XMLNAMESPACES para incluir namespaces no resultado XML. Para obter mais informações, consulte Adicionando namespaces usando WITH XMLNAMESPACES.

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>

Consulte também

Conceitos

Usando o modo PATH