OPENXML (SQL Server)

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do AzureBanco de Dados SQL no Microsoft Fabric

OPENXML é uma palavra-chave do Transact-SQL que fornece um conjunto de linhas a partir de documentos XML na memória, semelhante ao de uma tabela ou exibição. OPENXML permite acesso a dados XML ainda que ele seja um conjunto de linhas relacional. Ele faz isso fornecendo uma exibição do conjunto de linhas da representação interna de um documento XML. Os registros no conjunto de linhas podem ser armazenados em tabelas do banco de dados.

OPENXML pode ser usado em instruções SELECT e SELECT INTO em qualquer lugar em que provedores de conjunto de resultados, uma exibição ou OPENROWSET possam aparecer como a origem. Para obter informações sobre a sintaxe do OPENXML, consulte OPENXML (Transact-SQL).

Para escrever consultas em um documento XML usando OPENXML, você deve primeiro chamar sp_xml_preparedocument. Isso analisa o documento XML e retorna um identificador ao documento analisado pronto para consumo. O documento analisado é uma representação da árvore DOM (Document Object Model) de vários nós no documento XML. O identificador do documento é passado para OPENXML. Em seguida, o OPENXML fornece uma exibição do conjunto de linhas do documento, baseado nos parâmetros passados para ele.

A representação interna de um documento XML deve ser removida da memória chamando o procedimento armazenado do sistema sp_xml_removedocument para liberar a memória.

A ilustração a seguir mostra o processo.

Observe que para entender o OPENXML, é necessário estar familiarizado com consultas XPath e ter um entendimento de XML. Para obter mais informações sobre suporte ao XPath no SQL Server, consulte Usando consultas XPath no SQLXML 4.0.

Exemplo

O exemplo a seguir mostra o uso do OPENXML em uma instrução INSERT e em uma instrução SELECT . O documento XML de exemplo contém elementos <Customers> e <Orders> .

Primeiro, o procedimento armazenado sp_xml_preparedocument analisa o documento XML. O documento analisado é uma representação em árvore dos nós (elementos, atributos, texto e comentários) no documento XML. OPENXML se refere, em seguida, a esse documento XML analisado e fornece uma exibição de conjunto de linhas de todo ou partes desse documento XML. Uma instrução INSERT que usa OPENXML pode inserir dados desse tipo de conjunto de linhas em uma tabela de banco de dados. Várias chamadas do OPENXML podem ser usadas para fornecer uma exibição do conjunto de linhas de várias partes do documento XML e processá-las, por exemplo, inserindo-as em diferentes tabelas. Esse processo também é conhecido como fragmentação de XML em tabelas.

No exemplo a seguir, um documento XML é fragmentado de uma maneira que os elementos <Customers> são armazenados na tabela Customers e elementos <Orders> são armazenados na tabela Orders usando duas instruções INSERT . O exemplo também mostra uma instrução SELECT com OPENXML que recupera CustomerID e OrderDate do documento XML. A última etapa do processo é chamar sp_xml_removedocument. Isso é feito para liberar a memória alocada para conter a representação interna em árvore do XML que foi criada durante a fase de parsing.

-- Create tables for later population using OPENXML.
CREATE TABLE Customers (CustomerID varchar(20) primary key,
                ContactName varchar(20),
                CompanyName varchar(20));
GO
CREATE TABLE Orders( CustomerID varchar(20), OrderDate datetime);
GO
DECLARE @docHandle int;
DECLARE @xmlDocument nvarchar(max); -- or xml type
SET @xmlDocument = N'<ROOT>
<Customers CustomerID="XYZAA" ContactName="Joe" CompanyName="Company1">
<Orders CustomerID="XYZAA" OrderDate="2000-08-25T00:00:00"/>
<Orders CustomerID="XYZAA" OrderDate="2000-10-03T00:00:00"/>
</Customers>
<Customers CustomerID="XYZBB" ContactName="Steve"
CompanyName="Company2">No Orders yet!
</Customers>
</ROOT>';
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument;
-- Use OPENXML to provide rowset consisting of customer data.
INSERT Customers
SELECT *
FROM OPENXML(@docHandle, N'/ROOT/Customers')
  WITH Customers;
-- Use OPENXML to provide rowset consisting of order data.
INSERT Orders
SELECT *
FROM OPENXML(@docHandle, N'//Orders')
  WITH Orders;
-- Using OPENXML in a SELECT statement.
SELECT * FROM OPENXML(@docHandle, N'/ROOT/Customers/Orders')
  WITH (CustomerID nchar(5) '../@CustomerID', OrderDate datetime);
-- Remove the internal representation of the XML document.
EXEC sp_xml_removedocument @docHandle;

A ilustração a seguir mostra a árvore XML analisada do documento XML anterior que foi criado usando sp_xml_preparedocument.

Parâmetros de OPENXML

Os parâmetros para OPENXML incluem:

• Um identificador de documento XML (idoc)

• Uma expressão XPath para identificar os nós a serem mapeados para linhas (rowpattern)

• Uma descrição do conjunto de linhas a ser gerado

• Mapeamento entre as colunas do conjunto de linhas e os nós do XML

Identificador do documento XML (idoc)

O identificador do documento é retornado pelo procedimento armazenado sp_xml_preparedocument .

Expressão XPath para identificar os nós a serem processados (rowpattern)

A expressão XPath especificada como rowpattern identifica um conjunto de nós no documento XML. Cada nó identificado por rowpattern corresponde a uma única linha no conjunto de linhas gerado por OPENXML.

Os nós identificados pela expressão XPath podem ser qualquer nó XML no documento XML. Se rowpattern identificar um conjunto de elementos no documento XML, haverá uma linha no rowset para cada nó de elemento identificado. Por exemplo, se rowpattern terminar em um atributo, será criada uma linha para cada nó de atributo selecionado por rowpattern.

Descrição do conjunto de linhas a ser gerado

Um esquema de conjunto de linhas é usado pelo OPENXML para gerar o conjunto de linhas resultante. É possível usar as seguintes opções ao especificar um esquema de conjunto de linhas.

Use o formato de tabela edge

Você deve usar o formato de tabela de borda para especificar um esquema de conjunto de linhas. Não use a cláusula WITH.

Quando você faz isso, OPENXML retorna um conjunto de linhas no formato de tabela edge. Isso é chamado de tabela de arestas, pois cada aresta da árvore do documento XML analisado é mapeada para uma linha no conjunto de linhas.

Tabelas de borda representam a estrutura do documento XML refinado dentro de uma única tabela. Essa estrutura inclui o nomes dos atributos e elementos, a hierarquia do documento, os namespaces e as instruções de processamento. O formato da tabela de bordas permite que você obtenha informações adicionais que não são expostas por meio das metapropriedades. Para mais informações sobre as metapropriedades, consulte Specify Metaproperties in OPENXML.

As informações adicionais fornecidas por uma tabela de borda permitem armazenar e consultar o tipo de dados de um elemento e de um atributo e o tipo de nó, e também armazenar e consultar informações sobre a estrutura do documento XML. Com essas informações adicionais, também pode ser possível construir seu próprio sistema de gerenciamento de documentos XML.

Usando uma tabela de borda, é possível gravar procedimentos armazenados que usam documentos XML como entrada BLOB (bloco de objetos binários grandes), produzir a tabela de borda e, em seguida, extrair e analisar o documento em um nível mais detalhado. Esse nível detalhado pode incluir a localização da hierarquia do documento, os nomes dos atributos e elementos, os namespaces e as instruções de processamento.

A tabela de bordas também pode servir como um formato de armazenamento para documentos XML quando o mapeamento para outros formatos relacionais não for lógico e um campo de texto não estiver fornecendo informações estruturais suficientes.

Em situações em que é possível usar um analisador XML para examinar um documento XML, é possível usar uma tabela de borda em vez de obter as mesmas informações.

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

Usar a cláusula WITH para especificar uma tabela existente

Você pode usar a cláusula WITH para especificar o nome de uma tabela existente. Para isso, basta especificar o nome de uma tabela existente cujo esquema possa ser usado por OPENXML para gerar o conjunto de linhas.

Usar a cláusula WITH para especificar um esquema

É possível usar a cláusula WITH para especificar um esquema completo. Para especificar o esquema do conjunto de linhas, você especifica os nomes das colunas, seus tipos de dados e seus mapeamentos para o documento XML.

Você pode especificar o padrão da coluna usando o parâmetro ColPattern na SchemaDeclaration. O padrão da coluna especificado é usado para mapear uma coluna do conjunto de linhas para o nó XML que é identificado pelo padrão da linha e também é usado para determinar o tipo de mapeamento.

Se ColPattern não for especificado para uma coluna, a coluna do rowset é mapeada para o nó XML com o mesmo nome, de acordo com o mapeamento especificado pelo parâmetro flags. No entanto se ColPattern for especificado como parte da especificação do esquema na cláusula WITH, ele sobrescreverá o mapeamento especificado no parâmetro flags .

Mapeamento entre as colunas do conjunto de resultados e os nós XML

Na instrução OPENXML, você pode opcionalmente especificar o tipo de mapeamento, como centrado em atributo ou centrado em elemento, entre as colunas do conjunto de linhas e os nós XML identificados pelo rowpattern. Essas informações são usadas na transformação entre os nós XML e as colunas do conjunto de linhas.

Você pode especificar o mapeamento de duas maneiras e também pode usar ambas:

• Usando o parâmetro flags

  O mapeamento especificado pelo parâmetro flags pressupõe correspondência de nomes na qual os nós XML são mapeados para as colunas do conjunto de linhas correspondentes com o mesmo nome.

• Usando o parâmetro ColPattern

  ColPattern, uma expressão XPath , é especificada como parte de SchemaDeclaration na cláusula WITH. O mapeamento especificado em ColPattern substitui o mapeamento especificado pelo parâmetro flags .

  ColPattern pode ser usado para especificar o tipo de mapeamento, como centrado em atributo ou centrado em elemento, que sobrescreve ou aprimora o mapeamento indicado por flags.

  ColPattern é especificado nas seguintes circunstâncias:

  • O nome da coluna no conjunto de linhas é diferente do nome do atributo ou elemento para o qual ele é mapeado. Nesse caso, ColPattern é usado para identificar o elemento XML e o nome do atributo ao qual a coluna do conjunto de linhas é mapeada.

  • Você quer mapear um atributo de metapropriedade para a coluna. Nesse caso, ColPattern é usado para identificar a metapropriedade para a qual a coluna do conjunto de linhas é mapeada. Para mais informações sobre como usar as metapropriedades, consulte Especificar metapropriedades em OPENXML.

Os parâmetros flags e ColPattern são opcionais. Se nenhum mapeamento for especificado, será pressuposto o mapeamento centrado em atributo. O mapeamento centrado em atributo é o valor padrão do parâmetro flags .

Mapeamento centrado em atributo

A definição do parâmetro flags no OPENXML como 1 (XML_ATTRIBUTES) especifica mapeamento centrado em atributo . Se flags contiver XML_ ATTRIBUTES, o conjunto de linhas exposto fornecerá ou consumirá linhas nas quais cada elemento XML é representado como uma linha. Os atributos XML são mapeados para os atributos definidos na SchemaDeclaration ou que são fornecidos pelo Tablename da cláusula WITH com base na correspondência de nomes. A correspondência de nomes significa que os atributos XML de um nome específico são armazenados em uma coluna no conjunto de linhas com o mesmo nome.

Se o nome da coluna for diferente do nome de atributo para o qual ele é mapeado, ColPattern deverá ser especificado.

Se o atributo XML tiver um qualificador de namespace, o nome da coluna no conjunto de linhas também precisará ter o qualificador.

Mapeamento centrado em elemento

A definição do parâmetro flags no OPENXML como 2 (XML_ELEMENTS) especifica mapeamento centrado em elemento . Isso é semelhante ao mapeamento centrado em atributos, exceto pelas seguintes diferenças:

• Na correspondência de nomes do exemplo de mapeamento, um mapeamento de coluna para um elemento XML com o mesmo nome escolhe os subelementos simples, a menos que um padrão no nível da coluna seja especificado. No processo de recuperação, se o subelemento for complexo porque contém subelementos adicionais, a coluna será definida como NULL. Valores de atributos dos subelementos são ignorados então.

• Para vários subelementos com o mesmo nome, o primeiro nó é retornado.

Conteúdo relacionado

• sp_xml_preparedocument (Transact-SQL)
• sp_xml_removedocument (Transact-SQL)
• OPENXML (Transact-SQL)
• Dados XML (SQL Server)