OPENXML (Transact-SQL)

Artigo
05/10/2023

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

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.

Convenções de sintaxe de Transact-SQL

Sintaxe

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

Observação

Para exibir a sintaxe do Transact-SQL para o SQL Server 2014 (12.x) e versões anteriores, confira a Documentação das versões anteriores.

Argumentos

idoc

O identificador 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 serem 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:xmltext`.

SchemaDeclaration

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

ColName

O nome da 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 nas 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 pelo 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, 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 de dados real 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. Usar uma instrução SELECT básica 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. Especificar 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. Obter 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;

Confira também

Exemplos: uso do OPENXML

OPENXML (Transact-SQL)

Sintaxe

Argumentos

idoc

rowpattern

sinalizadores

SchemaDeclaration

TableName

Comentários

Exemplos

a. Usar uma instrução SELECT básica com OPENXML

B. Especificar ColPattern para mapeamento entre colunas e atributos XML

C. Obter resultados em um formato de tabela de borda

Confira também

Comentários

Comentários

Recursos adicionais