Hello people,
Good night!
In this post I will demonstrate to you how to import, read, handle and export data between a SQL Server table and an XML file.
This integration between database and XML files is a great feature and a big differentiator for developers using SQL Server who can easily read and generate files in this format natively through the database.
Knowing the Sample XML FilesBase 1 (Information as Attributes)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | <?xml version="1.0" encoding="ISO-8859-1"?> <Root> <Cliente Nome="Cliente Teste 1" Idade="29" CPF="11111111111" Email="[email protected]" Celular="99999999999"> <Endereco Cidade="Vitoria" Estado="ES" Pais="Brasil" CEP="2920000"> <Telefone Fixo="888888888888" Quintal="Sim" Quadra="Sim" /> </Endereco> <Endereco Cidade="Vila Velha" Estado="ES" Pais="Brasil" CEP="2900000"> <Telefone Fixo="777777777777" Piscina="Sim" /> </Endereco> <Endereco Cidade="Serra" Estado="ES" Pais="Brasil" CEP="2970000" /> </Cliente> <Endereco Cidade="Campos dos Goytacazes" Estado="RJ" Pais="Brasil" CEP="2825000"> <Telefone Fixo="33333333333"/> </Endereco> </Cliente> </Root> |
Base 2 (Information as Content)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | <?xml version="1.0" encoding="iso-8859-1"?> <Escola> <Turma Nome="Turma 1" Serie="1"> <Aluno Apostolo="1" Traidor="0" Nota="7">Joao</Aluno> <Aluno Apostolo="1" Traidor="0" Nota="7.5">Marcos</Aluno> <Aluno Apostolo="1" Traidor="0" Nota="8">Mateus</Aluno> <Aluno Apostolo="0" Traidor="0" Nota="10">Paulo</Aluno> </Turma> <Turma Nome="Turma 2" Serie="2"> <Aluno Apostolo="1" Traidor="0" Nota="6.1">Andre</Aluno> <Aluno Apostolo="1" Traidor="0" Nota="5.9">Simao</Aluno> <Aluno Apostolo="1" Traidor="0" Nota="7.3">Pedro</Aluno> <Aluno Apostolo="1" Traidor="0" Nota="7.1">Bartolomeu</Aluno> <Aluno Apostolo="1" Traidor="1" Nota="0">Judas</Aluno> <Aluno Apostolo="1" Traidor="0" Nota="6.4">Tiago</Aluno> <Aluno Apostolo="1" Traidor="0" Nota="6.7">Felipe</Aluno> <Aluno Apostolo="1" Traidor="0" Nota="6.3">Tome</Aluno> <Aluno Apostolo="1" Traidor="0" Nota="6.5">Tadeu</Aluno> </Turma> </Escola> |
Let's check now, easily and practically, how to do this.
How to import XML files into SQL Server
The first step in validating and working with the data contained in XML files is to import this data into our SQL Server database. If you already have XML data in a table, you can skip this step.
For importing XML from a physical file on disk or in a shared directory of your network, I will use the function fncFile_Ler_Returns_String, from CLR (C #), as it is the easiest and most practical to use.
As I had already shown in the post SQL Server - How to import text files into database (OLE Automation, CLR, BCP, BULK INSERT), including the source code of the function fncFile_Ler_Returns_String, you can also use other ways to import text files at your own discretion or restriction on the instance.
To perform the file import, we can do the following:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | DECLARE @XML_Bruto VARCHAR(MAX) = CLR.dbo.fncArquivo_Ler_Retorna_String('C:\Exemplo.xml') IF (OBJECT_ID('tempdb..#XML') IS NOT NULL) DROP TABLE #XML CREATE TABLE #XML ( Ds_XML VARCHAR(MAX) ) INSERT INTO #XML SELECT @XML_Bruto SET @XML_Bruto = CLR.dbo.fncArquivo_Ler_Retorna_String('C:\Aluno.xml') IF (OBJECT_ID('tempdb..#XML2') IS NOT NULL) DROP TABLE #XML2 CREATE TABLE #XML2 ( Ds_XML VARCHAR(MAX) ) INSERT INTO #XML2 SELECT @XML_Bruto |
How to handle and read attribute data from an XML in SQL Server
Once the XML is already in a table, let's start handling and reading the XML information. In this case, we will need to store the content in an XML type variable.
For this you can do this:
1 | DECLARE @XML XML = (SELECT TOP 1 Ds_XML FROM #XML) |
Now that we can load our XML into a variable, let's start handling the data.
Simple reading:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | DECLARE @XML XML = (SELECT TOP 1 Ds_XML FROM #XML) SELECT @XML.value('(/Root/Cliente[1]/@Nome)[1]','varchar(100)') AS Cliente1_Nome, @XML.value('(/Root/Cliente[1]/@Idade)[1]','int') AS Cliente1_Idade, @XML.value('(/Root/Cliente[1]/@CPF)[1]','varchar(100)') AS Cliente1_CPF, @XML.value('(/Root/Cliente[1]/@Email)[1]','varchar(100)') AS Cliente1_Email, @XML.value('(/Root/Cliente[1]/@Celular)[1]','varchar(100)') AS Cliente1_Celular, @XML.value('(/Root/Cliente[1]/Endereco[1]/@Cidade)[1]','varchar(100)') AS Cliente1_Endereco1_Cidade, @XML.value('(/Root/Cliente[1]/Endereco[2]/@Cidade)[1]','varchar(100)') AS Cliente1_Endereco2_Cidade SELECT @XML.value('(/Root/Cliente[2]/@Nome)[1]','varchar(100)') AS Cliente2_Nome, @XML.value('(/Root/Cliente[2]/@Idade)[1]','int') AS Cliente2_Idade, @XML.value('(/Root/Cliente[2]/@CPF)[1]','varchar(100)') AS Cliente2_CPF, @XML.value('(/Root/Cliente[2]/@Email)[1]','varchar(100)') AS Cliente2_Email, @XML.value('(/Root/Cliente[2]/@Celular)[1]','varchar(100)') AS Cliente2_Celular, @XML.value('(/Root/Cliente[2]/Endereco[1]/@Cidade)[1]','varchar(100)') AS Cliente1_Endereco1_Cidade, @XML.value('(/Root/Cliente[2]/Endereco[2]/@Cidade)[1]','varchar(100)') AS Cliente1_Endereco2_Cidade |
As we saw in the example above, we need to manually define the line we want to return information from our XML. But what if the file has 100, 1.000 or more lines? Will we have to use the WHILE statement to traverse all lines of XML? No. To do so, we can use the NODES function, which applies our SELECT filters to all nodes of our selector and returns them in the form of table rows:
Using the NODES function to return all nodes:
1 2 3 4 5 6 7 8 9 10 | DECLARE @XML XML = (SELECT TOP 1 Ds_XML FROM #XML) SELECT Clientes.linha.value('@Nome','varchar(100)') AS Nome, Clientes.linha.value('@Idade','int') AS Idade, Clientes.linha.value('@CPF','varchar(14)') AS CPF, Clientes.linha.value('@Email','varchar(200)') AS Email, Clientes.linha.value('@Celular','varchar(20)') AS Celular FROM @XML.nodes('/Root/Cliente') Clientes(linha) |
When we use the function nodes, we are informing that for each node in the XML tree that is in the “/ Root / Client” structure, a record from our table is returned. This record is identified using the nomenclature Customers (parent element, which would be like the table) and row (each record of the parent element).
After breaking the nodes into records, we need to retrieve the information for each attribute. For this, we use the function value, followed by @ AttributeName (must be the same as the XML attribute name) and its return type.
Using the EXIST Function
In certain situations, we need to know if a particular node exists in our XML. For this, SQL Server provides us with the exist function, which allows us to perform this type of verification:
1 2 3 4 5 6 | DECLARE @XML XML = (SELECT TOP 1 Ds_XML FROM #XML) SELECT @XML.exist('(/Root/Cliente[1]/@Nome)[1]') AS Cliente1_Existe, @XML.exist('(/Root/Cliente[2]/@Nome)[1]') AS Cliente2_Existe, @XML.exist('(/Root/Cliente[3]/@Nome)[1]') AS Cliente3_Existe |
Using exist along with the value:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | DECLARE @XML XML = (SELECT TOP 1 Ds_XML FROM #XML) SELECT (CASE WHEN @XML.exist('(/Root/Cliente[1]/@Nome)[1]') = 1 THEN @XML.value('(/Root/Cliente[1]/@Nome)[1]', 'varchar(100)') ELSE 'Não existe' END) AS Cliente1_Nome, (CASE WHEN @XML.exist('(/Root/Cliente[2]/@Nome)[1]') = 1 THEN @XML.value('(/Root/Cliente[2]/@Nome)[1]', 'varchar(100)') ELSE 'Não existe' END) AS Cliente2_Nome, (CASE WHEN @XML.exist('(/Root/Cliente[3]/@Nome)[1]') = 1 THEN @XML.value('(/Root/Cliente[3]/@Nome)[1]', 'varchar(100)') ELSE 'Não existe' END) AS Cliente3_Nome |
Recovering the child nodes
The first part has been completed: We have already been able to perform a simple import into our XML. But we still have a long way to go: Our XML has N child nodes, with more 2 levels of hierarchy to work with (Address and Phone).
Importing the 1th Hierarchy Level: Address
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | DECLARE @XML XML = (SELECT TOP 1 Ds_XML FROM #XML) SELECT Clientes.linha.value('@Nome','varchar(100)') AS Nome, Clientes.linha.value('@Idade','int') AS Idade, Clientes.linha.value('@CPF','varchar(14)') AS CPF, Clientes.linha.value('@Email','varchar(200)') AS Email, Clientes.linha.value('@Celular','varchar(20)') AS Celular, Enderecos.linha.value('@Cidade','varchar(60)') AS Cidade, Enderecos.linha.value('@Estado','varchar(2)') AS UF, Enderecos.linha.value('@Pais','varchar(50)') AS Pais, Enderecos.linha.value('@CEP','varchar(10)') AS CEP FROM @XML.nodes('/Root/Cliente') Clientes(linha) CROSS APPLY Clientes.linha.nodes('Endereco') Enderecos(linha) |
Note that to get the sublevels of the hierarchy I use a CROSS APPLY of the parent element (Clients) and for each record (row) I make a crossover of this data with child nodes (of type Address).
Importing the 2th Hierarchy Level: Phone
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | DECLARE @XML XML = (SELECT TOP 1 Ds_XML FROM #XML) SELECT Clientes.linha.value('@Nome','varchar(100)') AS Nome, Clientes.linha.value('@Idade','int') AS Idade, Clientes.linha.value('@CPF','varchar(14)') AS CPF, Clientes.linha.value('@Email','varchar(200)') AS Email, Clientes.linha.value('@Celular','varchar(20)') AS Celular, Enderecos.linha.value('@Cidade','varchar(60)') AS Cidade, Enderecos.linha.value('@Estado','varchar(2)') AS UF, Enderecos.linha.value('@Pais','varchar(50)') AS Pais, Enderecos.linha.value('@CEP','varchar(10)') AS CEP, Telefones.linha.value('@Fixo','varchar(20)') AS Telefone_Fixo, Telefones.linha.value('@Piscina','varchar(20)') AS Tem_Piscina, Telefones.linha.value('@Quintal','varchar(20)') AS Tem_Quintal, Telefones.linha.value('@Quadra','varchar(20)') AS Tem_Quadra, Telefones.linha.value('@NaoExiste','varchar(20)') AS Atributo_Nao_Existe FROM @XML.nodes('/Root/Cliente') Clientes(linha) CROSS APPLY Clientes.linha.nodes('Endereco') Enderecos(linha) CROSS APPLY Enderecos.linha.nodes('Telefone') Telefones(linha) |
And then we were finally able to import the data. But there is an error there ..
If we take a closer look at our XML, we will see that one of the addresses was not imported, because it had no phone, and since we made a CROSS APPLY, this record was ignored. We will need to address this:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | DECLARE @XML XML = (SELECT TOP 1 Ds_XML FROM #XML) SELECT Clientes.linha.value('@Nome','varchar(100)') AS Nome, Clientes.linha.value('@Idade','int') AS Idade, Clientes.linha.value('@CPF','varchar(14)') AS CPF, Clientes.linha.value('@Email','varchar(200)') AS Email, Clientes.linha.value('@Celular','varchar(20)') AS Celular, Enderecos.linha.value('@Cidade','varchar(60)') AS Cidade, Enderecos.linha.value('@Estado','varchar(2)') AS UF, Enderecos.linha.value('@Pais','varchar(50)') AS Pais, Enderecos.linha.value('@CEP','varchar(10)') AS CEP, Telefones.linha.value('@Fixo','varchar(20)') AS Telefone_Fixo, Telefones.linha.value('@Piscina','varchar(20)') AS Tem_Piscina, Telefones.linha.value('@Quintal','varchar(20)') AS Tem_Quintal, Telefones.linha.value('@Quadra','varchar(20)') AS Tem_Quadra, Telefones.linha.value('@NaoExiste','varchar(20)') AS Atributo_Nao_Existe FROM @XML.nodes('/Root/Cliente') Clientes(linha) CROSS APPLY Clientes.linha.nodes('Endereco') Enderecos(linha) OUTER APPLY Enderecos.linha.nodes('Telefone') Telefones(linha) |
To address this situation, I replaced the last CROSS APPLY with an OUTER APPLY, which behaves similar to a LEFT JOIN in this case, where records that do not have a child node continue to appear in our table, and columns intended for child nodes will be empty Whenever there is a situation where you are not sure whether the element will have children or not, use OUTER APPLY.
How to handle and read XML data in SQL Server
Unlike the 1 base, where the information was all stored as attributes, we will now use the 2 base XML, which is predominantly data.
Simple data reading
1 2 3 4 5 6 7 8 9 10 | DECLARE @XML XML = (SELECT TOP 1 Ds_XML FROM #XML2) SELECT @XML.value('(Escola/Turma/Aluno)[1]', 'varchar(60)') AS Aluno1, @XML.value('(Escola/Turma/Aluno)[2]', 'varchar(60)') AS Aluno2, @XML.value('(Escola/Turma/Aluno)[3]', 'varchar(60)') AS Aluno3, @XML.value('(Escola/Turma/Aluno)[4]', 'varchar(60)') AS Aluno4, @XML.value('(Escola/Turma/Aluno)[5]', 'varchar(60)') AS Aluno5, @XML.value('(Escola/Turma/Aluno)[6]', 'varchar(60)') AS Aluno6, @XML.value('(Escola/Turma/Aluno)[14]', 'varchar(60)') AS Aluno14_Nao_Existe |
As I did with the attributes, I wanted to demonstrate how to individually return each file node by specifying the index of its position relative to the root. Now I will demonstrate again how to use the NODES function to return all nodes as records in a table:
1 2 3 4 5 6 | DECLARE @XML XML = (SELECT TOP 1 Ds_XML FROM #XML2) SELECT Alunos.linha.value('.','varchar(100)') AS Nome FROM @XML.nodes('/Escola/Turma/Aluno') Alunos(linha) |
Most complete example, mixing data and attributes:
1 2 3 4 5 6 7 8 9 10 11 12 13 | DECLARE @XML XML = (SELECT TOP 1 Ds_XML FROM #XML2) SELECT Alunos.linha.value('.', 'varchar(60)') AS Aluno, Turmas.linha.value('@Nome','varchar(100)') AS Turma, Turmas.linha.value('@Serie','int') AS Serie, (CASE WHEN Alunos.linha.value('@Apostolo','varchar(60)') = '1' THEN 'SIM' ELSE 'NÃO' END) AS Apostolo, (CASE WHEN Alunos.linha.value('@Traidor','varchar(2)') = '1' THEN 'SIM' ELSE 'NÃO' END) AS Traidor FROM @XML.nodes('/Escola/Turma') Turmas(linha) CROSS APPLY Turmas.linha.nodes('Aluno') Alunos(linha) |
Using XQuery (XML.query Function)
Often when handling XML files, we feel the need to perform more advanced queries or filters to return the information we want rather than the entire file. For this need, we have the query function:
1 2 3 4 5 6 7 | DECLARE @XML XML = (SELECT TOP 1 Ds_XML FROM #XML2) SELECT @XML.query('.') AS XML_Completo, @XML.query('Escola/Turma[1]/Aluno[1]') AS XML_Turma1_Aluno1, @XML.query('Escola/Turma[1]/Aluno[1]/text()') AS XML_Turma1_Aluno1_Nome, @XML.query('Escola/Turma[1]/Aluno[1]').value('.', 'varchar(100)') AS Turma1_Aluno1_Nome |
Using XQuery to filter results:
1 2 3 4 5 6 7 8 | DECLARE @XML XML = (SELECT TOP 1 Ds_XML FROM #XML2) SELECT @XML.query('Escola/Turma[@Serie=''2'']') AS XML_Turma_2a_Serie, @XML.query('Escola/Turma[@Nome=''Turma 1'']') AS XML_Turma1, @XML.query('Escola/Turma/Aluno[@Traidor=''1'']') AS XML_Judas, @XML.query('Escola/Turma/Aluno[@Apostolo=''0'']') AS XML_Paulo, @XML.query('Escola/Turma/Aluno[.=''Pedro'']') AS XML_Pedro |
Returning Student Information Peter:
1 2 3 4 5 6 7 8 9 10 | DECLARE @XML XML = (SELECT TOP 1 Ds_XML FROM #XML2) DECLARE @XML2 XML = (SELECT @XML.query('Escola/Turma/Aluno[.=''Pedro'']')) SELECT Alunos.linhas.value('.', 'varchar(100)') AS Nome, Alunos.linhas.value('@Nota', 'float') AS Nota, Alunos.linhas.value('@Apostolo', 'bit') AS Apostolo, Alunos.linhas.value('@Traidor', 'bit') AS Apostolo FROM @XML2.nodes('/Aluno') AS Alunos(linhas) |
Working with values:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | DECLARE @XML XML = (SELECT TOP 1 Ds_XML FROM #XML2) DECLARE @XML_Aprovados XML = (SELECT @XML.query('//Aluno[@Nota>=7]')) DECLARE @XML_Reprovados XML = (SELECT @XML.query('//Aluno[@Nota<7]')) SELECT Alunos.linhas.value('.', 'varchar(100)') AS Nome, Alunos.linhas.value('@Nota', 'float') AS Nota, Alunos.linhas.value('@Apostolo', 'bit') AS Apostolo, Alunos.linhas.value('@Traidor', 'bit') AS Apostolo FROM @XML_Aprovados.nodes('/Aluno') AS Alunos(linhas) ORDER BY Nota DESC SELECT Alunos.linhas.value('.', 'varchar(100)') AS Nome, Alunos.linhas.value('@Nota', 'float') AS Nota, Alunos.linhas.value('@Apostolo', 'bit') AS Apostolo, Alunos.linhas.value('@Traidor', 'bit') AS Apostolo FROM @XML_Reprovados.nodes('/Aluno') AS Alunos(linhas) ORDER BY Nota |
One thing to note about the query function is that it does not allow you to select attribute data that is at the same level as the current node. For example, if you are using a student selector, you cannot return an attribute from that Student node that you are selecting. If you try to do so, you will encounter one of these error messages:
XQuery [Sqm.data.query ()]: Attribute may not appear outside of an element
XQuery [value ()]: Top-level attribute nodes are not supported
To work around this, use the function value and apply your filters in another way.
Using functions in XML
Another very nice feature of XML is the ability to use functions to filter or get more information from our XML data. I will now demonstrate how to apply this to our sample XML files.
Using numeric functions:
1 2 3 4 5 6 7 8 9 10 | DECLARE @XML XML = (SELECT TOP 1 Ds_XML FROM #XML2) SELECT @XML.value('max(//Aluno/@Nota)', 'float') AS Maior_Nota, @XML.value('min(//Aluno/@Nota)', 'float') AS Menor_Nota, @XML.value('avg(//Aluno/@Nota)', 'float') AS Media_Nota, @XML.value('sum(//Aluno/@Nota)', 'float') AS Soma_Nota, @XML.value('count(//Aluno/@Nota)', 'int') AS Qtde_Nota, @XML.value('count(//Aluno[@Nota>=7]/@Nota)', 'int') AS Qtde_Nota_Maior7, @XML.value('count(//Aluno[@Nota<7]/@Nota)', 'int') AS Qtde_Nota_Menor7 |
String Functions:
1 2 3 4 5 6 7 | DECLARE @XML XML = (SELECT TOP 1 Ds_XML FROM #XML2) SELECT @XML.value('(//Aluno[.=''Pedro''])[1]', 'varchar(100)') AS Pedro, @XML.value('string-length((//Aluno[.=''Pedro''])[1])', 'varchar(100)') AS Tamanho_Nome_Pedro, @XML.value('concat((//Aluno[.=''Pedro''])[1], '' Teste'')', 'varchar(100)') AS [Concat], @XML.value('substring((//Aluno[.=''Pedro''])[1], 1, 1)', 'varchar(100)') AS [Primeira_Letra] |
or we can use contains:
1 2 3 4 5 6 7 8 9 10 11 | DECLARE @XML XML = (SELECT TOP 1 Ds_XML FROM #XML2) SELECT Alunos.linha.value('.', 'varchar(100)') AS Pedro, Alunos.linha.value('string-length((.))', 'varchar(100)') AS Tamanho_Nome_Pedro, Alunos.linha.value('concat((.), '' Teste'')', 'varchar(100)') AS [Concat], Alunos.linha.value('substring((.), 1, 1)', 'varchar(100)') AS [Primeira_Letra] FROM @XML.nodes('//Aluno') AS Alunos(linha) WHERE Alunos.linha.value('contains((.), "Pedro")', 'bit') = 1 |
and even simple Transact-SQL operations:
1 2 3 4 5 6 7 8 9 10 11 | DECLARE @XML XML = (SELECT TOP 1 Ds_XML FROM #XML2) SELECT Alunos.linha.value('.', 'varchar(100)') AS Pedro, Alunos.linha.value('string-length((.))', 'varchar(100)') AS Tamanho_Nome_Pedro, Alunos.linha.value('concat((.), '' Teste'')', 'varchar(100)') AS [Concat], Alunos.linha.value('substring((.), 1, 1)', 'varchar(100)') AS [Primeira_Letra] FROM @XML.nodes('//Aluno') AS Alunos(linha) WHERE Alunos.linha.value('.', 'varchar(100)') = 'Pedro' |
Manipulating XML with the modify function
When we are using the modify function, we have further proof of how advanced XML handling is with SQL Server. This function allows us to modify the data stored in the XML variable at run time, and it allows us to insert data, replace data of replace, and delete data.
To use the modify () function, you must perform UPDATE, DELETE or SET @Variable. Use of this function during a SELECT is not allowed.
How to replace values using the replace value of function:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | DECLARE @XML XML = (SELECT TOP 1 Ds_XML FROM #XML2) SELECT @XML.value('(//Aluno[.="Pedro"]/@Nota)[1]', 'float') AS Nota_Antes ------------------------------------------------------ -- ALTERA O VALOR DA NOTA DO ALUNO "PEDRO" ------------------------------------------------------ DECLARE @Tabela_XML TABLE ( Dados XML ) INSERT INTO @Tabela_XML SELECT Ds_Xml FROM #XML2 UPDATE @Tabela_XML SET Dados.modify('replace value of (//Aluno[.="Pedro"]/@Nota)[1] with("8.5")') SET @XML = (SELECT TOP 1 Dados FROM @Tabela_XML) SELECT @XML.value('(//Aluno[.="Pedro"]/@Nota)[1]', 'float') AS Nota_Depois |
Remove an element from XML using the delete function:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | DECLARE @XML XML = (SELECT TOP 1 Ds_XML FROM #XML2) SELECT Alunos.linha.value('.', 'varchar(100)') AS Todos_os_Alunos FROM @XML.nodes('//Aluno') Alunos(linha) ------------------------------------------------------ -- REMOVE OS ALUNOS COM NOTA MENOR QUE 7 ------------------------------------------------------ DECLARE @Tabela_XML TABLE ( Dados XML ) INSERT INTO @Tabela_XML SELECT Ds_Xml FROM #XML2 UPDATE @Tabela_XML SET Dados.modify('delete (//Aluno[@Nota<7])') SET @XML = (SELECT TOP 1 Dados FROM @Tabela_XML) SELECT Alunos.linha.value('.', 'varchar(100)') AS Alunos_Aprovados, Alunos.linha.value('@Nota', 'float') AS Nota FROM @XML.nodes('//Aluno') Alunos(linha) |
Insert nodes into XML using the insert function:
1 2 3 4 5 6 7 8 9 10 11 12 | DECLARE @XML XML = (SELECT TOP 1 Ds_XML FROM #XML2) SET @XML.modify('insert <Aluno Apostolo="0" Traidor="0" Nota="2.6">Dirceu Turma 1</Aluno> into (//Turma)[1]') SET @XML.modify('insert <Aluno Apostolo="0" Traidor="0" Nota="2.9">Dirceu Turma 2</Aluno> into (//Turma)[2]') SELECT Alunos.linha.value('.', 'varchar(100)') AS Alunos_Aprovados, Alunos.linha.value('@Nota', 'float') AS Nota FROM @XML.nodes('//Aluno') Alunos(linha) |
Insert multiple nodes into XML from one SQL variable:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | DECLARE @XML XML = (SELECT TOP 1 Ds_XML FROM #XML2) DECLARE @Inserir XML = ' <Turma Nome="Turma 3" Serie="3"> <Aluno Apostolo="0" Traidor="0" Nota="2.6">Dirceu 1</Aluno> <Aluno Apostolo="0" Traidor="0" Nota="2.6">Dirceu 2</Aluno> </Turma>' SET @XML.modify('insert sql:variable("@Inserir") into (//Escola)[1]') SELECT Alunos.linha.value('.', 'varchar(100)') AS Alunos, Alunos.linha.value('@Nota', 'float') AS Nota, Alunos.linha.value('../@Nome', 'varchar(100)') AS Turma FROM @XML.nodes('//Aluno') Alunos(linha) |
Using XQuery FLOWR Expressions in XML
The FLOWR feature (pronounced “flower”) is a powerful tool and extension of the QUERY function, which allows iterating over XML files and performing a series of operations. The commands are FOR, LET, ORDER BY, WHERE and RETURN. Let's see below how to use them.
Using the FOR iteration:
1 2 3 4 5 6 7 8 9 | DECLARE @XML XML = (SELECT TOP 1 Ds_XML FROM #XML2) DECLARE @XML2 XML = (SELECT @XML.query('for $A in //Aluno[@Nota>=7] return $A')) SELECT Alunos.linha.value('.', 'varchar(100)') AS Nome, Alunos.linha.value('@Nota', 'float') AS Nota FROM @XML2.nodes('//Aluno') Alunos(linha) |
Using the FOR, WHERE, and ORDER BY iteration:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | DECLARE @XML XML = (SELECT TOP 1 Ds_XML FROM #XML2) DECLARE @XML2 XML = (SELECT @XML.query(' for $A in //Aluno where ($A = "Pedro" or $A = "Marcos" or $A = "Andre" or $A = "Joao") order by $A descending return $A')) SELECT Alunos.linha.value('.', 'varchar(100)') AS Nome, Alunos.linha.value('@Nota', 'float') AS Nota FROM @XML2.nodes('//Aluno') Alunos(linha) |
Using the LET command to change the value of an information and increase 20% for two employees:
In this specific example, I create XML in real time, because my example 2 XML had no values in data, only in attributes, which makes it impossible to use together with the query function.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | DECLARE @XML XML = ' <Empresa> <Funcionario> <Nome>Joao</Nome> <Salario>1250.28</Salario> </Funcionario> <Funcionario> <Nome>Pedro</Nome> <Salario>1754.54</Salario> </Funcionario> <Funcionario> <Nome>Paulo</Nome> <Salario>5487.99</Salario> </Funcionario> </Empresa>' DECLARE @XML_Modificado XML = (SELECT @XML.query(' for $A in //Funcionario let $T := 1.20 where $A/Nome != "Pedro" return <Funcionario> <Nome>{data($A/Nome)}</Nome> <NovoSalario>{data($A/Salario/text())[1] * data($T)}</NovoSalario> </Funcionario>')) SELECT Funcionarios.linha.value('Nome[1]', 'varchar(100)') AS Nome, Funcionarios.linha.value('NovoSalario[1]', 'numeric(18,2)') AS NovoSalario FROM @XML_Modificado.nodes('//Funcionario') Funcionarios(linha) |
Exporting bank data to XML - FOR XML RAW, AUTO, EXPLICIT, PATH
After much talk about importing and handling XML files, it's finally time to go the other way. How to transform data from a SQL Server table to an XML string.
To make this task easier, SQL Server provides 4 ways to do this with FOR XML: RAW, AUTO, EXPLICI, and PATH. Let's find out now what it is for and how to use it.
Test table creation:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | IF (OBJECT_ID('dbo.Teste_XML') IS NOT NULL) DROP TABLE dbo.Teste_XML CREATE TABLE dbo.Teste_XML ( Categoria VARCHAR(100), Descricao VARCHAR(100) ) INSERT INTO dbo.Teste_XML ( Categoria, Descricao ) VALUES ('Brinquedo', 'Bola'), ('Brinquedo', 'Carrinho'), ('Brinquedo', 'Boneco'), ('Brinquedo', 'Jogo'), ('Cama e Mesa', 'Toalha'), ('Cama e Mesa', 'Edredom'), ('Informatica', 'Teclado'), ('Informatica', 'Mouse'), ('Informatica', 'HD'), ('Informatica', 'CPU'), ('Informatica', 'Memoria'), ('Informatica', 'Placa-Mae'), (NULL, 'TV') |
FOR XML RAW
The FORW RAW method generates an XML from our table where each column becomes an attribute of the generated XML and each row will represent a node (element).
1 2 3 | SELECT * FROM dbo.Teste_XML FOR XML RAW |
Generated XML:
1 2 3 4 5 6 7 8 9 10 11 12 13 | <row Categoria="Brinquedo" Descricao="Bola" /> <row Categoria="Brinquedo" Descricao="Carrinho" /> <row Categoria="Brinquedo" Descricao="Boneco" /> <row Categoria="Brinquedo" Descricao="Jogo" /> <row Categoria="Cama e Mesa" Descricao="Toalha" /> <row Categoria="Cama e Mesa" Descricao="Edredom" /> <row Categoria="Informatica" Descricao="Teclado" /> <row Categoria="Informatica" Descricao="Mouse" /> <row Categoria="Informatica" Descricao="HD" /> <row Categoria="Informatica" Descricao="CPU" /> <row Categoria="Informatica" Descricao="Memoria" /> <row Categoria="Informatica" Descricao="Placa-Mae" /> <row Descricao="TV" /> |
In the example below, I will add the ROOT option after RAW (), to add a root element that will be the parent node of the created nodes. This is optional, if you don't use it, the only difference is that the element will not be created.
1 2 3 | SELECT * FROM dbo.Teste_XML FOR XML RAW('Produto'), ROOT('Produtos') |
Generated XML:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | <Produtos> <Produto Categoria="Brinquedo" Descricao="Bola" /> <Produto Categoria="Brinquedo" Descricao="Carrinho" /> <Produto Categoria="Brinquedo" Descricao="Boneco" /> <Produto Categoria="Brinquedo" Descricao="Jogo" /> <Produto Categoria="Cama e Mesa" Descricao="Toalha" /> <Produto Categoria="Cama e Mesa" Descricao="Edredom" /> <Produto Categoria="Informatica" Descricao="Teclado" /> <Produto Categoria="Informatica" Descricao="Mouse" /> <Produto Categoria="Informatica" Descricao="HD" /> <Produto Categoria="Informatica" Descricao="CPU" /> <Produto Categoria="Informatica" Descricao="Memoria" /> <Produto Categoria="Informatica" Descricao="Placa-Mae" /> <Produto Descricao="TV" /> </Produtos> |
Even in XML RAW we can have columns returned as XML elements. To do this, simply include the ELEMENTS option:
1 2 3 | SELECT * FROM dbo.Teste_XML FOR XML RAW('Produto'), ROOT('Produtos'), ELEMENTS |
Generated XML (just a snippet not to get too big):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | <Produtos> <Produto> <Categoria>Brinquedo</Categoria> <Descricao>Bola</Descricao> </Produto> <Produto> <Categoria>Brinquedo</Categoria> <Descricao>Carrinho</Descricao> </Produto> <Produto> <Categoria>Brinquedo</Categoria> <Descricao>Boneco</Descricao> </Produto> </Produtos> |
Another interesting option of FOR XML is when dealing with empty data (NULL). When we do not perform any treatment, they are simply ignored and are not generated, as is the case for the product category “TV”. To address this, we can use the XSINIL option after the ELEMENTS option, which will add the empty element and create an attribute stating this (xsi: nil = ”true”):
1 2 3 | SELECT * FROM dbo.Teste_XML FOR XML RAW('Produto'), ROOT('Produtos'), ELEMENTS XSINIL |
And then the XML with the product is generated like this:
1 2 3 4 | <Produto> <Categoria xsi:nil="true" /> <Descricao>TV</Descricao> </Produto> |
Additionally, we can use the XMLSCHEMA option to transform our XML into a full XSD:
1 2 3 | SELECT * FROM dbo.Teste_XML FOR XML RAW('Produto'), ROOT('Produtos'), ELEMENTS XSINIL, XMLSCHEMA |
FOR XML AUTO
FOR XML's AUTO mode is very similar to RAW, but with the difference that in its default usage, the table name is the default name of each element.
1 2 3 | SELECT * FROM dbo.Teste_XML FOR XML AUTO |
Generated XML:
1 2 3 4 5 6 7 8 9 10 11 12 13 | <dbo.Teste_XML Categoria="Brinquedo" Descricao="Bola" /> <dbo.Teste_XML Categoria="Brinquedo" Descricao="Carrinho" /> <dbo.Teste_XML Categoria="Brinquedo" Descricao="Boneco" /> <dbo.Teste_XML Categoria="Brinquedo" Descricao="Jogo" /> <dbo.Teste_XML Categoria="Cama e Mesa" Descricao="Toalha" /> <dbo.Teste_XML Categoria="Cama e Mesa" Descricao="Edredom" /> <dbo.Teste_XML Categoria="Informatica" Descricao="Teclado" /> <dbo.Teste_XML Categoria="Informatica" Descricao="Mouse" /> <dbo.Teste_XML Categoria="Informatica" Descricao="HD" /> <dbo.Teste_XML Categoria="Informatica" Descricao="CPU" /> <dbo.Teste_XML Categoria="Informatica" Descricao="Memoria" /> <dbo.Teste_XML Categoria="Informatica" Descricao="Placa-Mae" /> <dbo.Teste_XML Descricao="TV" /> |
FOR XML PATH
XML PATH is a little different from the other two examples, as column names and aliases are treated as XPATH elements. When you generate ordinary XML without customizing it, it includes a row element, where each row is a child element, one hierarchy level below, and each column is also