OPENXML (Transact-SQL)

Aplica-se a: simSQL Server (todas as versões compatíveis) SimBanco de Dados SQL do Azure SimInstância Gerenciada do Azure SQL

OPENXML fornece uma exibição de conjunto de linhas em um documento XML. Como OPENXML é um provedor de conjunto de linhas, OPENROWSET pode ser usado em instruções Transact-SQL nas quais os provedores de conjunto de linhas, como uma tabela, exibição ou a função OPENROWSET, podem aparecer.

Ícone de link do artigo Convenções de sintaxe Transact-SQL

Sintaxe

OPENXML( idoc int [ in] , rowpattern nvarchar [ in ] , [ flags byte [ in ] ] )   
[ WITH ( SchemaDeclaration | TableName ) ]  

Observação

Para ver a sintaxe do Transact-SQL para o SQL Server 2014 e versões anteriores, confira a Documentação das versões anteriores.

Argumentos

idoc
É o indentificador de documento da representação interna de um documento XML. A representação interna de um documento XML é criada chamando sp_xml_preparedocument.

rowpattern
O padrão XPath é usado para identificar os nós a ser processados como linhas. Os nós vêm do documento XML cujo identificador é transmitido no parâmetro idoc.

sinalizadores
Indica o mapeamento usado entre os dados XML e o conjunto de linhas relacional, e como a coluna de derramamento é preenchida. flags é um parâmetro de entrada opcional e pode ser um dos valores a seguir.

Valor do byte Descrição
0 O padrão será o mapeamento centrado em atributo.
1 Use o mapeamento centrado em atributo. Pode ser combinado com XML_ELEMENTS. Neste caso, o mapeamento centrado em atributo é aplicado primeiro. Em seguida, o mapeamento centrado em elemento é aplicado às colunas restantes.
2 Use o mapeamento centrado em elemento. Pode ser combinado com XML_ATTRIBUTES. Neste caso, o mapeamento centrado em atributo é aplicado primeiro. Em seguida, o mapeamento centrado em elemento é aplicado às colunas restantes.
8 Pode ser combinado (OR lógico) com XML_ATTRIBUTES ou XML_ELEMENTS. No contexto de recuperação, esse sinalizador indica que os dados consumidos não podem ser copiados para a propriedade de estouro @mp:xmltex.

SchemaDeclaration
É a definição de esquema no formato: ColNameColType [ColPattern | MetaProperty] [,ColNameColType [ColPattern | MetaProperty]...]

ColName
É o nome de coluna no conjunto de linhas.

ColType
É o tipo de dados do SQL Server da coluna no conjunto de linhas. Se os tipos de coluna forem diferentes do tipo de dados xml subjacente do atributo, ocorrerá coerção de tipo.

ColPattern
É um padrão geral XPath opcional que descreve como os nós XML devem ser mapeados para as colunas. Se ColPattern não for especificado, ocorrerá o mapeamento padrão (mapeamento centrado em atributo ou centrado em elemento de acordo com a especificação de flags).

O padrão XPath especificado como ColPattern é usado para especificar a natureza especial do mapeamento (para centrado em atributo e centrado em elemento) que substitui ou melhora o mapeamento padrão indicado por flags.

O padrão XPath geral especificado como ColPattern também é compatível com metapropriedades.

MetaProperty
É uma das metapropriedades fornecidas por OPENXML. Se MetaProperty for especificado, a coluna conterá as informações fornecidas pela metapropriedade. As metapropriedades permitem extrair informações (como a posição relativa e informações de namespace) sobre nós XML. Essas metapropriedades fornecem mais informações que as visíveis na representação textual.

TableName
É o nome da tabela que poderá ser fornecido (em vez de SchemaDeclaration) se já existir uma tabela com o esquema desejado e não for necessário nenhum padrão de coluna.

Comentários

A cláusula WITH fornece um formato de conjunto de linhas (e as informações de mapeamento adicionais, conforme o necessário) usando SchemaDeclaration ou especificando um TableName existente. Se a cláusula WITH opcional não for especificada, os resultados serão retornados em um formato de tabela de borda. As tabelas de borda representam a estrutura de documento XML refinada (como nomes de elemento/atributo, a hierarquia de documento, os namespaces e PIS e assim por diante) em uma única tabela.

A tabela a seguir descreve a estrutura da tabela de borda.

Nome da coluna Tipo de dados Descrição
id bigint É a ID exclusiva do nó do documento.

O elemento raiz tem o valor de ID igual a 0. Os valores negativos da ID são reservados.
parentid bigint Identifica o pai do nó. O pai identificado por essa ID não é necessariamente o elemento pai, mas depende do NodeType do nó cujo pai é identificado por essa ID. Por exemplo, se o nó for um nó de texto, o pai poderá ser um nó de atributo.

Se o nó estiver no nível superior no documento XML, seu ParentID será NULL.
nodetype int Identifica o tipo de nó. É um inteiro que corresponde à numeração de tipo de nó XML DOM.

Os tipos de nó são:

1 = Nó de elemento

2 = Nó de atributo

3 = Nó de texto
localname nvarchar Fornece o nome local do elemento ou do atributo. Será NULL se o objeto DOM não tiver um nome.
prefixo nvarchar É o prefixo do namespace do nome do nó.
namespaceuri nvarchar É o URI do namespace do nó. Se o valor for NULL, nenhum namespace estará presente.
datatype nvarchar É o tipo real de dados da linha de atributo ou de elemento, caso contrário será NULL. O tipo de dados é deduzido do DTD embutido ou do esquema embutido.
prev bigint É a ID de XML do elemento irmão anterior. Será NULL se não houver nenhum irmão anterior direto.
text ntext Contém o valor de atributo ou o conteúdo do elemento na forma de texto (ou será NULL se a entrada da tabela de borda não exigir um valor).

Exemplos

a. Usando uma instrução SELECT simples com OPENXML

O exemplo a seguir cria uma representação interna da imagem XML usando sp_xml_preparedocument. Uma instrução SELECT que usa um provedor de conjunto de linhas OPENXML é executada na representação interna do documento XML.

O valor de flag é definido como 1. Esse valor indica um mapeamento centrado em atributo. Portanto, os atributos XML mapeiam para as colunas no conjunto de linhas. O rowpattern especificado como /ROOT/Customer identifica os nós de <Customers> a serem processados.

O parâmetro opcional ColPattern (padrão de coluna) não é especificado porque o nome da coluna corresponde aos nomes de atributo XML.

O provedor de conjunto de linhas OPENXML cria um conjunto de linhas de duas colunas (CustomerID e ContactName) das quais a instrução SELECT recupera as colunas necessárias (nesse caso, todas as colunas).

DECLARE @idoc INT, @doc VARCHAR(1000);  
SET @doc ='  
<ROOT>  
<Customer CustomerID="VINET" ContactName="Paul Henriot">  
   <Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">  
      <OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>  
      <OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>  
   </Order>  
</Customer>  
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">  
   <Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">  
      <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>  
   </Order>  
</Customer>  
</ROOT>';  
--Create an internal representation of the XML document.  
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;  
-- Execute a SELECT statement that uses the OPENXML rowset provider.  
SELECT    *  
FROM       OPENXML (@idoc, '/ROOT/Customer',1)  
            WITH (CustomerID  VARCHAR(10),  
                  ContactName VARCHAR(20));  

Este é o conjunto de resultados.

CustomerID ContactName            
---------- --------------------   
VINET      Paul Henriot  
LILAS      Carlos Gonzlez  

Se a mesma instrução SELECT for executada com flags definido como 2, indicando um mapeamento centrado em elemento, os valores de CustomerID e ContactName para ambos os clientes no documento XML serão retornados como NULL, porque não há nenhum elemento chamado CustomerID ou ContactName no documento XML.

Este é o conjunto de resultados.

CustomerID ContactName  
---------- -----------  
NULL       NULL  
NULL       NULL  

B. Especificando ColPattern para mapeamento entre colunas e atributos XML

A consulta a seguir retorna a ID de cliente, a data do pedido, a ID de produto e atributos de quantidade do documento XML. O rowpattern identifica os elementos <OrderDetails>. ProductID e Quantity são os atributos do elemento <OrderDetails>. Entretanto, OrderID, CustomerIDe OrderDate são os atributos do elemento de pai (<Orders>).

O ColPattern opcional é especificado para os seguintes mapeamentos:

  • A OrderID, CustomerID e OrderDate no conjunto de linhas são mapeadas para os atributos do pai dos nós identificados por rowpattern no documento XML.

  • A coluna ProdID no conjunto de linhas é mapeada para o atributo ProductID, e a coluna Qty no conjunto de linhas é mapeada para o atributo Quantity dos nós identificados em rowpattern.

Embora o mapeamento centrado em elemento seja especificado pelo parâmetro flags, o mapeamento especificado em ColPattern substitui esse mapeamento.

DECLARE @idoc INT, @doc VARCHAR(1000);   
SET @doc ='  
<ROOT>  
<Customer CustomerID="VINET" ContactName="Paul Henriot">  
   <Order OrderID="10248" CustomerID="VINET" EmployeeID="5"   
           OrderDate="1996-07-04T00:00:00">  
      <OrderDetail ProductID="11" Quantity="12"/>  
      <OrderDetail ProductID="42" Quantity="10"/>  
   </Order>  
</Customer>  
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">v  
   <Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"   
           OrderDate="1996-08-16T00:00:00">  
      <OrderDetail ProductID="72" Quantity="3"/>  
   </Order>  
</Customer>  
</ROOT>';   
  
--Create an internal representation of the XML document.  
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;   
  
-- SELECT stmt using OPENXML rowset provider  
SELECT *  
FROM   OPENXML (@idoc, '/ROOT/Customer/Order/OrderDetail',2)   
         WITH (OrderID       int         '../@OrderID',
               CustomerID  varchar(10) '../@CustomerID',
               OrderDate   datetime    '../@OrderDate',
               ProdID      int         '@ProductID',
               Qty         int         '@Quantity');
  

Este é o conjunto de resultados.

OrderID CustomerID           OrderDate                 ProdID    Qty  
------------------------------------------------------------------------  
10248      VINET       1996-07-04 00:00:00.000   11      12  
10248      VINET       1996-07-04 00:00:00.000   42      10  
10283      LILAS       1996-08-16 00:00:00.000   72      3  

C. Obtendo resultados em um formato de tabela de borda

O documento XML de exemplo a seguir consiste nos elementos <Customers>, <Orders> e <Order_0020_Details>. Primeiro, sp_xml_preparedocument é chamado para obter um identificador de documento. Esse identificador de documento é passado para o OPENXML.

Na instrução OPENXML, o rowpattern (/ROOT/Customers) identifica os nós de <Customers> a serem processados. Como a cláusula WITH não é fornecida, OPENXML retorna o conjunto de linhas em um formato de tabela de borda.

Por fim a instrução SELECT recupera todas as colunas na tabela de borda.

DECLARE @idoc INT, @doc VARCHAR(1000);   
SET @doc ='  
<ROOT>  
<Customers CustomerID="VINET" ContactName="Paul Henriot">  
   <Orders CustomerID="VINET" EmployeeID="5" OrderDate=  
           "1996-07-04T00:00:00">  
      <Order_x0020_Details OrderID="10248" ProductID="11" Quantity="12"/>  
      <Order_x0020_Details OrderID="10248" ProductID="42" Quantity="10"/>  
   </Orders>  
</Customers>  
<Customers CustomerID="LILAS" ContactName="Carlos Gonzlez">  
   <Orders CustomerID="LILAS" EmployeeID="3" OrderDate=  
           "1996-08-16T00:00:00">  
      <Order_x0020_Details OrderID="10283" ProductID="72" Quantity="3"/>  
   </Orders>  
</Customers>  
</ROOT>';  
  
--Create an internal representation of the XML document.  
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;   
  
-- SELECT statement that uses the OPENXML rowset provider.  
SELECT    *  
FROM       OPENXML (@idoc, '/ROOT/Customers')   
EXEC sp_xml_removedocument @idoc;   

Consulte Também

Exemplos: uso do OPENXML