XQueries que envolvem hierarquias

Artigo
05/23/2023

Aplica-se a:SQL Server

A maioria das colunas de tipo xml no banco de dados AdventureWorks são documentos semiestruturados. Portanto, os documentos armazenados em cada linha podem parecer diferentes. Os exemplos de consulta neste tópico ilustram como extrair informações desses vários documentos.

Exemplos

a. Nos documentos de instruções de fabricação, recuperar os locais de centro de trabalho junto com a primeira etapa de fabricação nesses locais

Para o modelo de produto 7, a consulta constrói XML que inclui o <ManuInstr> elemento, com atributos ProductModelID e ProductModelName e um ou mais <Location> elementos filho.

Cada <Location> elemento tem seu próprio conjunto de atributos e um <step> elemento filho. Esse <step> elemento filho é a primeira etapa de fabricação no local do centro de trabalho.

SELECT Instructions.query('  
     declare namespace AWMI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";  
   \<ManuInstr  ProdModelID = "{sql:column("Production.ProductModel.ProductModelID") }"   
                ProductModelName = "{ sql:column("Production.ProductModel.Name") }" >  
            {   
              for $wc in //AWMI:root/AWMI:Location  
              return  
                <Location>  
                 {$wc/@* }  
                 <step1> { string( ($wc//AWMI:step)[1] ) } </step1>  
                </Location>  
            }  
          </ManuInstr>  
') as Result  
FROM Production.ProductModel  
WHERE ProductModelID=7

Observe o seguinte na consulta anterior:

A palavra-chave namespace no Prólogo XQuery define um prefixo de namespace. Esse prefixo é usado mais tarde no corpo da consulta.
As chaves agem como tokens de troca de contexto,{) e (}, e são usadas para alternar a consulta da construção XML para avaliação de consulta.
O sql:column() é usado para incluir um valor relacional no XML que está sendo construído.
Ao construir o <Location> elemento, $wc/@* recupera todos os atributos de local do centro de trabalho.
A função string() retorna o valor da cadeia de caracteres do <step> elemento .

Este é um resultado parcial:

<ManuInstr ProdModelID="7" ProductModelName="HL Touring Frame">  
   <Location LocationID="10" SetupHours="0.5"   
            MachineHours="3" LaborHours="2.5" LotSize="100">  
     <step1>Insert aluminum sheet MS-2341 into the T-85A   
             framing tool.</step1>  
   </Location>  
   <Location LocationID="20" SetupHours="0.15"   
            MachineHours="2" LaborHours="1.75" LotSize="1">  
      <step1>Assemble all frame components following   
             blueprint 1299.</step1>  
   </Location>  
...  
</ManuInstr>

B. Localizar todos os números de telefone na coluna AdditionalContactInfo

A consulta a seguir recupera números de telefone adicionais para um contato específico do cliente pesquisando o elemento em toda a <telephoneNumber> hierarquia. Como o <telephoneNumber> elemento pode aparecer em qualquer lugar na hierarquia, a consulta usa o operador descendente e self (//) na pesquisa.

SELECT AdditionalContactInfo.query('  
 declare namespace ci="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";  
 declare namespace act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";  
for $ph in /ci:AdditionalContactInfo//act:telephoneNumber  
   return  
      $ph/act:number  
') as x  
FROM  Person.Contact  
WHERE ContactID = 1

Este é o resultado:

\<act:number   
  xmlns:act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes">  
  111-111-1111  
\</act:number>  
\<act:number   
  xmlns:act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes">  
  112-111-1111  
\</act:number>

Para recuperar apenas os números de telefone de nível superior, especificamente os <telephoneNumber> elementos filho de <AdditionalContactInfo>, a expressão FOR na consulta muda para

for $ph in /ci:AdditionalContactInfo/act:telephoneNumber.

Confira também

Fundamentos de XQuery
Construção XML (XQuery)
Dados XML (SQL Server)

Compartilhar via