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
A categoria de artigos que mais me deu trabalho, pois o WordPress apagou várias vezes os exemplos dos XML na hora de publicar, mas um dos assuntos que mais gosto de tratar no SQL Server.
Gerando XML no SQL Server – Arte do FOR XML AUTO
Gerando XML no SQL Server – Arte do FOR XML EXPLICIT
É 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…
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:
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>
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>
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><TD>Gustavo</TD><TD>Achong</TD></TR> <TR><TD>Catherine</TD><TD>Abel</TD></TR> <TR><TD>Kim</TD><TD>Abercrombie</TD></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>
Seguindo com a Arte do FOR XML, nos deparamos com o FOR XML RAW, que não se diferencia muito do FOR XML AUTO, mas aproveitamos este artigo para demonstrar outros aspectos interessantes tanto do FOR XML RAW quando do FOR XML AUTO (campos nulos, tipos binários e schemas).
Como primeiro passo, temos uma consulta RAW básica:
SELECT TOP (5) FirstName , LastName FROM Person.Contact FOR XML RAW
<row FirstName="Gustavo" LastName="Achong" /> <row FirstName="Catherine" LastName="Abel" /> <row FirstName="Kim" LastName="Abercrombie" /> <row FirstName="Humberto" LastName="Acevedo" /> <row FirstName="Pilar" LastName="Ackerman" />
Seguida de outra um pouco mais customizada:
SELECT TOP (5)
[Name] = FirstName + ', ' + LastName
FROM
Person.Contact
FOR XML RAW('Contact')
, ELEMENTS
, ROOT('Person')
<Person>
<Contact>
<Name>Gustavo, Achong</Name>
</Contact>
<Contact>
<Name>Catherine, Abel</Name>
</Contact>
<Contact>
<Name>Kim, Abercrombie</Name>
</Contact>
<Contact>
<Name>Humberto, Acevedo</Name>
</Contact>
<Contact>
<Name>Pilar, Ackerman</Name>
</Contact>
</Person>
Uma questão importante de se verificar é o comportamento da geração do XML (AUTO e RAW) quando tratamos de campos NULL, assim temos a alternativa de não gerar os nós nulos (comportamento padrão):
SELECT TOP (5)
[Name] = FirstName + ', ' + LastName
, MiddleName
FROM
Person.Contact
FOR XML RAW('Contact')
, ELEMENTS ABSENT
, ROOT('Person')
<Person>
<Contact>
<Name>Gustavo, Achong</Name>
</Contact>
<Contact>
<Name>Catherine, Abel</Name>
<MiddleName>R.</MiddleName>
</Contact>
<Contact>
<Name>Kim, Abercrombie</Name>
</Contact>
<Contact>
<Name>Humberto, Acevedo</Name>
</Contact>
<Contact>
<Name>Pilar, Ackerman</Name>
</Contact>
</Person>
Ou utilizar XSINIL para que exista uma representação destes nós nulos:
SELECT TOP (5)
[Name] = FirstName + ', ' + LastName
, MiddleName
FROM
Person.Contact
FOR XML RAW('Contact')
, ELEMENTS XSINIL
, ROOT('Person')
<Person xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Contact>
<Name>Gustavo, Achong</Name>
<MiddleName xsi:nil="true" />
</Contact>
<Contact>
<Name>Catherine, Abel</Name>
<MiddleName>R.</MiddleName>
</Contact>
<Contact>
<Name>Kim, Abercrombie</Name>
<MiddleName xsi:nil="true" />
</Contact>
<Contact>
<Name>Humberto, Acevedo</Name>
<MiddleName xsi:nil="true" />
</Contact>
<Contact>
<Name>Pilar, Ackerman</Name>
<MiddleName xsi:nil="true" />
</Contact>
</Person>
Campos binários também podem ser problemáticos, exemplo o seguinte erro:
SELECT TOP (1) ProductPhotoID , ThumbNailPhoto FROM Production.ProductPhoto FOR XML RAW
Msg 6829, Level 16, State 1, Line 1
FOR XML EXPLICIT and RAW modes currently do not support addressing binary data as URLs in column ‘ThumbNailPhoto’. Remove the column, or use the BINARY BASE64 mode, or create the URL directly using the ‘dbobject/TABLE[@PK1="V1"]/@COLUMN’ syntax.
Assim, utilizamos das declarações BINARY BASE64 para tratar este problema:
SELECT TOP (1) ProductPhotoID , ThumbNailPhoto FROM Production.ProductPhoto FOR XML RAW , BINARY BASE64
<row ProductPhotoID="1" ThumbNailPhoto="R0lGODlhUAA..." />
Por fim, temos a necessidade de gerar Schemas ou alguma forma de representação dos tipos de dados que estão presentes no XML, onde podemos utilizar do XMLDATA para uma representação simples dos dados:
SELECT TOP (1) [Name] = FirstName + ', ' + LastName , [Email] = EmailAddress FROM Person.Contact FOR XML RAW , XMLDATA
<Schema name="Schema2" xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes">
<ElementType name="row" content="empty" model="closed">
<AttributeType name="Name" dt:type="string" />
<AttributeType name="Email" dt:type="string" />
<attribute type="Name" />
<attribute type="Email" />
</ElementType>
</Schema>
<row xmlns="x-schema:#Schema2" Name="Gustavo, Achong" Email="gustavo0@adventure-works.com" />
Ou XMLSCHEMA, para a declaração mais apropriada com Xml Schema definition language (XSD):
SELECT TOP (1)
[Name] = FirstName + ', ' + LastName
, [Email] = EmailAddress
FROM
Person.Contact
FOR XML RAW('Contact')
, ROOT('Person')
, XMLSCHEMA('PersonContact')
<Person>
<xsd:schema targetNamespace="PersonContact" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" elementFormDefault="qualified">
<xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
<xsd:element name="Contact">
<xsd:complexType>
<xsd:attribute name="Name" use="required">
<xsd:simpleType>
<xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreNonSpace IgnoreKanaType IgnoreWidth" sqltypes:sqlCollationVersion="2">
<xsd:maxLength value="102" />
</xsd:restriction>
</xsd:simpleType>
</xsd:attribute>
<xsd:attribute name="Email">
<xsd:simpleType>
<xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreNonSpace IgnoreKanaType IgnoreWidth" sqltypes:sqlCollationVersion="2">
<xsd:maxLength value="50" />
</xsd:restriction>
</xsd:simpleType>
</xsd:attribute>
</xsd:complexType>
</xsd:element>
</xsd:schema>
<Contact xmlns="PersonContact" Name="Gustavo, Achong" Email="gustavo0@adventure-works.com" />
</Person>
Continuando a série Arte do FOR XML, partiremos em busca do EXPLICIT, que permite a criação de XML que obedecem a estruturas hierárquicas (pai-filho) de uma forma pouco descomplicada.
A arte do FOR XML EXPLICT começa com a definição de duas colunas de ordem estrutural, a Tag, para identificar o nó que estamos trabalhando, e Parent, que define qual seria o nó pai. Após definias as colunas estruturais, vamos para a geração do nó, no qual identificamos primeiramente o nome do nó, seguido do numero de identificação do nó (Tag) e o atributo a ser criado, conforme a sintaxe:
[NomeDoNó!Tag!NomeDoAtributo]
Exemplo:
SELECT TOP (2)
Tag = 1
, Parent = NULL
, PC.Name AS [Categoria!1!Nome] -- Definição do nó
FROM
Production.ProductCategory AS PC
FOR XML EXPLICIT
, ROOT('Estoque') -- Nó raiz
<Estoque> <Categoria Nome="Accessories" /> <Categoria Nome="Bikes" /> </Estoque> -- Nó raiz
Para um cenário com mais de um nó, se faz necessária à utilização de UNION ALL entre o comando que constrói o primeiro nó e o comando que constrói o segundo nó:
SELECT TOP (2)
Tag = 1 -- Identificação do primeiro nó
, Parent = NULL
, PC.Name AS [Categoria1!1!Nome]
, NULL AS [Categoria2!2!Nome] -- Declaração do segundo nó
FROM
Production.ProductCategory AS PC
UNION ALL
SELECT TOP (2)
Tag = 2 -- Identificação do segundo nó
, Parent = NULL
, NULL AS [Categoria1!1!Nome] -- Repeticação do primeiro nó
, PC.Name AS [Categoria2!2!Nome]
FROM
Production.ProductCategory AS PC
FOR XML EXPLICIT
, ROOT('Estoque')
<Estoque> <Categoria1 Nome="Accessories" /> <Categoria1 Nome="Bikes" /> <Categoria2 Nome="Accessories" /> <Categoria2 Nome="Bikes" /> </Estoque>
Para questões de hierarquia, o nó filho deve ter definida sua relação com o nó pai, através da coluna estrutural Parent:
SELECT
Tag = 1 -- Identificação do primeiro nó
, Parent = NULL
, Pai.Name AS [Pai!1!Name]
, NULL AS [Filho!2!Name]
FROM
Production.ProductCategory AS Pai
UNION ALL
SELECT TOP (20)
Tag = 2
, Parent = 1 -- Definição de hierarquia
, Pai.Name AS [Pai!1!Name]
, Filho.Name AS [Filho!2!Name]
FROM
Production.ProductCategory AS Pai
INNER JOIN Production.ProductSubcategory AS Filho
ON Pai.ProductCategoryID = Filho.ProductCategoryID
FOR XML EXPLICIT
, ROOT('Estoque')
<Estoque>
<Pai Name="Accessories" />
<Pai Name="Bikes" />
<Pai Name="Clothing" />
<Pai Name="Components">
<Filho Name="Bike Racks" />
<Filho Name="Bike Stands" />
<Filho Name="Bottles and Cages" />
<Filho Name="Cleaners" />
<Filho Name="Fenders" />
<Filho Name="Helmets" />
<Filho Name="Hydration Packs" />
<Filho Name="Lights" />
<Filho Name="Locks" />
<Filho Name="Panniers" />
<Filho Name="Pumps" />
<Filho Name="Tires and Tubes" />
<Filho Name="Mountain Bikes" />
<Filho Name="Road Bikes" />
<Filho Name="Touring Bikes" />
<Filho Name="Bib-Shorts" />
<Filho Name="Caps" />
<Filho Name="Gloves" />
<Filho Name="Jerseys" />
<Filho Name="Shorts" />
</Pai>
</Estoque>
Conforme verificamos, foi criada a relação de pai para filho, mas não foi obedecida a real relação entre os registros (o registro Components ficou erroneamente como pai de todos os registros filhos), assim é recomendado utilizar um ORDER BY sobre um atributo do pai (seguido não obrigatoriamente de uma ordenação sobre o filho), da seguinte forma:
SELECT
Tag = 1
, Parent = NULL
, Pai.Name AS [Pai!1!Name]
, NULL AS [Filho!2!Name]
FROM
Production.ProductCategory AS Pai
UNION ALL
SELECT TOP (20)
Tag = 2
, Parent = 1
, Pai.Name AS [Pai!1!Name]
, Filho.Name AS [Filho!2!Name]
FROM
Production.ProductCategory AS Pai
INNER JOIN Production.ProductSubcategory AS Filho
ON Pai.ProductCategoryID = Filho.ProductCategoryID
ORDER BY [Pai!1!Name], [Filho!2!Name] -- Ordenação da hierarquia
FOR XML EXPLICIT
, ROOT('Estoque')
<Estoque>
<Pai Name="Accessories">
<Filho Name="Bike Racks" />
<Filho Name="Bike Stands" />
<Filho Name="Bottles and Cages" />
<Filho Name="Cleaners" />
<Filho Name="Fenders" />
<Filho Name="Helmets" />
<Filho Name="Hydration Packs" />
<Filho Name="Lights" />
<Filho Name="Locks" />
<Filho Name="Panniers" />
<Filho Name="Pumps" />
<Filho Name="Tires and Tubes" />
</Pai>
<Pai Name="Bikes">
<Filho Name="Mountain Bikes" />
<Filho Name="Road Bikes" />
<Filho Name="Touring Bikes" />
</Pai>
<Pai Name="Clothing">
<Filho Name="Bib-Shorts" />
<Filho Name="Caps" />
<Filho Name="Gloves" />
<Filho Name="Jerseys" />
<Filho Name="Shorts" />
</Pai>
<Pai Name="Components" />
</Estoque>
Por fim, temos a definição de uma hierarquia com um pai de dois filhos distintos:
SELECT TOP 1
Tag = 1
, Parent = NULL -- Pai
, PC.Name AS [PC!1!Name]
, NULL AS [PSC!2!Name]
, NULL AS [PSD!3!Name]
FROM
Production.ProductCategory AS PC
UNION ALL
SELECT TOP 1
Tag = 2
, Parent = 1 -- Filho 1
, PC.Name AS [PC!1!Name]
, PSC.Name AS [PSC!2!Name]
, NULL AS [PSD!3!Name]
FROM
Production.ProductCategory AS PC
INNER JOIN Production.ProductSubcategory AS PSC
ON PC.ProductCategoryID = PSC.ProductCategoryID
UNION ALL
SELECT TOP 1
Tag = 3
, Parent = 1 -- Filho 2
, PC.Name AS [PC!1!Name]
, PSC.Name AS [PSC!2!Name]
, PSC.Name AS [PSD!3!Name]
FROM
Production.ProductCategory AS PC
INNER JOIN Production.ProductSubcategory AS PSC
ON PC.ProductCategoryID = PSC.ProductCategoryID
ORDER BY [PC!1!Name], [PSC!2!Name], [PSD!3!Name]
FOR XML EXPLICIT
, ROOT('Estoque')
<Estoque>
<PC Name="Accessories"> -- Pai
<PSC Name="Bike Racks" /> -- Filho 1
<PSD Name="Bike Racks" /> -- Filho 2
</PC>
</Estoque>
E a hierarquia de três níveis, com pai, filho e neto:
SELECT TOP 1
Tag = 1
, Parent = NULL -- Pai
, PC.Name AS [PC!1!Name]
, NULL AS [PSC!2!Name]
, NULL AS [PSD!3!Name]
FROM
Production.ProductCategory AS PC
UNION ALL
SELECT TOP 1
Tag = 2
, Parent = 1 -- Filho
, PC.Name AS [PC!1!Name]
, PSC.Name AS [PSC!2!Name]
, NULL AS [PSD!3!Name]
FROM
Production.ProductCategory AS PC
INNER JOIN Production.ProductSubcategory AS PSC
ON PC.ProductCategoryID = PSC.ProductCategoryID
UNION ALL
SELECT TOP 1
Tag = 3
, Parent = 2 -- Neto
, PC.Name AS [PC!1!Name]
, PSC.Name AS [PSC!2!Name]
, PSC.Name AS [PSD!3!Name]
FROM
Production.ProductCategory AS PC
INNER JOIN Production.ProductSubcategory AS PSC
ON PC.ProductCategoryID = PSC.ProductCategoryID
ORDER BY [PC!1!Name], [PSC!2!Name], [PSD!3!Name]
FOR XML EXPLICIT
, ROOT('Estoque')
<Estoque>
<PC Name="Accessories"> -- Pai
<PSC Name="Bike Racks"> -- Filho
<PSD Name="Bike Racks" /> -- Neto
</PSC>
</PC>
</Estoque>
Como é inevitável se falar de Service Broker sem falar em XML, vou abordar nos próximos artigos a arte de gerar XML por T-SQL, que é útil para formatar dados diretamente pelo banco de dados, principalmente no caso de criar mensagens para o Service Broker ou XML para as aplicações.
Abordarei inicialmente as quatro formas básicas de geração de XML por meio da clausula FOR XML, e de acordo com a evolução dos artigos, partiremos para as técnicas de leitura e otimização.
Neste primeiro passo conheceremos o FOR XML AUTO, que é a forma mais simples para gerar XML por meio de consultas, exemplo:
SELECT TOP (5) FirstName , LastName FROM Person.Contact FOR XML AUTO
<Person.Contact FirstName="Gustavo" LastName="Achong" /> <Person.Contact FirstName="Catherine" LastName="Abel" /> <Person.Contact FirstName="Kim" LastName="Abercrombie" /> <Person.Contact FirstName="Humberto" LastName="Acevedo" /> <Person.Contact FirstName="Pilar" LastName="Ackerman" />
Com a consulta seguinte, criaremos um nó raiz para nosso XML:
SELECT TOP (5)
FirstName
, LastName
FROM
Person.Contact
FOR XML AUTO
, ROOT('Person') -- nó raiz
<Person> -- nó raiz <Person.Contact FirstName="Gustavo" LastName="Achong" /> <Person.Contact FirstName="Catherine" LastName="Abel" /> <Person.Contact FirstName="Kim" LastName="Abercrombie" /> <Person.Contact FirstName="Humberto" LastName="Acevedo" /> <Person.Contact FirstName="Pilar" LastName="Ackerman" /> </Person>
Separando as colunas em nós:
SELECT TOP (3)
FirstName
, LastName
, EmailAddress
FROM
Person.Contact
FOR XML AUTO
, ELEMENTS -- Separando em nós
, ROOT('Person')
<Person>
<Person.Contact>
<FirstName>Gustavo</FirstName>
<LastName>Achong</LastName>
<EmailAddress>gustavo0@adventure-works.com</EmailAddress>
</Person.Contact>
<Person.Contact>
<FirstName>Catherine</FirstName>
<LastName>Abel</LastName>
<EmailAddress>catherine0@adventure-works.com</EmailAddress>
</Person.Contact>
<Person.Contact>
<FirstName>Kim</FirstName>
<LastName>Abercrombie</LastName>
<EmailAddress>kim2@adventure-works.com</EmailAddress>
</Person.Contact>
</Person>
E como utilizar alias/apelidos para definir os nomes dos nós:
SELECT TOP (5)
[Name] = FirstName + ', ' + LastName -- Alias na coluna
, [Email] = EmailAddress
FROM
Person.Contact AS [Contact] -- Alias na tabela
FOR XML AUTO
, ELEMENTS
, ROOT('Person')
<Person>
<Contact> -- Alias da tabela
<Name>Gustavo, Achong</Name> -- Alias da coluna
<Email>gustavo0@adventure-works.com</Email>
</Contact>
<Contact>
<Name>Catherine, Abel</Name>
<Email>catherine0@adventure-works.com</Email>
</Contact>
<Contact>
<Name>Kim, Abercrombie</Name>
<Email>kim2@adventure-works.com</Email>
</Contact>
<Contact>
<Name>Humberto, Acevedo</Name>
<Email>humberto0@adventure-works.com</Email>
</Contact>
<Contact>
<Name>Pilar, Ackerman</Name>
<Email>pilar1@adventure-works.com</Email>
</Contact>
</Person>
Para ficar mais complexo o cenário, a utilização de JOINs, que segundo a ordem dos elementos que forem informados no SELECT, alteraremos a ordem da “hierarquia” dos nós:
Exemplo 1: PC como pai, PSC como filho:
SELECT TOP (5)
PC.Name, -- Pai
PSC.Name -- Filho
FROM
Production.ProductCategory AS PC
INNER JOIN Production.ProductSubcategory AS PSC
ON PC.ProductCategoryID = PSC.ProductCategoryID
FOR XML AUTO
, ROOT('P1')
<P1>
<PC Name="Accessories"> -- Pai
<PSC Name="Bike Racks" /> -- Filho
<PSC Name="Bike Stands" />
<PSC Name="Bottles and Cages" />
<PSC Name="Cleaners" />
<PSC Name="Fenders" />
</PC>
</P1>
Exemplo 2: PSC como pai, PC como filho:
SELECT TOP (5)
PSC.Name, -- Pai
PC.Name -- Filho
FROM
Production.ProductCategory AS PC
INNER JOIN Production.ProductSubcategory AS PSC
ON PC.ProductCategoryID = PSC.ProductCategoryID
FOR XML AUTO
, ROOT('P1')
<P1>
<PSC Name="Bike Racks"> -- Pai
<PC Name="Accessories" /> -- Filho
</PSC>
<PSC Name="Bike Stands">
<PC Name="Accessories" />
</PSC>
<PSC Name="Bottles and Cages">
<PC Name="Accessories" />
</PSC>
<PSC Name="Cleaners">
<PC Name="Accessories" />
</PSC>
<PSC Name="Fenders">
<PC Name="Accessories" />
</PSC>
</P1>
Obs.: Veja que a ordem do JOIN não afeta em nada, pois somente foi alterada a ordem do SELECT do primeiro exemplo para o segundo.
Exemplo 3: Vários níveis:
SELECT TOP (2)
PSC.Name, -- Pai
PC.Name, -- Filho
PSC2.Name -- Neto
FROM
Production.ProductCategory AS PC
INNER JOIN Production.ProductSubcategory AS PSC
ON PC.ProductCategoryID = PSC.ProductCategoryID
INNER JOIN Production.ProductSubcategory AS PSC2
ON PSC2.ProductSubcategoryID = PSC.ProductSubcategoryID
FOR XML AUTO
, ROOT('P1')
<P1>
<PSC Name="Bike Racks"> -- Pai
<PC Name="Accessories"> -- Filho
<PSC2 Name="Bike Racks" /> -- Neto
</PC>
</PSC>
<PSC Name="Bike Stands">
<PC Name="Accessories">
<PSC2 Name="Bike Stands" />
</PC>
</PSC>
</P1>
