Nota
O acesso a esta página requer autorização. Pode tentar iniciar sessão ou alterar os diretórios.
O acesso a esta página requer autorização. Pode tentar alterar os diretórios.
Aplica-se a:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceBase de dados SQL no Microsoft Fabric
OPENXML é uma palavra-chave Transact-SQL, que fornece um conjunto de linhas sobre documentos XML em memória semelhante a uma tabela ou a uma vista. O OPENXML permite o acesso a dados XML como se fosse um conjunto de linhas relacional. Faz isto fornecendo uma vista por conjunto de linhas da representação interna de um documento XML. Os registos no conjunto de linhas podem ser armazenados em tabelas de bases de dados.
O OPENXML pode ser usado nas instruções SELECT e SELECT INTO sempre que fornecedores de rowset, uma vista ou OPENROWSET possam aparecer como fonte. Para informações sobre a sintaxe do OPENXML, veja OPENXML (Transact-SQL).
Para escrever consultas contra um documento XML usando OPENXML, deve primeiro chamar sp_xml_preparedocument. Isto analisa o documento XML e devolve um handle ao documento analisado que está pronto para consumo. O documento analisado é uma representação em árvore de modelo de objetos (DOM) de vários nós no documento XML. O handle do documento é passado para o OPENXML. O OPENXML fornece então uma vista de linhas do documento, baseada nos parâmetros que lhe são passados.
Observação
sp_xml_preparedocument utiliza uma versão atualizada em SQL do parser MSXML, Msxmlsql.dll. Esta versão do analisador MSXML foi concebida para suportar SQL Server e manter-se compatível com a versão 2.6 do MSXML.
A representação interna de um documento XML deve ser removida da memória chamando o procedimento armazenado do sistema sp_xml_removedocument para libertar a memória.
A ilustração seguinte mostra o processo.
Note que, para compreender o OPENXML, é necessário conhecimento das consultas XPath e compreensão de XML. Para mais informações sobre o suporte ao XPath no SQL Server, consulte Utilização de Consultas XPath em SQLXML 4.0.
Observação
O OpenXML permite que os padrões XPath de linhas e colunas sejam parametrizados como variáveis. Tal parametrização pode levar a injeções de expressões XPath, se o programador expor a parametrização a utilizadores externos (por exemplo, se os parâmetros forem fornecidos através de um procedimento armazenado chamado externamente). Para evitar potenciais problemas de segurança, recomenda-se que os parâmetros XPath nunca sejam expostos a chamadores externos.
Example
O exemplo seguinte mostra o uso de OPENXML numa INSERT afirmação e numa SELECT afirmação. O documento XML de exemplo contém os elementos <Customers> e <Orders>.
Primeiro, o sp_xml_preparedocument procedimento armazenado 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 depois refere-se a este documento XML analisado e fornece uma vista em conjunto de linhas de todo ou partes deste documento XML. Uma instrução INSERT usando OPENXML pode inserir dados de um conjunto de linhas tal numa tabela de base de dados. Várias OPENXML chamadas podem ser usadas para fornecer uma vista de linhas de várias partes do documento XML e processá-las, por exemplo, inserindo-as em diferentes tabelas. Este processo também é referido como triturar XML em tabelas.
No exemplo seguinte, um documento XML é triturado de forma a que os elementos <Customers> são armazenados na tabela Customers e os elementos <Orders> na tabela Orders usando duas instruções INSERT. O exemplo também mostra uma SELECT instrução com OPENXML que recupera CustomerID e OrderDate do documento XML. O último passo do processo é ligar para sp_xml_removedocument. Isto é feito para libertar a memória alocada para conter a representação interna da árvore XML criada durante a fase de análise sintética.
-- 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 seguinte mostra a árvore XML analisada do documento XML anterior que foi criado usando sp_xml_preparedocument.
Parâmetros OPENXML
Os parâmetros do OPENXML incluem os seguintes:
Um handle de documento XML (idoc)
Uma expressão XPath para identificar os nós a serem mapeados para linhas (padrão de linhas)
Uma descrição do conjunto de linhas a gerar
Mapeamento entre as colunas do conjunto de linhas e os nós XML
Manipulador de documento XML (idoc)
O handle do documento é retornado pelo procedimento armazenado sp_xml_preparedocument.
Expressão XPath para identificar os nós a serem processados (padrão de linhas)
A expressão XPath especificada como padrão de linhas identifica um conjunto de nós no documento XML. Cada nó identificado por padrão de linhas corresponde a uma única linha no conjunto de linhas gerada pelo OPENXML.
Os nós identificados pela expressão XPath podem ser qualquer nó XML no documento XML. Se o rowpattern identificar um conjunto de elementos no documento XML, há uma linha no conjunto de linhas para cada nó de elemento identificado. Por exemplo, se o padrão de linhas terminar num atributo, é criada uma linha para cada nó de atributo selecionado pelo padrão de linhas.
Descrição do conjunto de linhas a gerar
Um esquema de conjunto de linhas é usado pelo OPENXML para gerar o conjunto de linhas resultante. Pode usar as seguintes opções ao especificar um esquema de rowset.
Usar o formato de tabela de bordas
Deves usar o formato de tabela de margem para especificar um esquema de linhas. Não uses a cláusula WITH.
Quando fazes isto, o OPENXML devolve um conjunto de linhas no formato de tabela de arestas. Isto é chamado de tabela de arestas, pois cada borda na árvore do documento XML interpretado corresponde a uma linha no conjunto de linhas.
As tabelas de borda representam a estrutura detalhada de documentos XML numa única tabela. Esta estrutura inclui os nomes dos elementos e atributos, a hierarquia dos documentos, os namespaces e as instruções de processamento. O formato de tabela de bordas permite-lhe obter informação adicional que não é exposta através das metapropriedades. Para mais informações sobre metapropriedades, consulte Especificar Metapropriedades em OPENXML.
A informação adicional fornecida por uma tabela de arestas permite-lhe armazenar e consultar o tipo de dado de um elemento e atributo, e o tipo de nó, bem como armazenar e consultar informações sobre a estrutura do documento XML. Com esta informação adicional, poderá também ser possível construir o seu próprio sistema de gestão de documentos XML.
Ao usar uma tabela de arestas, pode escrever procedimentos armazenados que recebem documentos XML como entrada binária de objetos grandes (BLOB), produzir a tabela de arestas e depois extrair e analisar o documento a um nível mais detalhado. Este nível detalhado pode incluir encontrar a hierarquia do documento, os nomes dos elementos e atributos, os namespaces e as instruções de processamento.
A tabela de arestas também pode servir como formato de armazenamento para documentos XML quando mapear para outros formatos relacionais não é lógico e um campo ntext não fornece informação estrutural suficiente.
Em situações em que pode usar um parser XML para examinar um documento XML, pode usar uma tabela de arestas para obter a mesma informação.
A tabela seguinte descreve a estrutura da tabela de borda.
| Nome da coluna | Tipo de dados | Description |
|---|---|---|
| ID | bigint | É o ID único do nó de documento. O elemento raiz tem um valor ID de 0. Os valores negativos de ID são reservados. |
| parentid | bigint | Identifica o pai do nó. O progenitor identificado por este ID não é necessariamente o elemento pai. No entanto, isso depende do NodeType do nó cujo progenitor é identificado por este ID. Por exemplo, se o nó for um nó de texto, o seu pai pode ser um nó de atributo. Se o nó estiver no nível superior do documento XML, seu ParentID é NULL. |
| Tipo de nó | int | Identifica o tipo de nó e é um inteiro que corresponde à numeração de tipos de nós do modelo de objeto XML (DOM). Seguem os valores que podem aparecer nesta coluna para indicar o tipo de nó: 1 = Nó de elemento 2 = Nó de atributo 3 = Nó de texto 4 = nó de secção CDATA 5 = Nó de referência da entidade 6 = Nó entidade 7 = Nó de instrução de processamento 8 = Nó de comentário 9 = Nó do documento 10 = Nó Tipo de Documento 11 = Nó Fragmento de Documento 12 = Nó de notação Para mais informações, consulte o artigo "NodeType Property" no sdk Microsoft XML (MSXML). |
| Nome local | nvarchar(max) | Dá o nome local do elemento ou atributo. É NULL se o objeto DOM não tiver nome. |
| prefixo | nvarchar(max) | É o prefixo de namespace do nome do nó. |
| namespaceuri | nvarchar(max) | É o URI do namespace do nó. Se o valor for NULL, não existe namespace. |
| Tipo de dados | nvarchar(max) | É o tipo de dado real da linha de elemento ou atributo e, caso contrário, é NULL. O tipo de dado é inferido a partir do DTD inline ou do esquema inline. |
| Anterior | bigint | É o ID XML do elemento irmão anterior. É NULL se não existir um irmão direto anterior. |
| texto | ntext | Contém o valor do atributo ou o conteúdo do elemento em forma de texto. Ou é NULL, se a entrada da tabela de eixos não precisar de valor. |
Use a cláusula WITH para especificar uma tabela existente
Pode usar a cláusula WITH para especificar o nome de uma tabela existente. Para isso, basta especificar um nome de tabela existente cujo esquema possa ser usado pelo OPENXML para gerar o conjunto de linhas.
Use a cláusula WITH para especificar um esquema
Pode usar a Cláusula WITH para especificar um esquema completo. Ao especificar o esquema do conjunto de linhas, especifica os nomes das colunas, os seus tipos de dados e o seu mapeamento para o documento XML.
Pode especificar o padrão da coluna usando o parâmetro ColPattern na SchemaDeclaration. O padrão de colunas especificado é usado para mapear uma coluna de conjunto de linhas para o nó XML identificado pelo padrão de linhas e também é usado para determinar o tipo de mapeamento.
Se o ColPattern não for especificado para uma coluna, a coluna do conjunto de linhas será mapeada para o nó XML com o mesmo nome, com base no mapeamento especificado pelo parâmetro flags. No entanto, se o ColPattern for especificado como parte da especificação do esquema na cláusula WITH, sobrescreve o mapeamento especificado no parâmetro flags.
Mapeamento entre as colunas do conjunto de linhas e os nós XML
Na instrução OPENXML, pode opcionalmente especificar o tipo de mapeamento, como centrado no atributo ou no elemento, entre as colunas do conjunto de linhas e os nós XML identificados pelo padrão de linhas. Esta informação é usada na transformação entre os nós XML e as colunas do conjunto de linhas.
Pode especificar o mapeamento de duas formas, e também pode especificar ambas:
Usando o parâmetro flags
O mapeamento especificado pelo parâmetro flags assume correspondência de nomes, na qual os nós XML mapeiam para as colunas correspondentes do conjunto de linhas com o mesmo nome.
Utilizando o parâmetro ColPattern
ColPattern, uma expressão XPath, é especificado como parte do SchemaDeclaration na cláusula WITH. O mapeamento especificado em ColPattern sobrescrive o mapeamento especificado pelo parâmetro de flags .
O ColPattern pode ser usado para especificar o tipo de mapeamento, como centrado no atributo ou no elemento, que sobreescreve ou melhora o mapeamento padrão indicado pelas bandeiras.
O ColPattern é especificado nas seguintes circunstâncias:
O nome da coluna no conjunto de linhas é diferente do nome do elemento ou atributo para o qual está mapeado. Neste caso, o ColPattern é usado para identificar o elemento XML e o nome do atributo para o qual a coluna do conjunto de linhas corresponde.
Queres mapear um atributo metapropriedade para a coluna. Neste caso, ColPattern é usado para identificar a metapropriedade ao qual a coluna do conjunto de linhas está mapeada. Para mais informações sobre como usar metapropriedades, consulte Especificar Metapropriedades no OPENXML.
Tanto as bandeiras como os parâmetros do ColPattern são opcionais. Se não for especificado o mapeamento, assume-se o mapeamento centrado no atributo. O mapeamento centrado em atributos é o valor padrão do parâmetro de flags .
Mapeamento centrado em atributos
Definir o parâmetro flags no OPENXML para 1 (XML_ATTRIBUTES) especifica o mapeamento centrado em atributos . Se flags contiver XML_ ATRIBUTOS, o conjunto de linhas exposto fornece ou consome linhas onde cada elemento XML é representado como uma linha. Os atributos XML são mapeados para os atributos definidos na SchemaDeclaration ou fornecidos pelo TableName da cláusula WITH, com base na correspondência dos nomes. Correspondência de nomes significa que os atributos XML de um determinado nome são armazenados numa coluna do conjunto de linhas com o mesmo nome.
Se o nome da coluna for diferente do nome do atributo a que corresponde, o ColPattern deve ser especificado.
Se o atributo XML tiver um qualificador de namespace, o nome da coluna no conjunto de linhas também deve ter o qualificador.
Mapeamento centrado no elemento
Definir o parâmetro de flags no OPENXML para o valor 2 (XML_ELEMENTS) especifica o mapeamento centralizado em elemento. É semelhante ao mapeamento centrado em atributos , exceto pelas seguintes diferenças:
A correspondência de nomes no exemplo de mapeamento, onde uma coluna mapeia para um elemento XML com o mesmo nome, seleciona os subelementos não complexos, a menos que seja definida uma configuração específica ao nível da coluna. No processo de recuperação, se o subelemento for complexo porque contém subelementos adicionais, a coluna é definida como NULL. Os valores dos atributos dos subelementos são então ignorados.
No caso de múltiplos subelementos com o mesmo nome, é devolvido o primeiro nó.