none
Tabela com campo text RRS feed

  • Pergunta

  • Tenho uma tabela com um campo do tipo TEXT.

    O conteudo deste campo esta com um texto em formato XML referente a Nota fiscal Eletrica.

    Preciso extrair dados deste campo por exemplo referente a observações.

    Qual a melhor forma de fazer isso no SQL ?


    hdk

    sexta-feira, 19 de outubro de 2012 17:05

Respostas

  • Ótimo.

    Eu recomendo um estudo sobre o tipo de dados XML. Você descobrirá diversas opções muito boas.

    Talvez possa começar por aqui:

    1) http://sqlfromhell.wordpress.com/2011/08/02/lendo-xml-no-sql-server-iniciando-com-xquery/

    2) http://msdn.microsoft.com/pt-br/library/ms189075.aspx


    Roberson Ferreira - Database Developer
    Acesse: www.robersonferreira.com.br
    Email: contato@robersonferreira.com.br

    Se esta sugestão for útil, por favor, classifique-a como útil.
    Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

    • Marcado como Resposta Harley Araujo sexta-feira, 9 de novembro de 2012 13:41
    sábado, 20 de outubro de 2012 03:07
    Moderador
  • Harley,

    Seguindo a mesma linha de raciocínio do Roberson, após realizar a conversão deste tipo de dados para XML, será possível utilizar a linguagem XQuery, para pesquisa e obtenção de dados neste formato de conteúdo.

    Veja se este exemplo poderá ajudar:

    CREATE TABLE Prods (ID Int IDENTITY(1,1) PRIMARY KEY, ColunaXML XML NOT NULL)
    GO
    
    INSERT INTO Prods(ColunaXML)
    SELECT * 
      FROM OPENROWSET(BULK 'C:\Produtos.xml',
                      SINGLE_BLOB) AS A
    GO
    
    
    SELECT *
      FROM Prods
    
    
    -- Método Query()
    -- Retorna um fragmento XML
    
    -- Antes de iniciar podemos observar que o método deve ser chamado em case sensitive, 
    -- "Query" não é válido
    SELECT ID, ColunaXML.Query('TabelaProdutos/ProdCat/SubProdCat/Prod')
      FROM Prods
    
    -- Consulta simples que retorna os dados de todos os produtos
    SELECT ID, ColunaXML.query('TabelaProdutos/ProdCat/SubProdCat/Prod')
      FROM Prods
    
    -- Retorna todos o XML aplicando um filtro para trazer apenas os produtos da 
    -- Categoria "Accessories"
    SELECT ID, ColunaXML.query('TabelaProdutos/ProdCat[@Categoria="Accessories"]')
      FROM Prods
    
    -- Retorna todos os produtos aplicando um filtro para trazer apenas os produtos da 
    -- Categoria "Clothing"
    SELECT ID, ColunaXML.query('TabelaProdutos/ProdCat[@Categoria="Clothing"]/SubProdCat/Prod')
      FROM Prods
    
    -- Vamos utilizar as expressões xQuery
    SELECT ID, ColunaXML.query('for $TabelaProdutos in /TabelaProdutos
                                return $TabelaProdutos/ProdCat/SubProdCat/Prod')
      FROM Prods
    -- Nesta consulta retornamos a mesma coisa que a consulta acima
    -- porem desta vez usamos as expressões xQuery "for" e "return"
    -- a Microsoft recomenda a utilização de comandos xPath e somente quando o 
    -- xPath não atender aos seus requisitos então deverá recorer aos recursos do xQuery
    
    -- Outra consulta, desta vez retornando apenas os produtos da categoria "Clothing"
    SELECT ID, ColunaXML.query('for $TabelaProdutos in /TabelaProdutos
                                return $TabelaProdutos/ProdCat[@Categoria="Clothing"]/SubProdCat/Prod')
      FROM Prods
    GO
    
    -- Retorna o total dos produtos por categoria
    SELECT ID, ColunaXML.query('for $TabProd in /TabelaProdutos/ProdCat
    	                           return
    	                           <Fabricante Nome="{$TabProd/@Categoria}"
    	                           Carros="{count($TabProd//SubProdCat/Prod)}"/>')
      FROM Prods
    
    
    -- Retorna todas as categorias e seus produtos ordenados por Categoria e Produto
    -- e renomeia o nome das tags dos elementos
    SELECT ColunaXML.query(
    'for $Cat in /TabelaProdutos/ProdCat,
         $Produto in $Cat/SubProdCat/Prod/@Produto
    	order by $Produto, $Cat/@Nome
    	return
        <Cat Prod="{$Cat/@Categoria}">{$Produto}</Cat>
        ')
      FROM Prods
    
    
    -- Método Value()
    
    -- Retorna o nome do produto encontrado no XML, repare o [1] que significa em qual nó ele 
    -- deverá ler a informação, por ex [1], [2] ...
    SELECT	ID,	
           ColunaXML.value('(/TabelaProdutos/ProdCat/SubProdCat/Prod/@Produto)[1]','nvarchar(80)') As Produto
      FROM	Prods
    
    -- Retorna o nome da Categoria, SubCategoria e Produto encontrado no XML
    SELECT	ID,	
           ColunaXML.value('(/TabelaProdutos/ProdCat/@Categoria)[1]','nvarchar(80)') As Categoria,
           ColunaXML.value('(/TabelaProdutos/ProdCat/SubProdCat/@SubCategoria)[1]','nvarchar(80)') As SubCategoria,
           ColunaXML.value('(/TabelaProdutos/ProdCat/SubProdCat/Prod/@Produto)[1]','nvarchar(80)') As Produto
      FROM	Prods
    
    -- Método Exists()
    
    -- Verifica se existe o produto "Full-Finger Gloves, L"
    SELECT ID,
    	      ColunaXML.exist('/TabelaProdutos/ProdCat/SubProdCat/Prod[@Produto="Full-Finger Gloves, L"]')
      FROM	Prods
    
    -- Pode ser usado no where como filtro do resultado
    -- irá retornar 1 caso encontre ou 0 caso não encontre
    SELECT *
      FROM	Prods
     WHERE ColunaXML.exist('/TabelaProdutos/ProdCat/SubProdCat/Prod[@Produto="Full-Finger Gloves, L"]') = 1
    
    -- Método Nodes()
    
    -- Retorna 1 linha para cada categoria existente.
    SELECT Pro.Cat.query('.')
      FROM	Prods
     CROSS APPLY ColunaXML.nodes('/TabelaProdutos/ProdCat') AS Pro(Cat)
    
    -- Retorna 1 linha para cada SubCategoria existente.
    SELECT Pro.Cat.query('.')
      FROM	Prods
     CROSS APPLY ColunaXML.nodes('/TabelaProdutos/ProdCat/SubProdCat') AS Pro(Cat)
    
    -- Retorna todas as Categorias
    SELECT Pro.Cat.value('@Categoria', 'nVarChar(80)') AS Categorias
      FROM	Prods
     CROSS APPLY ColunaXML.nodes('/TabelaProdutos/ProdCat') AS Pro(Cat)
    

    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]

    • Marcado como Resposta Harley Araujo sexta-feira, 9 de novembro de 2012 13:41
    terça-feira, 23 de outubro de 2012 12:29

Todas as Respostas

  • Qual a versão do seu SQL?

    Roberson Ferreira - Database Developer
    Acesse: www.robersonferreira.com.br
    Email: contato@robersonferreira.com.br

    Se esta sugestão for útil, por favor, classifique-a como útil.
    Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

    sexta-feira, 19 de outubro de 2012 17:32
    Moderador
  • SQL Server 2008


    hdk

    sexta-feira, 19 de outubro de 2012 17:37
  • Por que você não usa o tipo de dados XML? Este tipo de dados já possui métodos próprios para extração dos dados a partir dos atributos.

    Do contrário, ou você terá que usar funções diretamente no campo Text para extrair o que precisa (Substring, por exemplo) - o que não é bom - ou poderá, talvez, jogar o conteúdo deste campo Text para um campo XML de uma tabela temporária, para então trabalhar com ele como XML.


    Roberson Ferreira - Database Developer
    Acesse: www.robersonferreira.com.br
    Email: contato@robersonferreira.com.br

    Se esta sugestão for útil, por favor, classifique-a como útil.
    Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

    sexta-feira, 19 de outubro de 2012 18:33
    Moderador
  • Beleza, fiz isso.

    Agora como extrair os valores dos nodes para colunas ?


    hdk

    sexta-feira, 19 de outubro de 2012 19:53
  • Ótimo.

    Eu recomendo um estudo sobre o tipo de dados XML. Você descobrirá diversas opções muito boas.

    Talvez possa começar por aqui:

    1) http://sqlfromhell.wordpress.com/2011/08/02/lendo-xml-no-sql-server-iniciando-com-xquery/

    2) http://msdn.microsoft.com/pt-br/library/ms189075.aspx


    Roberson Ferreira - Database Developer
    Acesse: www.robersonferreira.com.br
    Email: contato@robersonferreira.com.br

    Se esta sugestão for útil, por favor, classifique-a como útil.
    Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

    • Marcado como Resposta Harley Araujo sexta-feira, 9 de novembro de 2012 13:41
    sábado, 20 de outubro de 2012 03:07
    Moderador
  • Harley,

    Seguindo a mesma linha de raciocínio do Roberson, após realizar a conversão deste tipo de dados para XML, será possível utilizar a linguagem XQuery, para pesquisa e obtenção de dados neste formato de conteúdo.

    Veja se este exemplo poderá ajudar:

    CREATE TABLE Prods (ID Int IDENTITY(1,1) PRIMARY KEY, ColunaXML XML NOT NULL)
    GO
    
    INSERT INTO Prods(ColunaXML)
    SELECT * 
      FROM OPENROWSET(BULK 'C:\Produtos.xml',
                      SINGLE_BLOB) AS A
    GO
    
    
    SELECT *
      FROM Prods
    
    
    -- Método Query()
    -- Retorna um fragmento XML
    
    -- Antes de iniciar podemos observar que o método deve ser chamado em case sensitive, 
    -- "Query" não é válido
    SELECT ID, ColunaXML.Query('TabelaProdutos/ProdCat/SubProdCat/Prod')
      FROM Prods
    
    -- Consulta simples que retorna os dados de todos os produtos
    SELECT ID, ColunaXML.query('TabelaProdutos/ProdCat/SubProdCat/Prod')
      FROM Prods
    
    -- Retorna todos o XML aplicando um filtro para trazer apenas os produtos da 
    -- Categoria "Accessories"
    SELECT ID, ColunaXML.query('TabelaProdutos/ProdCat[@Categoria="Accessories"]')
      FROM Prods
    
    -- Retorna todos os produtos aplicando um filtro para trazer apenas os produtos da 
    -- Categoria "Clothing"
    SELECT ID, ColunaXML.query('TabelaProdutos/ProdCat[@Categoria="Clothing"]/SubProdCat/Prod')
      FROM Prods
    
    -- Vamos utilizar as expressões xQuery
    SELECT ID, ColunaXML.query('for $TabelaProdutos in /TabelaProdutos
                                return $TabelaProdutos/ProdCat/SubProdCat/Prod')
      FROM Prods
    -- Nesta consulta retornamos a mesma coisa que a consulta acima
    -- porem desta vez usamos as expressões xQuery "for" e "return"
    -- a Microsoft recomenda a utilização de comandos xPath e somente quando o 
    -- xPath não atender aos seus requisitos então deverá recorer aos recursos do xQuery
    
    -- Outra consulta, desta vez retornando apenas os produtos da categoria "Clothing"
    SELECT ID, ColunaXML.query('for $TabelaProdutos in /TabelaProdutos
                                return $TabelaProdutos/ProdCat[@Categoria="Clothing"]/SubProdCat/Prod')
      FROM Prods
    GO
    
    -- Retorna o total dos produtos por categoria
    SELECT ID, ColunaXML.query('for $TabProd in /TabelaProdutos/ProdCat
    	                           return
    	                           <Fabricante Nome="{$TabProd/@Categoria}"
    	                           Carros="{count($TabProd//SubProdCat/Prod)}"/>')
      FROM Prods
    
    
    -- Retorna todas as categorias e seus produtos ordenados por Categoria e Produto
    -- e renomeia o nome das tags dos elementos
    SELECT ColunaXML.query(
    'for $Cat in /TabelaProdutos/ProdCat,
         $Produto in $Cat/SubProdCat/Prod/@Produto
    	order by $Produto, $Cat/@Nome
    	return
        <Cat Prod="{$Cat/@Categoria}">{$Produto}</Cat>
        ')
      FROM Prods
    
    
    -- Método Value()
    
    -- Retorna o nome do produto encontrado no XML, repare o [1] que significa em qual nó ele 
    -- deverá ler a informação, por ex [1], [2] ...
    SELECT	ID,	
           ColunaXML.value('(/TabelaProdutos/ProdCat/SubProdCat/Prod/@Produto)[1]','nvarchar(80)') As Produto
      FROM	Prods
    
    -- Retorna o nome da Categoria, SubCategoria e Produto encontrado no XML
    SELECT	ID,	
           ColunaXML.value('(/TabelaProdutos/ProdCat/@Categoria)[1]','nvarchar(80)') As Categoria,
           ColunaXML.value('(/TabelaProdutos/ProdCat/SubProdCat/@SubCategoria)[1]','nvarchar(80)') As SubCategoria,
           ColunaXML.value('(/TabelaProdutos/ProdCat/SubProdCat/Prod/@Produto)[1]','nvarchar(80)') As Produto
      FROM	Prods
    
    -- Método Exists()
    
    -- Verifica se existe o produto "Full-Finger Gloves, L"
    SELECT ID,
    	      ColunaXML.exist('/TabelaProdutos/ProdCat/SubProdCat/Prod[@Produto="Full-Finger Gloves, L"]')
      FROM	Prods
    
    -- Pode ser usado no where como filtro do resultado
    -- irá retornar 1 caso encontre ou 0 caso não encontre
    SELECT *
      FROM	Prods
     WHERE ColunaXML.exist('/TabelaProdutos/ProdCat/SubProdCat/Prod[@Produto="Full-Finger Gloves, L"]') = 1
    
    -- Método Nodes()
    
    -- Retorna 1 linha para cada categoria existente.
    SELECT Pro.Cat.query('.')
      FROM	Prods
     CROSS APPLY ColunaXML.nodes('/TabelaProdutos/ProdCat') AS Pro(Cat)
    
    -- Retorna 1 linha para cada SubCategoria existente.
    SELECT Pro.Cat.query('.')
      FROM	Prods
     CROSS APPLY ColunaXML.nodes('/TabelaProdutos/ProdCat/SubProdCat') AS Pro(Cat)
    
    -- Retorna todas as Categorias
    SELECT Pro.Cat.value('@Categoria', 'nVarChar(80)') AS Categorias
      FROM	Prods
     CROSS APPLY ColunaXML.nodes('/TabelaProdutos/ProdCat') AS Pro(Cat)
    

    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]

    • Marcado como Resposta Harley Araujo sexta-feira, 9 de novembro de 2012 13:41
    terça-feira, 23 de outubro de 2012 12:29
  • Harley,

    parece-me que as duas Threads que você abriu recentemente (esta e a http://social.technet.microsoft.com/Forums/pt-BR/520/thread/2573f816-9746-4fbe-9885-1b9ac0282df1) referem-se ao mesmo problema.

    Se for isso mesmo, por favor, encerre uma e continue somente com a outra.

    Do contrário, pode manter as duas. Obrigado.


    Roberson Ferreira - Database Developer
    Acesse: www.robersonferreira.com.br
    Email: contato@robersonferreira.com.br

    Se esta sugestão for útil, por favor, classifique-a como útil.
    Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

    terça-feira, 23 de outubro de 2012 15:42
    Moderador