Aula 02 - Select for XML - exemplos

Prévia do material em texto

Sistema	
  Integrado	
  de	
  Gestão	
  –	
  Prof.	
  Ms.	
  Ricardo	
  Leme	
  
Aula	
  02	
  –	
  Select	
  for	
  XML	
  
Exportando	
  os	
  dados	
  do	
  SGBD	
  SQL	
  SERVER	
  através	
  do	
  parâmetro	
  
FOR	
  XML	
  
Observe exemplos com o parâmetro FOR XML AUTO, que é a forma mais simples para gerar XML 
por meio de consultas, exemplos: 
1 
2 
3 
4 
5 
6 
SELECT TOP (5) 
 FirstName 
 , LastName 
FROM 
 Person.Contact 
FOR XML AUTO 
1 
2 
3 
4 
5 
<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: 
1 
2 
3 
4 
5 
6 
7 
SELECT TOP (5) 
 FirstName 
 , LastName 
FROM 
 Person.Contact 
FOR XML AUTO 
 , ROOT('Person') -- nó raiz 
1 
2 
3 
4 
5 
6 
7 
<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: 
1 
2 
3 
4 
5 
6 
7 
8 
9 
SELECT TOP (3) 
 FirstName 
 , LastName 
 , EmailAddress 
FROM 
 Person.Contact 
FOR XML AUTO 
 , ELEMENTS -- Separando em nós 
 , ROOT('Person') 
1 
2 
3 
4 
5 
6 
7 
8 
9 
10 
<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> 
	
  
Sistema	
  Integrado	
  de	
  Gestão	
  –	
  Prof.	
  Ms.	
  Ricardo	
  Leme	
  
Aula	
  02	
  –	
  Select	
  for	
  XML	
  
11 
12 
13 
14 
15 
16 
17 
 </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: 
1 
2 
3 
4 
5 
6 
7 
8 
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') 
1 
2 
3 
4 
5 
6 
7 
8 
9 
10 
11 
12 
13 
14 
15 
16 
17 
18 
19 
20 
21 
22 
<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: 
1 
2 
3 
4 
5 
6 
7 
8 
9 
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') 
1 
2 
3 
<P1> 
 <PC Name="Accessories"> -- Pai 
 <PSC Name="Bike Racks" /> -- Filho 
	
  
Sistema	
  Integrado	
  de	
  Gestão	
  –	
  Prof.	
  Ms.	
  Ricardo	
  Leme	
  
Aula	
  02	
  –	
  Select	
  for	
  XML	
  
4 
5 
6 
7 
8 
9 
 <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: 
1 
2 
3 
4 
5 
6 
7 
8 
9 
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') 
1 
2 
3 
4 
5 
6 
7 
8 
9 
10 
11 
12 
13 
14 
15 
16 
17 
<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: 
1 
2 
3 
4 
5 
6 
7 
8 
9 
10 
11 
12 
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') 
1 
2 
3 
4 
5 
<P1> 
 <PSC Name="Bike Racks"> -- Pai 
 <PC Name="Accessories"> -- Filho 
 <PSC2 Name="Bike Racks" /> -- Neto 
 </PC> 
	
  
Sistema	
  Integrado	
  de	
  Gestão	
  –	
  Prof.	
  Ms.	
  Ricardo	
  Leme	
  
Aula	
  02	
  –	
  Select	
  for	
  XML	
  
6 
7 
8 
9 
10 
11 
12 
 </PSC> 
 <PSC Name="Bike Stands"> 
 <PC Name="Accessories"> 
 <PSC2 Name="Bike Stands" /> 
 </PC> 
 </PSC> 
</P1> 
For	
  XML	
  RAW	
  
Como primeiro passo, temos uma consulta RAW básica: 
1 
2 
3 
4 
5 
6 
SELECT TOP (5) 
 FirstName 
 , LastName 
FROM 
 Person.Contact 
FOR XML RAW 
1 
2 
3 
4 
5 
<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: 
1 
2 
3 
4 
5 
6 
7 
SELECT TOP (5) 
 [Name] = FirstName + ', ' + LastName 
FROM 
 Person.Contact 
FOR XML RAW('Contact') 
 , ELEMENTS 
 , ROOT('Person') 
1 
2 
3 
4 
5 
6 
7 
8 
9 
10 
11 
12 
13 
14 
15 
16 
17 
<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): 
1 
2 
3 
SELECT TOP (5) 
 [Name] = FirstName + ', ' + LastName 
 , MiddleName 
	
  
Sistema	
  Integrado	
  de	
  Gestão	
  –	
  Prof.	
  Ms.	
  Ricardo	
  Leme	
  
Aula	
  02	
  –	
  Select	
  for	
  XML	
  
4 
5 
6 
7 
8 
FROM 
 Person.ContactFOR XML RAW('Contact') 
 , ELEMENTS ABSENT 
 , ROOT('Person') 
1 
2 
3 
4 
5 
6 
7 
8 
9 
10 
11 
12 
13 
14 
15 
16 
17 
18 
<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: 
1 
2 
3 
4 
5 
6 
7 
8 
SELECT TOP (5) 
 [Name] = FirstName + ', ' + LastName 
 , MiddleName 
FROM 
 Person.Contact 
FOR XML RAW('Contact') 
 , ELEMENTS XSINIL 
 , ROOT('Person') 
1 
2 
3 
4 
5 
6 
7 
8 
9 
10 
11 
12 
13 
14 
15 
16 
17 
18 
19 
20 
21 
22 
<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>