OPENXML (Transact-SQL)

  • Artigo

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 sobre um documento XML. Como OPENXML é um provedor de conjunto de linhas, OPENXML pode ser usado em instruções Transact-SQL nas quais provedores de conjunto de linhas, como uma tabela, exibição ou 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 ) ]

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 é attribute-centric mapeamento.
1 Use o attribute-centric mapeamento. Pode ser combinado com XML_ELEMENTS. Nesse caso, attribute-centric o mapeamento é aplicado primeiro. Em seguida, element-centric o mapeamento é aplicado a todas as colunas restantes.
2 Use o element-centric mapeamento. Pode ser combinado com XML_ATTRIBUTES. Nesse caso, element-centric o mapeamento é aplicado primeiro. Em seguida, attribute-centric o mapeamento é aplicado a todas as 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

Uma definição de esquema do formulário: 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, o mapeamento padrão (element-centricattribute-centricou mapeamento conforme especificado por sinalizadores) ocorrerá.

    O padrão XPath especificado como ColPattern é usado para especificar a natureza especial do mapeamento (for attribute-centric e element-centric mapping) que substitui ou aprimora o mapeamento padrão indicado por sinalizadores.

    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 WITH cláusula fornece um formato de conjunto de linhas (e informações adicionais de mapeamento, conforme necessário) usando SchemaDeclaration ou especificando um TableName existente. Se a cláusula opcional WITH 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 um valor 0de ID . 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 nodetype do nó cujo pai é identificado por essa ID. Por exemplo, se o nó for um nó de texto, o pai dele poderá ser um nó de atributo.

Se o nó estiver no nível superior do documento XML, será ParentID NULL.
nodetype int Identifica o tipo de nó. Esse valor é um número inteiro que corresponde à numeração do tipo de nó XML DOM. Os tipos de nó são:

1 = Nó do elemento
2 = Nó de atributo
3 = Nó de texto
localname nvarchar Fornece o nome local do elemento ou do atributo. NULL se o objeto DOM não tiver um nome.
prefix 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.
data type nvarchar O tipo de dados real do elemento ou linha de atributo, caso contrário, é NULL. O tipo de dados é deduzido do DTD embutido ou do esquema embutido.
prev bigint A ID de XML do elemento irmão anterior. NULL se não houver um irmão anterior direto.
text ntext Contém o valor do atributo ou o conteúdo do elemento em forma de texto (ou se NULL 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 attribute-centric mapeamento. 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)
);

Veja a seguir o conjunto de resultados.

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

Se a mesma SELECT instrução for executada com sinalizadores definidos como 2, indicando element-centric mapeamento, os valores de CustomerID e ContactName para ambos os clientes no documento XML serão retornados como NULL, porque não há nenhum elemento nomeado CustomerID ou ContactName no documento XML.

Veja a seguir o conjunto de resultados.

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

B. Especifique ColPattern para mapeamento entre colunas e os 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 element-centric mapeamento 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'
);

Veja a seguir 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 WITH cláusula 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;

Conteúdo relacionado