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>

Gerando XML no SQL Server – Arte do FOR XML PATH

Partimos para o FOR XML PATH, que torna a Arte do FOR XML extremamente flexível, permitindo que o FOR XML PATH seja adequado para suprir qualquer necessidade de geração de XML por meio de T-SQL.

Vamos para o primeiro exemplo desta facilidade, onde criaremos um XML simples a partir de uma consulta, na qual as colunas relacionadas serão os nós filhos:

SELECT TOP (3)
	  FirstName
	, LastName
FROM
	Person.Contact
FOR XML PATH('Contacts'), ROOT('Person')

<Person>
  <Contacts>
    <FirstName>Gustavo</FirstName>
    <LastName>Achong</LastName>
  </Contacts>
  <Contacts>
    <FirstName>Catherine</FirstName>
    <LastName>Abel</LastName>
  </Contacts>
  <Contacts>
    <FirstName>Kim</FirstName>
    <LastName>Abercrombie</LastName>
  </Contacts>
</Person>

E com uma pequena variação (@), temos ao invés de nós filhos, atributos:

SELECT TOP (3)
	  FirstName AS [@FirstName]
	, LastName AS [@LastName]
FROM
	Person.Contact
FOR XML PATH('Contacts'), ROOT('Person')

<Person>
  <Contacts FirstName="Gustavo" LastName="Achong" />
  <Contacts FirstName="Catherine" LastName="Abel" />
  <Contacts FirstName="Kim" LastName="Abercrombie" />
</Person>

Para quem já percebeu com os exemplos anteriores, com o FOR XML PATH temos uma interpretação simplificada do XPATH na geração dos nós e atributos, assim podemos criar facilmente caminhos com atributos, elementos e até comentários de forma totalmente livre:

SELECT TOP (3)
	  LastName AS [Name/@LastName] -- Atributo
	, FirstName AS [Name/text()] -- Texto
	, EmailAddress AS [Contact/Email/text()] -- Texto
	, Phone AS [Contact/Phone] -- Texto, sem 'text()'
	, ModifiedDate AS [comment()] -- Comentário
FROM
	Person.Contact
FOR XML PATH('Contacts'), ROOT('Person')

<Person>
  <Contacts>
    <Name LastName="Achong">Gustavo</Name>
    <Contact>
      <Email>gustavo0@adventure-works.com</Email>
      <Phone>398-555-0132</Phone>
    </Contact>
    <!--2005-05-16T16:33:33.060-->
  </Contacts>
  <Contacts>
    <Name LastName="Abel">Catherine</Name>
    <Contact>
      <Email>catherine0@adventure-works.com</Email>
      <Phone>747-555-0171</Phone>
    </Contact>
    <!--2005-05-16T16:33:33.077-->
  </Contacts>
  <Contacts>
    <Name LastName="Abercrombie">Kim</Name>
    <Contact>
      <Email>kim2@adventure-works.com</Email>
      <Phone>334-555-0137</Phone>
    </Contact>
    <!--2005-05-16T16:33:33.077-->
  </Contacts>
</Person>

Dai iniciam os desafios para gerar uma HTML TABLE com várias colunas no FOR XML PATH, sendo as próximas consultas não obtendo o resultado esperado:

Erro 1: Representação de duas colunas no mesmo path gera texto concatenado:

SELECT TOP (3)
	  FirstName AS [TD]
	  ,LastName AS [TD]
FROM
	Person.Contact
FOR XML PATH('TR'), ROOT('TABLE')

<TABLE>
  <TR>
    <TD>GustavoAchong</TD>
  </TR>
  <TR>
    <TD>CatherineAbel</TD>
  </TR>
  <TR>
    <TD>KimAbercrombie</TD>
  </TR>
</TABLE>

Erro 2: Texto com nós XML é transformado (encode):

SELECT TOP (3)
	  '<TD>' + FirstName + '</TD>',
	  '<TD>' + LastName + '</TD>'
FROM
	Person.Contact
FOR XML PATH('TR'), ROOT('TABLE')

<TABLE>
  <TR>&lt;TD&gt;Gustavo&lt;/TD&gt;&lt;TD&gt;Achong&lt;/TD&gt;</TR>
  <TR>&lt;TD&gt;Catherine&lt;/TD&gt;&lt;TD&gt;Abel&lt;/TD&gt;</TR>
  <TR>&lt;TD&gt;Kim&lt;/TD&gt;&lt;TD&gt;Abercrombie&lt;/TD&gt;</TR>
</TABLE>

Solução: Para solução deste problema, temos as seguintes alternativas:

-- Gerando um path vazio, exigindo do SQL Server a criação de dois nós separados
SELECT TOP (3)
	  FirstName AS [TD]
	, NULL AS [text()]
	, LastName AS [TD]
FROM
	Person.Contact
FOR XML PATH('TR'), ROOT('TABLE')

-- Gerando o XML por conversão de dados
SELECT TOP (3)
	  CAST('<TD>' + FirstName + '</TD><TD>' + LastName + '</TD>' AS XML)
FROM
	Person.Contact
FOR XML PATH('TR'), ROOT('TABLE')

-- Gerando o XML por subconsulta
SELECT TOP (3)
	  (SELECT FirstName AS [text()] FOR XML PATH('TD'), TYPE)
	, (SELECT LastName AS [text()] FOR XML PATH('TD'), TYPE)
FROM
	Person.Contact
FOR XML PATH('TR'), ROOT('TABLE')

<TABLE>
  <TR>
    <TD>Gustavo</TD>
    <TD>Achong</TD>
  </TR>
  <TR>
    <TD>Catherine</TD>
    <TD>Abel</TD>
  </TR>
  <TR>
    <TD>Kim</TD>
    <TD>Abercrombie</TD>
  </TR>
</TABLE>