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]
