Exemplos: Usar OPENXML

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

Os exemplos deste artigo mostram como o OPENXML é usado para criar uma vista de conjunto de linhas de um documento XML. Para informações sobre a sintaxe do OPENXML, veja OPENXML (Transact-SQL). Os exemplos mostram todos os aspetos do OPENXML, mas não especificam metapropriedades no OPENXML. Para mais informações sobre como especificar metapropriedades no OPENXML, consulte Especificar Metapropriedades no OPENXML.

Examples

Ao recuperar os dados, o padrão de linhas é usado para identificar os nós no documento XML que determinam as linhas. Adicionalmente, rowpattern é expresso na linguagem de padrões XPath usada na implementação MSXML XPath. Por exemplo, se o padrão terminar num elemento ou atributo, é criada uma linha para cada elemento ou nó de atributo selecionado pelo padrão de linhas.

O valor de flags fornece o mapeamento padrão. Se não for especificado um ColPattern na SchemaDeclaration, assume-se que o mapeamento indicado em flags é utilizado. O valor flags é ignorado se ColPattern for especificado no SchemaDeclaration. O ColPattern especificado determina o mapeamento, centrado no atributo ou no elemento, bem como o comportamento no tratamento de dados excedentes e não consumidos.

A. Executar uma instrução SELECT com OPENXML

O documento XML neste exemplo é composto pelos <Customer>elementos, <Order>, e <OrderDetail> . A instrução OPENXML recupera informações do cliente num conjunto de linhas de duas colunas, CustomerID e ContactName, a partir do documento XML.

Primeiro, o procedimento armazenado sp_xml_preparedocument é invocado para obter um identificador de documento. Este handle de documento é passado para o OPENXML.

A declaração OPENXML ilustra o seguinte:

rowpattern (/ROOT/Customer) identifica os <Customer> nós a processar.
O valor do parâmetro flags está definido como 1 e indica um mapeamento centrado no atributo. Como resultado, os atributos XML correspondem às colunas do conjunto de resultados definido no SchemaDeclaration.
Em SchemaDeclaration, na cláusula WITH, os valores especificados de ColName correspondem aos respetivos nomes de atributos XML. Portanto, o parâmetro ColPattern não é especificado no SchemaDeclaration.

A instrução SELECT recupera então todas as colunas do conjunto de linhas fornecido pelo OPENXML.

DECLARE @DocHandle int;
DECLARE @XmlDocument nvarchar(1000);
SET @XmlDocument = N'<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 Gonzalez">
   <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 @DocHandle OUTPUT, @XmlDocument;
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@DocHandle, '/ROOT/Customer',1)
      WITH (CustomerID  varchar(10),
            ContactName varchar(20));
EXEC sp_xml_removedocument @DocHandle;

Este é o resultado:

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

Como os <Customer> elementos não têm subelementos, se a mesma instrução SELECT for executada com flags definidos para 2 para indicar o mapeamento centrado no elemento, os valores de CustomerID e ContactName para ambos os clientes são devolvidos como NULL.

O @xmlDocument também pode ser do tipo xml ou (n)varchar(max).

Se <CustomerID> e <ContactName> no documento XML forem subelementos, o mapeamento centrado no elemento recupera os valores.

DECLARE @XmlDocumentHandle int;
DECLARE @XmlDocument nvarchar(1000);
SET @XmlDocument = N'<ROOT>
<Customer>
   <CustomerID>VINET</CustomerID>
   <ContactName>Paul Henriot</ContactName>
   <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</CustomerID>
   <ContactName>Carlos Gonzalez</ContactName>
   <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 @XmlDocumentHandle OUTPUT, @XmlDocument;
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT    *
FROM      OPENXML (@XmlDocumentHandle, '/ROOT/Customer',2)
           WITH (CustomerID  varchar(10),
                 ContactName varchar(20));
EXEC sp_xml_removedocument @XmlDocumentHandle;

Este é o resultado:

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

O identificador do documento devolvido por sp_xml_preparedocument é válido durante o lote, mas não durante a sessão.

B. Especifique o ColPattern para mapeamento entre colunas do conjunto de linhas e os atributos e elementos XML

Este exemplo mostra como o padrão XPath é especificado no parâmetro opcional ColPattern para fornecer mapeamento entre as colunas do conjunto de linhas e os atributos e elementos XML.

O documento XML neste exemplo é composto pelos <Customer>elementos, <Order>, e <OrderDetail> . A instrução OPENXML recupera informações de clientes e encomendas como um conjunto de linhas (CustomerID, OrderDate, ProdID e Qty) a partir do documento XML.

Primeiro, o procedimento armazenado sp_xml_preparedocument é invocado para obter um identificador de documento. Este handle de documento é passado para o OPENXML.

A declaração OPENXML ilustra o seguinte:

rowpattern (/ROOT/Customer/Order/OrderDetail) identifica os <OrderDetail> nós que devem ser processados.

Para ilustração, o valor do parâmetro flags é definido como 2 e indica um mapeamento centrado no elemento. No entanto, o mapeamento especificado no ColPattern sobrescreve este mapeamento. Ou seja, o padrão XPath especificado em ColPattern mapeia as colunas do conjunto de linhas para atributos. Isto resulta num mapeamento centrado no atributo.

Em SchemaDeclaration, na cláusula WITH, ColPattern também é especificado com os parâmetros ColName e ColType. O padrão ColPattern opcional é o padrão XPath especificado e indica o seguinte:

As colunas OrderID, CustomerID e OrderDate no conjunto de linhas correspondem aos atributos do pai dos nós identificados por padrão de linhas, e o padrão de linhas identifica os <OrderDetail> nós. Assim, as colunas CustomerID e OrderDate correspondem aos atributos CustomerID e OrderDate do <Order> elemento.
As colunas ProdID e Qty no conjunto de linhas mapeiam para os atributos ProductID e Quantity dos nós identificados no padrão de linhas.

A instrução SELECT recupera então todas as colunas do conjunto de linhas fornecido pelo OPENXML.

DECLARE @XmlDocumentHandle int;
DECLARE @XmlDocument nvarchar(1000);
SET @XmlDocument = N'<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 Gonzalez">
   <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 @XmlDocumentHandle OUTPUT, @XmlDocument;
-- Execute a SELECT stmt using OPENXML rowset provider.
SELECT *
FROM OPENXML (@XmlDocumentHandle, '/ROOT/Customer/Order/OrderDetail',2)
WITH (OrderID     int         '../@OrderID',
      CustomerID  varchar(10) '../@CustomerID',
      OrderDate   datetime    '../@OrderDate',
      ProdID      int         '@ProductID',
      Qty         int         '@Quantity');
EXEC sp_xml_removedocument @XmlDocumentHandle;

Este é o resultado:

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

O padrão XPath especificado como ColPattern também pode ser especificado para mapear os elementos XML para as colunas do conjunto de linhas. Isto resulta num mapeamento centrado no elemento. No exemplo seguinte, o documento <CustomerID> XML e <OrderDate> são subelementos do <Orders> elemento. Como ColPattern sobreescreve o mapeamento especificado no parâmetro flags, o parâmetro flags não é especificado no OPENXML.

DECLARE @docHandle int;
DECLARE @XmlDocument nvarchar(1000);
SET @XmlDocument = N'<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
   <Order EmployeeID="5" >
      <OrderID>10248</OrderID>
      <CustomerID>VINET</CustomerID>
      <OrderDate>1996-07-04T00:00:00</OrderDate>
      <OrderDetail ProductID="11" Quantity="12"/>
      <OrderDetail ProductID="42" Quantity="10"/>
   </Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzalez">
   <Order  EmployeeID="3" >
      <OrderID>10283</OrderID>
      <CustomerID>LILAS</CustomerID>
      <OrderDate>1996-08-16T00:00:00</OrderDate>
      <OrderDetail ProductID="72" Quantity="3"/>
   </Order>
</Customer>
</ROOT>';
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @docHandle OUTPUT, @XmlDocument;
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@docHandle, '/ROOT/Customer/Order/OrderDetail')
WITH (CustomerID  varchar(10)   '../CustomerID',
      OrderDate   datetime      '../OrderDate',
      ProdID      int           '@ProductID',
      Qty         int           '@Quantity');
EXEC sp_xml_removedocument @docHandle;

C. Combinar mapeamento centrado em atributos e centrado em elementos

Neste exemplo, o parâmetro de flags está definido como 3 e indica que tanto o mapeamento centrado no atributo como o elemento serão aplicados. Neste caso, aplica-se primeiro o mapeamento centrado no atributo, e depois aplica-se o mapeamento centrado no elemento para todas as colunas ainda não tratadas.

DECLARE @docHandle int;
DECLARE @XmlDocument nvarchar(1000);
SET @XmlDocument =N'<ROOT>
<Customer CustomerID="VINET"  >
     <ContactName>Paul Henriot</ContactName>
   <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 Gonzalez</ContactName>
   <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 @docHandle OUTPUT, @XmlDocument;

-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@docHandle, '/ROOT/Customer',3)
      WITH (CustomerID  varchar(10),
            ContactName varchar(20));
EXEC sp_xml_removedocument @docHandle;

Este é o resultado

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

O mapeamento centrado no atributo é aplicado ao CustomerID. Não existe nenhum atributo ContactName no <Customer> elemento. Portanto, aplica-se o mapeamento centrado no elemento.

D. Especifique a função text() XPath como ColPattern

O documento XML neste exemplo é composto pelos elementos <Customer> e <Order>. A instrução OPENXML recupera um conjunto de linhas composto pelo atributo oid do <Order> elemento, o ID do pai do nó identificado pelo padrão de linhas e a cadeia de valores folha do conteúdo do elemento.

Primeiro, o procedimento armazenado sp_xml_preparedocument é invocado para obter um identificador de documento. Este handle de documento é passado para o OPENXML.

A declaração OPENXML ilustra o seguinte:

rowpattern (/root/Customer/Order) identifica os nós <Order> que devem ser processados.
O valor do parâmetro flags está definido como 1 e indica um mapeamento centrado no atributo. Como resultado, os atributos XML correspondem às colunas do conjunto de linhas definidas no SchemaDeclaration.
No SchemaDeclaration na cláusula WITH, os nomes das colunas oid e amount do conjunto de linhas correspondem aos respetivos nomes de atributos XML. Portanto, o parâmetro ColPattern não é especificado. Para a coluna de comentários no conjunto de linhas, a função XPath, text(), é especificada como ColPattern. Isto sobrescreve o mapeamento centrado no atributo especificado em flags, e a coluna contém a cadeia de valor final do conteúdo do elemento.

A instrução SELECT recupera então todas as colunas do conjunto de linhas fornecido pelo OPENXML.

DECLARE @docHandle int;
DECLARE @xmlDocument nvarchar(1000);
--sample XML document
SET @xmlDocument =N'<root>
  <Customer cid= "C1" name="Janine" city="Issaquah">
      <Order oid="O1" date="1/20/1996" amount="3.5" />
      <Order oid="O2" date="4/30/1997" amount="13.4">Customer was very satisfied
      </Order>
   </Customer>
   <Customer cid="C2" name="Ursula" city="Oelde" >
      <Order oid="O3" date="7/14/1999" amount="100" note="Wrap it blue
             white red">
            <Urgency>Important</Urgency>
            Happy Customer.
      </Order>
      <Order oid="O4" date="1/20/1996" amount="10000"/>
   </Customer>
</root>';
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument;

-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@docHandle, '/root/Customer/Order', 1)
     WITH (oid     char(5),
           amount  float,
           comment ntext 'text()');
EXEC sp_xml_removedocument @docHandle;

Este é o resultado:

oid   amount        comment
----- -----------   -----------------------------
O1    3.5           NULL
O2    13.4          Customer was very satisfied
O3    100.0         Happy Customer.
O4    10000.0       NULL

E. Especificar TableName na cláusula WITH

Este exemplo especifica TableName na cláusula WITH em vez de SchemaDeclaration. Isto é útil se tiver uma tabela com a estrutura que deseja e não são necessários padrões de colunas do parâmetro ColPattern.

O documento XML neste exemplo é composto pelos elementos <Customer> e <Order>. A instrução OPENXML recupera informação de ordem num conjunto de linhas de três colunas (oid, data e quantidade) a partir do documento XML.

Primeiro, o procedimento armazenado sp_xml_preparedocument é invocado para obter um identificador de documento. Este handle de documento é passado para o OPENXML.

A declaração OPENXML ilustra o seguinte:

rowpattern (/root/Customer/Order) identifica os nós <Order> que devem ser processados.
Não há SchemaDeclaration na cláusula WITH. Em vez disso, é especificado um nome da tabela. Portanto, o esquema de tabela é usado como esquema de linhas.
O valor do parâmetro flags está definido como 1 e indica um mapeamento centrado no atributo. Assim, os atributos dos elementos, identificados pelo padrão de linhas, correspondem às colunas do conjunto de linhas com o mesmo nome.

A instrução SELECT recupera então todas as colunas do conjunto de linhas fornecido pelo OPENXML.

-- Create a test table. This table schema is used by OPENXML as the
-- rowset schema.
CREATE TABLE T1(oid char(5), date datetime, amount float);
GO
DECLARE @docHandle int;
DECLARE @xmlDocument nvarchar(1000);
-- Sample XML document
SET @xmlDocument =N'<root>
  <Customer cid= "C1" name="Janine" city="Issaquah">
      <Order oid="O1" date="1/20/1996" amount="3.5" />
      <Order oid="O2" date="4/30/1997" amount="13.4">Customer was very
             satisfied</Order>
   </Customer>
   <Customer cid="C2" name="Ursula" city="Oelde" >
      <Order oid="O3" date="7/14/1999" amount="100" note="Wrap it blue
             white red">
          <Urgency>Important</Urgency>
      </Order>
      <Order oid="O4" date="1/20/1996" amount="10000"/>
   </Customer>
</root>';
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument;

-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@docHandle, '/root/Customer/Order', 1)
     WITH T1;
EXEC sp_xml_removedocument @docHandle;

Este é o resultado:

oid   date                        amount
----- --------------------------- ----------
O1    1996-01-20 00:00:00.000     3.5
O2    1997-04-30 00:00:00.000     13.4
O3    1999-07-14 00:00:00.000     100.0
O4    1996-01-20 00:00:00.000     10000.0

F. Obtenha o resultado num formato de tabela de arestas

Neste exemplo, a cláusula WITH não está especificada na instrução OPENXML. Como resultado, o conjunto de linhas gerado pelo OPENXML tem um formato de tabela de arestas. A instrução SELECT devolve todas as colunas da tabela de arestas.

O documento XML de exemplo é composto pelos elementos <Customer>, <Order> e <OrderDetail>.

Primeiro, o procedimento armazenado sp_xml_preparedocument é invocado para obter um identificador de documento. Este handle de documento é passado para o OPENXML.

A declaração OPENXML ilustra o seguinte:

rowpattern (/ROOT/Customer) identifica os <Customer> nós a processar.
A cláusula WITH não está fornecida. Portanto, o OPENXML devolve o conjunto de linhas num formato de tabela de arestas.

A instrução SELECT recupera então todas as colunas da tabela de arestas.

DECLARE @docHandle int;
DECLARE @xmlDocument nvarchar(1000);
SET @xmlDocument = N'<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 Gonzalez">
   <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 @docHandle OUTPUT, @xmlDocument;
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@docHandle, '/ROOT/Customer');

EXEC sp_xml_removedocument @docHandle;

O resultado é devolvido como uma tabela de arestas. Pode escrever consultas à tabela de arestas para obter informações. Por exemplo:

A consulta seguinte devolve o número de nós Cliente do documento. Como a cláusula WITH não é especificada, o OPENXML devolve uma tabela de aresta. A instrução SELECT consulta a tabela de aresta.
```
SELECT count(*)
FROM OPENXML(@docHandle, '/')
WHERE localname = 'Customer';
```

A consulta seguinte devolve os nomes locais dos nós XML do tipo elemento.

SELECT distinct localname
FROM OPENXML(@docHandle, '/')
WHERE nodetype = 1
ORDER BY localname;

G. Especifique o padrão de linhas terminando com um atributo

O documento XML neste exemplo é composto pelos <Customer>elementos, <Order>, e <OrderDetail> . A instrução OPENXML recupera informações sobre os detalhes da ordem num conjunto de linhas de três colunas (ProductID,Quantity e OrderID) a partir do documento XML.

Primeiro, o sp_xml_preparedocument é chamado para obter um handle de documento. Este handle de documento é passado para o OPENXML.

A declaração OPENXML ilustra o seguinte:

rowpattern (/ROOT/Customer/Order/OrderDetail/@ProductID) termina com um atributo XML, ProductID. No conjunto de linhas resultante, é criada uma linha para cada nó de atributo selecionado no documento XML.
Neste exemplo, o parâmetro de flags não é especificado. Em vez disso, os mapeamentos são especificados pelo parâmetro ColPattern .

Em SchemaDeclaration na cláusula WITH, ColPattern também é especificado com os parâmetros ColName e ColType . O ColPattern opcional é o padrão XPath especificado para indicar o seguinte:

O padrão XPath (.) especificado como ColPattern para a coluna ProdID no conjunto de resultados identifica o nó de contexto e nó atual. De acordo com o padrão de linhas especificado, é o atributo ProductID do <OrderDetail> elemento.
O ColPattern, .. /@Quantity, especificado para a coluna Qty no conjunto de linhas, identifica o atributo Quantity do nó pai, <OrderDetail>, do nó de contexto, <ProductID.>
De forma semelhante, o ColPattern, ../../@OrderID, especificado para a coluna OID no conjunto de linhas, identifica o atributo OrderID do nó pai do nó de contexto, <Order>. O nó pai é <OrderDetail>, e o nó de contexto é <ProductID>.

A instrução SELECT recupera então todas as colunas do conjunto de linhas fornecido pelo OPENXML.

DECLARE @docHandle int;
DECLARE @xmlDocument nvarchar(1000);
--Sample XML document
SET @xmlDocument =N'<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 Gonzalez">
   <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 @docHandle OUTPUT, @xmlDocument
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@docHandle, '/ROOT/Customer/Order/OrderDetail/@ProductID')
       WITH ( ProdID  int '.',
              Qty     int '../@Quantity',
              OID     int '../../@OrderID');
EXEC sp_xml_removedocument @docHandle;

Este é o resultado:

ProdID      Qty         OID
----------- ----------- -------
11          12          10248
42          10          10248
72          3           10283

H. Especifique um documento XML que tenha múltiplos nós de texto

Se tiver vários nós de texto num documento XML, uma instrução SELECT com um ColPattern, text(), devolve apenas o primeiro nó de texto, em vez de todos. Por exemplo:

DECLARE @h int;
EXEC sp_xml_preparedocument @h OUTPUT,
         N'<root xmlns:a="urn:1">
           <a:Elem abar="asdf">
             T<a>a</a>U
           </a:Elem>
         </root>',
         '<ns xmlns:b="urn:1" />';

SELECT * FROM openxml(@h, '/root/b:Elem')
      WITH (Col1 varchar(20) 'text()');
EXEC sp_xml_removedocument @h;

A instrução SELECT devolve T como resultado, e não TaU.

I. Especifique o tipo de dado XML na cláusula WITH

Na cláusula WITH, um padrão de coluna que é mapeado para a coluna do tipo de dados xml, seja tipado ou não, deve devolver uma sequência vazia ou uma sequência de elementos, instruções de processamento, nós de texto e comentários. Os dados são convertidos para o tipo de dados xml.

No exemplo seguinte, a declaração do esquema de tabela na cláusula WITH inclui colunas do tipo xml .

DECLARE @h int;
DECLARE @x xml;
set @x = '<Root>
  <row id="1"><lname>Duffy</lname>
   <Address>
            <Street>111 Maple</Street>
            <City>Seattle</City>
   </Address>
  </row>
  <row id="2"><lname>Wang</lname>
   <Address>
            <Street>222 Pine</Street>
            <City>Bothell</City>
   </Address>
  </row>
</Root>';

EXEC sp_xml_preparedocument @h output, @x;
SELECT *
FROM   OPENXML (@h, '/Root/row', 10)
      WITH (id int '@id',

            lname    varchar(30),
            xmlname  xml 'lname',
            OverFlow xml '@mp:xmltext');
EXEC sp_xml_removedocument @h;

Especificamente, estás a passar uma variável do tipo xml (@x) para a função sp_xml_preparedocument().

Este é o resultado:

id  lname   xmlname                   OverFlow
--- ------- ------------------------------ -------------------------------
1   Duffy   <lname>Duffy</lname>  <row><Address>
                                   <Street>111 Maple</Street>
                                   <City>Seattle</City>
                                  </Address></row>
2   Wang    <lname>Wang</lname>   <row><Address>
                                    <Street>222 Pine</Street>
                                    <City>Bothell</City>
                                   </Address></row>

Note o seguinte do resultado:

Para a coluna lname do tipo varchar(30 ), o seu valor é recuperado do elemento correspondente <lname> .
Para a coluna xmlname do tipo xml , o mesmo elemento name é devolvido como o seu valor.
O indicador está ajustado para 10. O 10 significa 2 + 8, onde 2 indica mapeamento centrado no elemento e 8 indica que apenas dados XML não consumidos devem ser adicionados à coluna OverFlow definida na cláusula WITH. Se definires o flag para 2, todo o documento XML é copiado para a coluna OverFlow que está especificada na cláusula WITH.
Caso a coluna na cláusula WITH seja uma coluna XML tipada e a instância XML não confirme o esquema, é devolto um erro.

J. Recuperar valores individuais de atributos multivalorados

Um documento XML pode ter atributos multivalorados. Por exemplo, o atributo IDREFS pode ser multivalorado. Num documento XML, os valores de atributos multivalorados são especificados como uma cadeia, com os valores separados por um espaço. No documento XML seguinte, o atributo attends do <elemento Student> e o atributo attendedBy de <Class> são multivalorados. Recuperar valores individuais de um atributo XML multivalorado e armazenar cada valor numa linha separada na base de dados requer trabalho extra. Este exemplo mostra o processo.

Este documento XML de exemplo é composto pelos seguintes elementos:

<Aluno>

Os id (id do estudante), nome e frequência atributos. O atributo attends é um atributo multivalorado.
<Classe>

Os atributos id (ID da classe), name e attendedBy. O atributo attendedBy é um atributo multivalorizado.

O atributo attends em <Student> e o atributo attendedBy em <Class> representam uma relação m:n entre as tabelas Student e Turma. Um aluno pode frequentar muitas aulas e uma aula pode ter muitos alunos.

Assuma que pretende triturar este documento e guardá-lo na base de dados, como mostrado no seguinte:

Guarde os <Student> dados na tabela Estudantes.
Guarde os <Class> dados na tabela de Cursos.
Guarde os dados de relação m:n , entre Aluno e Turma, na tabela CourseAttendence. É necessário mais trabalho para extrair os valores. Para recuperar esta informação e armazená-la na tabela, utilize estes procedimentos armazenados:
- Insert_Idrefs_Values
  
  Insere os valores do ID do curso e do ID do estudante na tabela CourseAttendence.
- Extract_idrefs_values
  
  Extrai os IDs individuais dos estudantes de cada <elemento do curso> . Uma tabela de arestas é usada para recuperar estes valores.

Aqui estão os passos:

-- Create these tables:
DROP TABLE CourseAttendance;
DROP TABLE Students;
DROP TABLE Courses;
GO
CREATE TABLE Students(
                id   varchar(5) primary key,
                name varchar(30)
                );
GO
CREATE TABLE Courses(
               id       varchar(5) primary key,
               name     varchar(30),
               taughtBy varchar(5)
);
GO
CREATE TABLE CourseAttendance(
             id         varchar(5) references Courses(id),
             attendedBy varchar(5) references Students(id),
             constraint CourseAttendance_PK primary key (id, attendedBy)
);
GO
-- Create these stored procedures:
DROP PROCEDURE f_idrefs;
GO
CREATE PROCEDURE f_idrefs
    @t      varchar(500),
    @idtab  varchar(50),
    @id     varchar(5)
AS
DECLARE @sp int;
DECLARE @att varchar(5);
SET @sp = 0;
WHILE (LEN(@t) > 0)
BEGIN
    SET @sp = CHARINDEX(' ', @t+ ' ');
    SET @att = LEFT(@t, @sp-1);
    EXEC('INSERT INTO '+@idtab+' VALUES ('''+@id+''', '''+@att+''')');
    SET @t = SUBSTRING(@t+ ' ', @sp+1, LEN(@t)+1-@sp);
END;
GO

DROP PROCEDURE fill_idrefs
GO
CREATE PROCEDURE fill_idrefs
    @xmldoc     int,
    @xpath      varchar(100),
    @from       varchar(50),
    @to         varchar(50),
    @idtable    varchar(100)
AS
DECLARE @t varchar(500);
DECLARE @id varchar(5);

/* Temporary Edge table */
SELECT *
INTO #TempEdge
FROM OPENXML(@xmldoc, @xpath);

DECLARE fillidrefs_cursor CURSOR FOR
    SELECT CAST(iv.text AS nvarchar(200)) AS id,
           CAST(av.text AS nvarchar(4000)) AS refs
    FROM   #TempEdge c, #TempEdge i,
           #TempEdge iv, #TempEdge a, #TempEdge av
    WHERE  c.id = i.parentid
    AND    UPPER(i.localname) = UPPER(@from)
    AND    i.id = iv.parentid
    AND    c.id = a.parentid
    AND    UPPER(a.localname) = UPPER(@to)
    AND    a.id = av.parentid;

OPEN fillidrefs_cursor
FETCH NEXT FROM fillidrefs_cursor INTO @id, @t;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
    IF (@@FETCH_STATUS <> -2)
    BEGIN
        execute f_idrefs @t, @idtable, @id;
    END
    FETCH NEXT FROM fillidrefs_cursor INTO @id, @t
END;
CLOSE fillidrefs_cursor;
DEALLOCATE fillidrefs_cursor;
Go
-- This is the sample document that is shredded and the data is stored in the preceding tables.
DECLARE @h int;
EXECUTE sp_xml_preparedocument @h OUTPUT, N'<Data>
  <Student id = "s1" name = "Student1"  attends = "c1 c3 c6"  />
  <Student id = "s2" name = "Student2"  attends = "c2 c4" />
  <Student id = "s3" name = "Student3"  attends = "c2 c4 c6" />
  <Student id = "s4" name = "Student4"  attends = "c1 c3 c5" />
  <Student id = "s5" name = "Student5"  attends = "c1 c3 c5 c6" />
  <Student id = "s6" name = "Student6" />

  <Class id = "c1" name = "Intro to Programming"
         attendedBy = "s1 s4 s5" />
  <Class id = "c2" name = "Databases"
         attendedBy = "s2 s3" />
  <Class id = "c3" name = "Operating Systems"
         attendedBy = "s1 s4 s5" />
  <Class id = "c4" name = "Networks" attendedBy = "s2 s3" />
  <Class id = "c5" name = "Algorithms and Graphs"
         attendedBy =  "s4 s5"/>
  <Class id = "c6" name = "Power and Pragmatism"
         attendedBy = "s1 s3 s5" />
</Data>';

INSERT INTO Students SELECT * FROM OPENXML(@h, '//Student') WITH Students

INSERT INTO Courses SELECT * FROM OPENXML(@h, '//Class') WITH Courses
/* Using the edge table */
EXECUTE fill_idrefs @h, '//Class', 'id', 'attendedby', 'CourseAttendance';

SELECT * FROM Students;
SELECT * FROM Courses;
SELECT * FROM CourseAttendance;

EXECUTE sp_xml_removedocument @h;

K. Em XML, recuperar dados binários a partir de dados codificados em base64.

Os dados binários são frequentemente incluídos em XML usando codificação base64. Quando trituras este XML usando OPENXML, recebes os dados codificados em base64. Este exemplo mostra como podes converter os dados codificados em base64 de volta para binário.

Crie uma tabela com dados binários de exemplo.
Use uma consulta FOR XML e a opção BINARY BASE64 para construir XML que tenha os dados binários codificados como base64.
Destrói o XML usando OPENXML. Os dados devolvidos pelo OPENXML serão dados codificados em base64. De seguida, chama a .value função para a converter de volta para binário.

CREATE TABLE T (Col1 int primary key, Col2 varbinary(100));
GO
-- Insert sample binary data
INSERT T VALUES(1, 0x1234567890);
GO
-- Create test XML document that has base64 encoded binary data (use FOR XML query and specify BINARY BASE64 option)
SELECT * FROM T
FOR XML AUTO, BINARY BASE64;
GO
-- result
-- <T Col1="1" Col2="EjRWeJA="/>

-- Now shredd the sample XML using OPENXML.
-- Call the .value function to convert
-- the base64 encoded data returned by OPENXML to binary.
DECLARE @h int;
EXEC sp_xml_preparedocument @h OUTPUT, '<T Col1="1" Col2="EjRWeJA="/>';
SELECT Col1,
CAST('<binary>' + Col2 + '</binary>' AS XML).value('.', 'varbinary(max)') AS BinaryCol
FROM openxml(@h, '/T')
WITH (Col1 integer, Col2 varchar(max)) ;
EXEC sp_xml_removedocument @h;
GO

Este é o resultado. Os dados binários devolvidos são os dados binários originais na tabela T.

Col1        BinaryCol
----------- ---------------------
1           0x1234567890

Consulte também

Feedback

Esta página foi útil?

Last updated on 2025-11-18

Partilhar via

Exemplos: Usar OPENXML

Examples

A. Executar uma instrução SELECT com OPENXML

B. Especifique o ColPattern para mapeamento entre colunas do conjunto de linhas e os atributos e elementos XML

C. Combinar mapeamento centrado em atributos e centrado em elementos

D. Especifique a função text() XPath como ColPattern

E. Especificar TableName na cláusula WITH

F. Obtenha o resultado num formato de tabela de arestas

G. Especifique o padrão de linhas terminando com um atributo

H. Especifique um documento XML que tenha múltiplos nós de texto

I. Especifique o tipo de dado XML na cláusula WITH

J. Recuperar valores individuais de atributos multivalorados

K. Em XML, recuperar dados binários a partir de dados codificados em base64.

Consulte também

Feedback

Recursos adicionais