none
Ajuda com o OPENXML RRS feed

  • Pergunta

  • 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);

    terça-feira, 7 de outubro de 2014 19:53

Respostas

  • 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"

    • Marcado como Resposta Euler Vicente quarta-feira, 8 de outubro de 2014 17:35
    quarta-feira, 8 de outubro de 2014 14:35
    Moderador

Todas as Respostas

  • 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"

    terça-feira, 7 de outubro de 2014 20:28
    Moderador
  • 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!

    quarta-feira, 8 de outubro de 2014 11:32
  • 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"

    • Marcado como Resposta Euler Vicente quarta-feira, 8 de outubro de 2014 17:35
    quarta-feira, 8 de outubro de 2014 14:35
    Moderador
  • Obrigado Durval! Parece que o caminho é esse mesmo!

    Abraço

    quarta-feira, 8 de outubro de 2014 17:35