Ajuda com o OPENXML

Question

Colegas, boa tarde!

Estou precisando converter um XML em dado tabular (um registro de dados para cada conjunto de informações contidas em "Resultado"), mas estou tendo dificuldade em aprender a usar o comando OPENXML.

Meu código abaixo não retorna valor nenhum. Alguém poderia me ajudar nisso? Lembrando que eu posso ter vários registros de "Resultado" dentro do registro/tag "Consulta".

DECLARE @idoc int, @doc varchar(8000);

SET @doc ='
<?xml version="1.0" encoding="utf-8"?>
<DataSet>
 <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
  <Consulta>      
   <Resultado diffgr:id="Resultado1" msdata:rowOrder="0" xmlns="Resultado">        
      <ID>2</ID>        
      <Edicao>2500</Edicao>        
      <DataPublicacao>2014-10-06T00:00:00-03:00</DataPublicacao>        
      <NumeroAto>2121</NumeroAto>        
   </Resultado>    
  </Consulta>  
 </diffgr:diffgram>
</DataSet>
';

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;
SELECT    *
FROM       OPENXML (@idoc, '//Consulta/Resultado')
            WITH (ID    smallint,
                    Edicao int,
                    DataPublicacao smalldatetime,
                    NumeroAto int);

Answer 1

Euler,

Segue dois artigos que devem ajudar a esclarecer sua dúvida.

http://social.technet.microsoft.com/wiki/pt-br/contents/articles/22149.como-extrair-dados-em-xml-para-atender-os-requisitos-de-um-schema.aspx

http://social.technet.microsoft.com/wiki/pt-br/contents/articles/12731.extraindo-informacoes-de-arquivo-xml-para-o-sql-server.aspx

Se ajudou na sua solução, não esqueça de marcar como resposta !

Abraços,

Durval Ramos
Microsoft Partner | MTA | MCSA - SQL Server 2012 | MCSE - Data Platform
----------------------------------
Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"

Answer 2

Bom dia Durval!

Obrigado pela sua atenção! Mas na verdade, minha dificuldade maior tem sido na escrita do XPATH para resolver a consulta e o mapeamento dos campos do XML. Eu já li alguns artigos na net sobre XPATH, mas mesmo assim minha query não retorna nada.

O que eu fiz de errado nesse XPATH? --> '//Consulta/Resultado'

O mapeamento que eu fiz das colunas está correto?

WITH (ID    smallint,
                    Edicao int,
                    DataPublicacao smalldatetime,
                    NumeroAto int);

Agradeço muito a atenção!

Answer 3

Bom dia Durval!

Obrigado pela sua atenção! Mas na verdade, minha dificuldade maior tem sido na escrita do XPATH para resolver a consulta e o mapeamento dos campos do XML. Eu já li alguns artigos na net sobre XPATH, mas mesmo assim minha query não retorna nada.

O que eu fiz de errado nesse XPATH? --> '//Consulta/Resultado'

O mapeamento que eu fiz das colunas está correto?

WITH (ID    smallint,
                    Edicao int,
                    DataPublicacao smalldatetime,
                    NumeroAto int);

Agradeço muito a atenção!

Euler,

Provavelmente você está com problema na definição do seu namespace.

Veja no exemplo abaixo, seu XML sem referências adicionais funciona normalmente.

DECLARE @idoc int, @doc varchar(8000);

SET @doc ='
<?xml version="1.0" encoding="utf-8"?>
<DataSet>
 <diffgr>
  <Consulta>      
   <Resultado>        
      <ID>2</ID>        
      <Edicao>2500</Edicao>        
      <DataPublicacao>2014-10-06T00:00:00-03:00</DataPublicacao>        
      <NumeroAto>2121</NumeroAto>        
   </Resultado>    
  </Consulta>  
 </diffgr>
</DataSet>
';

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;

-- Este ponto você precisa indicar o NAMESPACE (xmlns), 
--senão informar esta linha ele não retorna nada.
--;WITH XMLNAMESPACES(DEFAULT 'schemas-microsoft-com:xml-msdata') 
 
SELECT * FROM OPENXML(@idoc, N'//DataSet/diffgram/Consulta/Resultado/') 
WITH (ID    smallint 'ID',
	Edicao int  'Edicao',
	DataPublicacao smalldatetime  'DataPublicacao',
	NumeroAto int 'NumeroAto')
WHERE ID is not null;

--É IMPORTANTE SEMPRE REMOVER A REFERÊNCIA DE SEU XML
EXEC sp_xml_removedocument @idoc; 
 

Não esqueça de remover o XML de sua estrutura no fim do processo. 

Se ajudou na sua solução, não esqueça de marcar como resposta !

Abraços,

Durval Ramos
Microsoft Partner | MTA | MCSA - SQL Server 2012 | MCSE - Data Platform
----------------------------------
Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"

Answer 4

Obrigado Durval! Parece que o caminho é esse mesmo!

Abraço