OPENXML (Transact-SQL)

Aplica-se a:SQL Server Azure SQL Database Azure SQL Managed Instance Base de dados SQL no Microsoft Fabric

OPENXML fornece uma vista de conjunto de linhas sobre um documento XML. Como OPENXML é um fornecedor de conjuntos de linhas, OPENXML pode ser usado em Transact-SQL instruções em que os fornecedores de conjuntos de linhas, como uma tabela, vista ou a OPENROWSET função, podem aparecer.

Transact-SQL convenções de sintaxe

Sintaxe

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

Arguments

IDOC

O handle do documento da representação interna de um documento XML. A representação interna de um documento XML é criada chamando sp_xml_preparedocument.

padrão de filas

O padrão XPath é usado para identificar os nós a serem processados como linhas. Os nós provêm do documento XML cujo handle é passado no parâmetro idoc .

flags

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

Valor do byte	Description
`0`	Por defeito, é `attribute-centric` o mapeamento.
`1`	Usa o `attribute-centric` mapeamento. Pode ser combinado com `XML_ELEMENTS`. Neste caso, `attribute-centric` aplica-se primeiro o mapeamento. De seguida, `element-centric` aplica-se o mapeamento para quaisquer colunas restantes.
`2`	Usa o `element-centric` mapeamento. Pode ser combinado com `XML_ATTRIBUTES`. Neste caso, `element-centric` aplica-se primeiro o mapeamento. De seguida, `attribute-centric` aplica-se o mapeamento para quaisquer colunas restantes.
`8`	Pode ser combinado (OR lógico) com `XML_ATTRIBUTES` ou `XML_ELEMENTS`. No contexto da recuperação, este flag indica que os dados consumidos não devem ser copiados para a propriedade `@mp:xmltext`de overflow .

SchemaDeclaração

Uma definição de esquema da forma: ColNameColType [ ColPattern | MetaProperty ] [ , ColNameColType [ ColPattern | MetaProperty ] ... ]

ColName

O nome da coluna no conjunto de linhas.
ColType

O tipo de dado SQL Server da coluna no conjunto de linhas. Se os tipos de coluna diferirem do tipo de dados xml subjacente ao atributo, ocorre coerção de tipo.
ColPattern

Um padrão opcional e geral de XPath que descreve como os nós XML devem ser mapeados para as colunas. Se o ColPattern não for especificado, ocorre o mapeamento padrão (attribute-centric ou element-centric mapeamento conforme especificado pelas bandeiras).

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

O padrão geral XPath especificado como ColPattern também suporta as metapropriedades.
MetaPropriedade

Uma das metapropriedades fornecidas por OPENXML. Se a MetaPropriedade for especificada, a coluna contém a informação fornecida pela metapropriedade. As metapropriedades permitem-lhe extrair informação (como posição relativa e namespace) sobre nós XML. Estas metapropriedades fornecem mais informação do que é visível na representação textual.

Nome da tabela

O nome da tabela que pode ser dado (em vez de SchemaDeclaration), se já existir uma tabela com o esquema desejado e não forem necessários padrões de colunas.

Observações

A WITH cláusula fornece um formato de conjunto de linhas (e informação adicional de mapeamento conforme necessário) usando SchemaDeclaration ou especificando um TableName, já existente. Se a cláusula opcional WITH não for especificada, os resultados são devolvidos em formato de tabela de arestas . As tabelas de arestas representam a estrutura fina do documento XML (como nomes de elementos/atributos, a hierarquia do documento, os namespaces, PIs, etc.) numa única tabela.

A tabela seguinte descreve a estrutura da tabela de aresta .

Nome da coluna	Tipo de dados	Description
`id`	bigint	O ID único do nó documento. O elemento raiz tem um valor `0`ID . Os valores negativos de ID são reservados.
`parentid`	bigint	Identifica o pai do nó. O pai identificado por este ID não é necessariamente o elemento pai, mas depende do `nodetype` nó cujo pai é 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 ao nível superior do documento XML, é `ParentIDNULL`.
`nodetype`	int	Identifica o tipo de nó. Este valor é um inteiro que corresponde à numeração do tipo de nó DOM em XML. Os tipos de nós são: `1` = Nó elemento `2` = Nó de atributo `3` = Nó de texto
`localname`	nvarchar	Dá o nome local do elemento ou atributo. `NULL` se o objeto DOM não tiver nome.
`prefix`	nvarchar	O prefixo de namespace do nome do nó.
`namespaceuri`	nvarchar	O URI do namespace do nó. Se o valor for `NULL`, não existe espaço de nomes.
`data type`	nvarchar	O tipo de dado real da linha de elementos ou atributos, caso contrário, é `NULL`. O tipo de dado é inferido a partir do DTD inline ou do esquema inline.
`prev`	bigint	O ID 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 está `NULL` se a entrada da tabela de arestas não exigir um valor).

Examples

A. Use uma instrução SELECT básica com OPENXML

O exemplo seguinte cria uma representação interna da imagem XML usando sp_xml_preparedocument. Uma SELECT instrução que utiliza um OPENXML fornecedor de conjuntos de linhas é então executada contra a representação interna do documento XML.

O valor do flag é definido para 1. Este valor indica attribute-centric o mapeamento. Assim, os atributos XML correspondem às colunas do conjunto de linhas. O padrão de linhas especificado como /ROOT/Customer identifica os <Customers> nós a serem processados.

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

O OPENXML fornecedor de conjuntos de linhas cria um conjunto de duas colunas (CustomerID e ContactName) do qual a SELECT instrução recupera as colunas necessárias (neste 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)
);

Aqui está o conjunto de resultados.

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

Se a mesma SELECT instrução for executada com flags definidos para 2, indicando element-centric o mapeamento, os valores de CustomerID e ContactName para ambos os clientes no documento XML são devolvidos como NULL, porque não há elementos nomeados CustomerID ou ContactName no documento XML.

Aqui está o conjunto de resultados.

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

B. Especificar o ColPattern para mapeamento entre colunas e os atributos XML

A consulta seguinte devolve o ID do cliente, data da encomenda, ID do produto e atributos de quantidade do documento XML. O padrão de linha identifica os <OrderDetails> elementos. ProductID e Quantity são os atributos do <OrderDetails> elemento. No entanto, OrderID, CustomerID, e OrderDate são os atributos do elemento pai (<Orders>).

O ColPattern opcional é especificado para os seguintes mapeamentos:

O OrderID, CustomerID, e OrderDate no conjunto de linhas mapeiam para os atributos do pai dos nós identificados pelo padrão de linhas no documento XML.
A ProdID coluna no conjunto de linhas corresponde ao ProductID atributo, e a Qty coluna no conjunto de linhas corresponde ao Quantity atributo dos nós identificados no padrão de linhas.

Embora o element-centric mapeamento seja especificado pelo parâmetro flags , o mapeamento especificado no ColPattern sobrescrive este 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'
);

Aqui está 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 num formato de tabela de arestas

O documento XML de exemplo no exemplo seguinte consiste em <Customers>, <Orders>, e <Order_0020_Details> elementos. Primeiro, sp_xml_preparedocument é chamado para obter um endereço de documento. Este handle do documento é passado para OPENXML.

Na OPENXML instrução, o padrão de linha (/ROOT/Customers) identifica os <Customers> nós a processar. Como a WITH cláusula não é fornecida, OPENXML devolve o conjunto de linhas num formato de tabela de arestas .

Finalmente, a SELECT instrução recupera todas as colunas da tabela de arestas .

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;

Exemplos: Utilização de OPENXML

Comentários

Esta página foi útil?

Last updated on 2025-11-19

Partilhar via

OPENXML (Transact-SQL)

Sintaxe

Arguments

IDOC

padrão de filas

flags

SchemaDeclaração

Nome da tabela

Observações

Examples

A. Use uma instrução SELECT básica com OPENXML

B. Especificar o ColPattern para mapeamento entre colunas e os atributos XML

C. Obter resultados num formato de tabela de arestas

Conteúdo relacionado

Comentários

Recursos adicionais