Iniciando com XQuery – Resumo

A possibilidade de utilizar XQuery no SQL Server permite ler, gerar, manipular XML de uma forma simples e muito prática, sem a necessidade de depender de recursos externos ao SQL Server para tratar dados deste tipo.

Ano passado escrevi quatro artigos que oferecem uma introdução de como utilizar XQuery no SQL Server.

Iniciando com XQuery – Lendo XML no SQL Server

Iniciando com XQuery – Gerando XML no SQL Server

Iniciando com XQuery – Modificando XML no SQL Server

Iniciando com XQuery – Namespaces têm solução!

Anúncios

Iniciando com XQuery – Namespaces têm solução!

É muito comum em cenário com XML ter situações que no cabeçalho do arquivo há “xmlns” e nós com prefixos “xsi:”, “xsd:” e entre outros, deixando o DBA ou desenvolvedor responsável pelas consultas confuso com o fato de consultas com simples XQueries não trazerem nenhum resultado.

Mesmo que o comportamento “padrão” nestes cenários na maioria das vezes é dar um REPLACE no cabeçalho do XML, vamos aprender como resolver esta situação de forma prática.

O XML que usarei para o exemplo possui um namespace bem comum para dados oriundos de aplicações como o http://tempuri.org/, que é o namespace padrão do exemplo (URL declarada com xmlns sem qualquer sufixo):

DECLARE @X XML

SET @X = '
<ArrayOfDsItem xmlns="http://tempuri.org/" xmlns:ns2="http://tempuri2.org/">
  <DsItem Code="1" Value="ACRE" Value2="AC">
    <ns2:Status>Não existe</ns2:Status>
  </DsItem>
  <DsItem Code="5" Value="BAHIA" Value2="BA" />
  <DsItem Code="11" Value="MATO GROSSO" Value2="MT" />
  <DsItem Code="13" Value="MINAS GERAIS" Value2="MG" />
  <DsItem Code="19" Value="RIO DE JANEIRO" Value2="RJ" />
  <DsItem Code="21" Value="RIO GRANDE DO SUL" Value2="RS" />
  <DsItem Code="24" Value="SÃO PAULO" Value2="SP" />
</ArrayOfDsItem>'

Visto que estamos tratando de um namespace padrão, podemos determinar ou por T-SQL ou por XQuery, que este é o bendito namespace de todo o XML:

-- Definindo o namespace padrão
;WITH XMLNAMESPACES (DEFAULT 'http://tempuri.org/')
SELECT
	E.value('@Value', 'varchar(25)') AS Estado,
	E.value('@Value2', 'char(2)') AS Sigla
FROM @X.nodes('//ArrayOfDsItem/DsItem') AS N(E)

SELECT
	E.value('@Value', 'varchar(25)') AS Estado,
	E.value('@Value2', 'char(2)') AS Sigla
FROM @X.nodes('
	declare default element namespace "http://tempuri.org/";
	//ArrayOfDsItem/DsItem
') AS N(E)

Ou podemos criar um namespace sinônimo (ns), o que afetará um pouco a nossa XQuery:

-- Definindo um sinonimo para namespace padrão (ns)
;WITH XMLNAMESPACES ('http://tempuri.org/' AS ns)
SELECT
	E.value('@Value', 'varchar(25)') AS Estado,
	E.value('@Value2', 'char(2)') AS Sigla
FROM @X.nodes('//ns:ArrayOfDsItem/ns:DsItem') AS N(E)

SELECT
	E.value('@Value', 'varchar(25)') AS Estado,
	E.value('@Value2', 'char(2)') AS Sigla
FROM @X.nodes('
	declare namespace ns = "http://tempuri.org/";
	//ns:ArrayOfDsItem/ns:DsItem
') AS N(E)

Mas e agora, tenho dois ou mais namespaces no XML, o que podemos fazer? Podemos optar por qualquer uma das práticas anteriores (definindo um sinônimo ou definindo o namespace padrão) e mais os sinônimos para os namespaces secundários:

-- Definindo o namespace padrão e o sinonimo para o secundário (ns2)
;WITH XMLNAMESPACES (
	DEFAULT 'http://tempuri.org/',
	'http://tempuri2.org/' AS ns2
)
SELECT
	E.value('@Value', 'varchar(25)') AS Estado,
	E.value('@Value2', 'char(2)') AS Sigla,
	E.value('ns2:Status[1]', 'varchar(25)') AS Comentario
FROM @X.nodes('//ArrayOfDsItem/DsItem') AS N(E)

SELECT
E.value('@Value', 'varchar(25)') AS Estado,
E.value('@Value2', 'char(2)') AS Sigla,
E.value('
	declare namespace ns2 = "http://tempuri2.org/";
	ns2:Status[1]
' , 'varchar(25)') AS Comentario
FROM @X.nodes('
	declare default element namespace "http://tempuri.org/";
	//ArrayOfDsItem/DsItem
') AS N(E)

-- Definindo sinonimos para namespace padrão (ns) e para o secundário (ns2)
;WITH XMLNAMESPACES (
	'http://tempuri.org/' AS ns,
	'http://tempuri2.org/' AS ns2
)
SELECT
	E.value('@Value', 'varchar(25)') AS Estado,
	E.value('@Value2', 'char(2)') AS Sigla,
	E.value('ns2:Status[1]', 'varchar(25)') AS Comentario
FROM @X.nodes('//ns:ArrayOfDsItem/ns:DsItem') AS N(E)

SELECT
E.value('@Value', 'varchar(25)') AS Estado,
E.value('@Value2', 'char(2)') AS Sigla,
E.value('
	declare namespace ns2 = "http://tempuri2.org/";
	ns2:Status[1]
' , 'varchar(25)') AS Comentario
FROM @X.nodes('
	declare namespace ns = "http://tempuri.org/";
	//ns:ArrayOfDsItem/ns:DsItem
') AS N(E)

Ficou legal, não ficou? Mas nada te impede de colocar alguns REPLACEs na sua query e ser feliz…

No próximo artigo veremos com iniciar um pequeno caos com o FOR XML e Namespaces…

Iniciando com XQuery – Modificando XML no SQL Server

E ai pessoas, estou de volta esta semana com mais um artigo sobre a utilização do XQuery no SQL Server, sendo este já o terceiro artigo desta série, mostrando como é possível fazer leituras de XML com SQL, gerar XML com XQuery e agora, modificando XML com XQuery.

Para executar modificações em XML com XQuery, precisaremos de dois elementos básicos, sendo o primeiro o método modify() da variável/coluna do tipo XML, e o segundo elemento, no caso de variáveis será o SET, conforme a sintaxe:

SET @VariavelXml.modify('... XQuery ...')

E no caso de colunas será o SET do UPDATE:

UPDATE Tabela
SET ColunaXml.modify('... XQuery ...')

Para o nosso primeiro exemplo de XQuery para o método modify, partimos para a alteração do valor de um determinado atributo:

DECLARE @X XML
SET @X = '
	<Estoque>
		<Armario>
			<Produto Id="1">Nike</Produto>
			<Produto Id="2">Puma</Produto>
			<Produto Id="3">Freeway</Produto>
		</Armario>
	</Estoque>'

-- Modificando Id 1 por 4
SET @X.modify('replace value of (//Produto)[1]/@Id with ("4")')

--Resultado:
--<Estoque>
--	<Armario>
--		<Produto Id="4">Nike</Produto>
--		<Produto Id="2">Puma</Produto>
--		<Produto Id="3">Freeway</Produto>
--	</Armario>
--</Estoque>

E como alterar o texto de determinado elemento ou nó do XML:

-- Modificando Nike por Adidas
SET @X.modify('replace value of (//Produto/text())[1] with ("Adidas")')

--Resultado:
--<Estoque>
--	<Armario>
--		<Produto Id="4">Adidas</Produto>
--		<Produto Id="2">Puma</Produto>
--		<Produto Id="3">Freeway</Produto>
--	</Armario>
--</Estoque>

Utilizando os conceitos de geração de XML por XQuery, vamos criar um novo elemento em nosso XML depois de um determinado nó:

-- Adicionando All Star depois de Freeway
SET @X.modify('insert <Produto Id="1">All Star</Produto> after (//Produto)[3]')

--Resultado:
--<Estoque>
--	<Armario>
--		<Produto Id="4">Adidas</Produto>
--		<Produto Id="2">Puma</Produto>
--		<Produto Id="3">Freeway</Produto>
--		<Produto Id="1">All Star</Produto>
--	</Armario>
--</Estoque>

E de forma simples, removemos um elemento ou nó:

-- Deletando Puma
SET @X.modify('delete (//Produto)[2]') 

--Resultado:
--<Estoque>
--	<Armario>
--		<Produto Id="4">Adidas</Produto>
--		<Produto Id="3">Freeway</Produto>
--		<Produto Id="1">All Star</Produto>
--	</Armario>
--</Estoque>

Criamos um novo elemento ou nó em nosso XML antes de um determinado nó:

-- Adicionando Nike antes de Adidas
SET @X.modify('insert <Produto Id="5">Nike</Produto> before (//Produto)[1]')

--Resultado:
--<Estoque>
--	<Armario>
--		<Produto Id="5">Nike</Produto>
--		<Produto Id="4">Adidas</Produto>
--		<Produto Id="3">Freeway</Produto>
--		<Produto Id="1">All Star</Produto>
--	</Armario>
--</Estoque>

Criamos como primeiro elemento de um nó:

-- Adicionando Reebok na primeira posição
SET @X.modify('insert <Produto Id="1">Reebok</Produto> as first into (//Armario)[1]')

--Resultado:
--<Estoque>
--	<Armario>
--		<Produto Id="1">Reebok</Produto>
--		<Produto Id="5">Nike</Produto>
--		<Produto Id="4">Adidas</Produto>
--		<Produto Id="3">Freeway</Produto>
--		<Produto Id="1">All Star</Produto>
--	</Armario>
--</Estoque>

Ou como último elemento, e com um atributo cujo o valor será a função MAX de um determinado caminho + 1 (já complicando um pouco):

-- Adicionando Olympus na última posição com o valor do Id maior + 1
SET @X.modify('insert <Produto Id="{max((//Produto)/@Id) + 1}">Olympus</Produto> as last into (//Armario)[1]')

--Resultado:
--<Estoque>
--	<Armario>
--		<Produto Id="1">Reebok</Produto>
--		<Produto Id="5">Nike</Produto>
--		<Produto Id="4">Adidas</Produto>
--		<Produto Id="3">Freeway</Produto>
--		<Produto Id="1">All Star</Produto>
--		<Produto Id="6">Olympus</Produto>
--	</Armario>
--</Estoque>

Modificando o atributo do primeiro elemento segundo uma determinada regra:

-- Modificando o Id de Reebok pelo valor do Id maior + 1
SET @X.modify('
	replace value of (//Produto)[1]/@Id
		with (
			let $i := max((//Produto)/@Id)
			return $i + 1
		)')

--Resultado:
--<Estoque>
--	<Armario>
--		<Produto Id="7">Reebok</Produto>
--		<Produto Id="5">Nike</Produto>
--		<Produto Id="4">Adidas</Produto>
--		<Produto Id="3">Freeway</Produto>
--		<Produto Id="1">All Star</Produto>
--		<Produto Id="6">Olympus</Produto>
--	</Armario>
--</Estoque>

Reordenando os elemento, utilizando o conceito do artigo de criação de XML por XQuery e um “for” com “order by”:

-- Gerando um novo XML para ordenação dos Ids
SET @X = @X.query('
	<Estoque>
		<Armario>
			{
				for $p in //Produto
				order by $p/@Id
					return $p
			}
		</Armario>
	</Estoque>')

--Resultado:
--<Estoque>
--	<Armario>
--		<Produto Id="1">All Star</Produto>
--		<Produto Id="3">Freeway</Produto>
--		<Produto Id="4">Adidas</Produto>
--		<Produto Id="5">Nike</Produto>
--		<Produto Id="6">Olympus</Produto>
--		<Produto Id="7">Reebok</Produto>
--	</Armario>
--</Estoque>

Por fim, inserindo um elemento antes de um determinado elemento que se situa a posição [2]:

-- Adicionando Puma antes de Freeway
SET @X.modify('insert <Produto Id="2">Puma</Produto> before (//Produto)[2]')

--Resultado:
--<Estoque>
--	<Armario>
--		<Produto Id="1">All Star</Produto>
--		<Produto Id="2">Puma</Produto>
--		<Produto Id="3">Freeway</Produto>
--		<Produto Id="4">Adidas</Produto>
--		<Produto Id="5">Nike</Produto>
--		<Produto Id="6">Olympus</Produto>
--		<Produto Id="7">Reebok</Produto>
--	</Armario>
--</Estoque>

Espero que tenham gostado, pois acabei elaborando uma quantidade relativamente grande de exemplos para demonstrar as principais formas de se utilizar XQuery para modificar um XML no SQL Server, tentando evitar ao máximo de complexidade.

Artigos relacionados:

Iniciando com XQuery – Lendo XML no SQL Server

Iniciando com XQuery – Gerando XML no SQL Server

Iniciando com XQuery – Gerando XML no SQL Server

Além das artes de geração de XML pela clausula FOR XML e por técnicas menos renomadas com CAST/CONVERT, ainda temos uma participação não muito conhecida, mas demonstra o potencial do XQuery que não se limita a leitura e a modificação de dados XML.

Para inicio de conversa, precisaremos de uma variável XML não nula (em cenários reais, é comum utilizar variáveis ou colunas com dados XML, tanto para geração ou manipulação destes dados para geração de XML em um novo formato), e de forma simples gerar alguns XMLs com XQuery:

DECLARE @X XML
SET @X = ''

-- Definição de XML para gerar XML
SELECT @X.query('
	<Estoque>
		<Armario>
			<Produto Id="1">Nike</Produto>
			<Produto Id="2">Puma</Produto>
			<Produto Id="3">Freeway</Produto>
		</Armario>
	</Estoque>
')

-- Definição de elementos para gerar XML
SELECT @X.query('
	element Estoque {
		element Armario {
			element Produto {
				text {"Nike"},
				attribute Id { 1 }
			},
			element Produto {
				text {"Puma"},
				attribute Id { 2 }
			},
			element Produto {
				text {"Freeway"},
				attribute Id { 3 }
			}
		}
	}
')

-- Definição mista para gerar XML
SELECT @X.query('
	element Estoque {
		<Armario>
			<Produto Id="1">Nike</Produto>
			{
				element Produto {
					text {"Puma"},
					attribute Id { 2 }
				}
			}
			<Produto Id="3">Freeway</Produto>
		</Armario>
	}
')

--Resultado:

--<Estoque>
--  <Armario>
--    <Produto Id="1">Nike</Produto>
--    <Produto Id="2">Puma</Produto>
--    <Produto Id="3">Freeway</Produto>
--  </Armario>
--</Estoque>

Também demonstrando a possibilidade de utilizar variáveis:

DECLARE @X XML
SET @X = ''

DECLARE @I INT
SET @I = 10

-- Recuperando uma variável
SELECT @X.query('sql:variable("@I")')

--Resultado:
--10

-- Recuperando uma variável para geração de XML
SELECT @X.query('
	<Tipo>
		{sql:variable("@I")}
	</Tipo>')

SELECT @X.query('element Tipo {sql:variable("@I")}')

--Resultado:
--<Tipo>10</Tipo>

E colunas de uma dada consulta:

DECLARE @X XML
SET @X = ''

-- Recuperando uma coluna
SELECT TOP 3
	@X.query('sql:column("name")')
FROM sys.types

--Resultado:
--bigint
--binary
--bit

-- Recuperando uma coluna para geração de XML
SELECT TOP 3
	@X.query('
	<Tipo>
		{sql:column("name")}
	</Tipo>')
FROM sys.types

SELECT TOP 3
	@X.query('element Tipo {sql:column("name")}')
FROM sys.types

--Resultado:
--<Tipo>bigint</Tipo>
--<Tipo>binary</Tipo>
--<Tipo>bit</Tipo>

Iniciando com XQuery – Lendo XML no SQL Server

Uma das vantagens de se trabalhar com tipo XML no SQL Server é a facilidade de recuperar partículas de dados no formato XML, por meio de consultas, denominadas XQuery.

Tendo uma variável XML, vamos caminhar para as primeiras consultas com XQuery:

DECLARE @XML XML
SET @XML = '
<note>
	<id>1</id>
	<to mail="maria@email.com">maria</to>
	<from mail="ana@email.com">ana</from>
	<heading>Reminder</heading>
	<body>Happy Birthday</body>
	<date>2011-01-01</date>
</note>
<note>
	<id>2</id>
	<to mail="ana@email.com">ana</to>
	<from mail="maria@email.com">maria</from>
	<heading>RE: Reminder</heading>
	<body>Thanks</body>
	<date>2011-01-01</date>
</note>
<note>
	<id>3</id>
	<to mail="maria@email.com">maria</to>
	<from mail="ana@email.com">ana</from>
	<heading>RE: RE: Reminder</heading>
	<body>Party?</body>
	<date>2011-01-02</date>
</note>
'

Método “query()”

Para recuperar certas partículas de um XML, mantendo ainda este formato, se torna facilmente praticável com o método query. Para uma simples demonstração (não exibindo ainda todo potencial deste método), vamos recuperar informações de um determinado caminho (XPath):

--1. Recuperando XML de um caminho simples
SELECT @XML.query('note/to')

--Resultado:
--<to mail="maria@email.com">maria</to><to mail="ana@email.com">ana</to><to mail="maria@email.com">maria</to>

--

--2. Recuperando XML de um caminho específico
-- , no caso, o primeiro caso da tag "note"
SELECT @XML.query('note[1]/to')

--Resultado:
--<to mail="maria@email.com">maria</to>

--

--3. Recuperando XML de um caminho específico
-- , onde from possui o texto "maria"
SELECT @XML.query('note[from=''maria'']/to')

--Resultado:
--<to mail="ana@email.com">ana</to>

--

--4. O mesmo caso anterior, especificando o termo "text()"
SELECT @XML.query('note[from/text()=''maria'']/to')

--Resultado:
--<to mail="ana@email.com">ana</to>

--

--5. O mesmo caso anterior, filtrando por um atributo
SELECT @XML.query('note[from/@mail=''maria@email.com'']/to')

--Resultado:
--<to mail="ana@email.com">ana</to>

Método “value()”

O método value permite recuperar valores de caminhos bem específicos de um XML:

--1. Recuperando o valor de um "id", do tipo INT
SELECT @XML.value('(note/id)[1]', 'int')

--Resultado:
--1

--

--2. Recuperando o valor de um atributo mail, do tipo "varchar"
SELECT @XML.value('(note/to)[1]/@mail', 'varchar(45)')

--Resultado:
--maria@email.com

--

--3. Recuperando o valor de um atributo mail, do tipo "varchar"
SELECT @XML.value('(note[from/@mail=''maria@email.com'']/to)[1]/@mail', 'varchar(45)')

--Resultado:
--ana@email.com

--

--4. Recuperando datas de uma caminho específico
SELECT @XML.value('(note/date)[1]', 'date')
UNION ALL
SELECT @XML.value('(note/date)[3]', 'date')
UNION ALL
SELECT @XML.value('(note/date)[5]', 'date')

--Resultado:
--2011-01-01
--2011-01-02
--NULL

Método “exist()”

O método exist verifica se o caminho informado existe:

--1. Verificando a existência de um caminho específico
SELECT @XML.exist('note/date')

--Resultado
--1 (Existe)

--

--2. Verificando a existência de um caminho específico
SELECT @XML.exist('(note/date)[3]')

--Resultado
--1 (Existe)

--

--3. Verificando a existência de um caminho específico
SELECT @XML.exist('(note/date)[5]')

--Resultado
--0 (Não existe)

Método “nodes()”

O método nodes permite executar consultas sobre um XML com apoio dos métodos anteriores:

--1. Consulta simples
SELECT C.query('.')
FROM @XML.nodes('note/to') AS T(C)

--Resultado
--<to mail="maria@email.com">maria</to>
--<to mail="ana@email.com">ana</to>
--<to mail="maria@email.com">maria</to>

--

--2. Consulta com mais critérios
SELECT
 C.value('id[1]', 'int'),
 C.value('date[1]', 'date'),
 C.value('from[1]/@mail', 'varchar(25)'),
 C.query('to')

FROM @XML.nodes('note') AS T(C)

WHERE C.exist('to') = 1

--Resultado
--1	2011-01-01	ana@email.com	<to mail="maria@email.com">maria</to>
--2	2011-01-01	maria@email.com	<to mail="ana@email.com">ana</to>
--3	2011-01-02	ana@email.com	<to mail="maria@email.com">maria</to>