Usando OPENXML – Parte 2

Usando OPENXML – Parte 2

por Jerônimo Jardel Vogt

No artigo anterior, fizemos uma introdução ao uso das stored procedures sp_xml_preparedocument e sp_xml_removedocument. A primeira sp permite importar o documento XML para memória a fim de podermos consultá-lo com a função OPENXML. A segunda sp remove o documento lido da memória.

A função OPENXML, permite transformar um documento XML em um conjunto de linhas (rowset) a serem inseridas em uma ou mais tabelas no banco de dados. Depois de fazer o parse de um documento XML usando a stored procedure sp_xml_preparedocument, podemos gerar um rowset a partir do comando OPENXML. E é da sintaxe e uso da função OPENXML que tratamos nesse artigo.

Sintaxe do OPENXML

A função OPENXML retorna um rowset de um documento XML, permitindo assim, escrever comandos Transact-SQL de SELECT, UPDATE ou INSERT para alterar o banco de dados.

A sintaxe da função OPENXML é apresentada abaixo:

OpenXML(idoc, rowpattern [, flags])

[WITH (SchemaDeclaration | TableName)]

<SchemaDeclaration> ::=

ColumnName ColumnType [colpattern],n

A tabela abaixo descreve os parâmetros da função OPENXML.

Usando o OPENXML

A função OPENXML normalmente é usada em comandos de SELECT como se fosse uma tabela ou view. Por exemplo, considere o parse do seguinte fragmento XML:

DECLARE @hdoc int

DECLARE @doc varchar(1000)

SET @doc ='

<Customer CustomerID="1" CustomerType="S">

   <Order SalesOrderID="43860" Status="5" OrderDate="2001-08-01T00:00:00">

      <OrderDetail ProductID="761" Quantity="2"/>

      <OrderDetail ProductID="770" Quantity="1"/>

   </Order>

</Customer>'

EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc

Supondo que pretendemos listar os atributos do elemento OrderDetail, especificamos esse nó no argumento rowpattern. O comando SELECT com OPENXML será como mostra o exemplo abaixo.

SELECT *

FROM OPENXML (@hdoc, '/Customer/Order/OrderDetail')

WITH (ProductID int, Quantity int)

E a tabela abaixo mostra o rowset retornado pela consulta OPENXML.

ProductID      Quantity

---------------------------

761              2

770              1

Em algumas situações, precisamos retornar dados de diferentes níveis da hierarquia de um documento XML, e para isso usamos um padrão XPath para especificar as colunas como parâmetro colpattern. O XPath usado como parâmetro de coluna é relativo ao padrão XPath especificado no parâmetro rowpattern.

O exemplo abaixo mostra como uma declaração de esquema pode referenciar os nós da hierarquia XML usando o mesmo documento XML do exemplo anterior.

SELECT *

FROM OPENXML (@idoc, '/Customer/Order/OrderDetail', 1)

WITH (CustomerID int '../../@CustomerID',

OrderID int '../@SalesOrderID',

OrderDate datetime '../@OrderDate',

ProdID int '@ProductID',

Quantity int)

O valor do parâmetro flags está setado em 1, assim, atributos são retornados por padrão, a menos que um parâmetro colpattern diferente for especificado. O atributo CustomerID deverá ser retornado a partir do elemento Customer, que está dois níveis acima do elemento OrderDetail corrente. Isso é conseguido usando a combinação. . / para navegar para os níveis acima. O atributo Quantity é retornado automaticamente, pois o nome do atributo é o mesmo que o nome da coluna, sendo diferente com a coluna ProdID.

A tabela abaixo mostra o rowset retornado pela consulta OPENXML anterior.

Mais informações sobre o uso da função OPENXML, pesquise por “Querying XML Using OPENXML” no SQL Server Books Online.