SQL Server - How to read, import and export data from XML files

Base 1 (Information as attributes)

<?xml version="1.0" encoding="ISO-8859-1"?> 
<Root>
   <Cliente Nome="Cliente Teste 1" Idade="29" CPF="11111111111" Email="cliente1@email.com" 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>
   <Cliente Nome="Cliente Teste 2" Idade="30" CPF="222222222222" Email="cliente2@email.com.br">
     <Endereco Cidade="Campos dos Goytacazes" Estado="RJ" Pais="Brasil" CEP="2825000">
       <Telefone Fixo="33333333333"/>
     </Endereco>
   </Cliente>
</Root>

Base 2 (Information as content)

<?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, in an easy and practical way, how to do this.

How to import XML files into SQL Server

The first step in being able to validate and work with the data contained in XML files is to import this data into our SQL Server database. If you already have the XML data in a table, you can skip this step.

To import XML from a physical file on disk or in a shared directory on your network, I will use the function fncFile_Read_Return_String, from the CLR (C#), as it is the easiest and most practical to use.

As I already demonstrated in the post SQL Server – How to import text files into the database (OLE Automation, CLR, BCP, BULK INSERT), including the source code of the function fncFile_Read_Return_String, you can also use other ways of importing text files, depending on your wishes or restrictions in the instance.

To import the file, we can do it as follows:

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 treat and read attribute data from XML in SQL Server

Once the XML is already in a table, let's start processing and reading the XML information. In this case, we will need to store the content in an XML type variable.

To do this, you can do it like this:

DECLARE @XML XML = (SELECT TOP 1 Ds_XML FROM #XML)

Now that we can load our XML into a variable, let's start processing the data.

Simple reading:

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 the XML? No. To do this, we can use the NODES function, which applies our SELECT filters to all nodes in our selector and returns them in the form of rows from a table:

Using the NODES function to return all nodes:

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/Cliente” 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 line (each record in the parent element).

After breaking the nodes into records, we need to retrieve the information for each attribute. To do this, we use the function value, followed by @NameOfAttribute (must be the same as the name of the XML attribute) and its respective return type.

Using the EXIST function

In certain situations, we need to know if a certain node exists in our XML. To do this, SQL Server provides us with the exist function, which allows us to perform this type of check:

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 exists together with the value:

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

Retrieving child nodes

The first part has been completed: We are now 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 2 more hierarchy levels for us to work with (Address and Telephone).

Importing the 1st level of hierarchy: Address

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 (Customers) and for each record (line) I cross this data with the child nodes (of the Address type).

Importing the 2nd hierarchy level: Telephone

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's an error there...

If we analyze our XML further, we will see that one of the addresses was not imported, because it did not have a telephone number and as we did a CROSS APPLY, this record was ignored. We will need to address this:

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 resolve this situation, I replaced the last CROSS APPLY with an OUTER APPLY, which has similar behavior to a LEFT JOIN in this case, where records that do not have a child node continue to appear in our table, and the columns destined 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 treat and read data from XML in SQL Server

Unlike base 1, where the information was all stored in the form of attributes, we will now use base 2 XML, which is predominantly made up of data.

Simple data reading

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

Just as I did with attributes, I wanted to demonstrate how to individually return each node in the file, specifying the index of its position in relation to the root. Now I will demonstrate again how to use the NODES function to return all nodes as records from a table:

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)

More complete example, mixing data and attributes:

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)

Many times when handling XML files, we feel the need to perform more advanced queries or filters to return the information we want instead of the entire file. For this need, we have the query function:

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:

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 Pedro’s information:

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:

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. Example, if you are using a student selector, you cannot return an attribute of that Student node that you are selecting. If you try to do this, 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 get around this, use the function value and apply your filters another way.

Using functions in XML

Another very cool feature of XML is the possibility of using functions to filter or obtain more information through the data in our XML. I will now demonstrate how to apply this to our example XML files.

Using numeric functions:

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:

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:

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:

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 processing 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 (insert), replace data (replace value of) and delete data (delete).

To use the modify() function, you must perform UPDATE, DELETE or SET @Variavel. Using this function during a SELECT is not permitted.

How to replace values ​​using the replace value of function:

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:

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:

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 a SQL variable:

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 resource (pronounced “flower”) is a powerful tool and extension of the QUERY function, which allows you to iterate through XML files and perform a series of operations. The commands are FOR, LET, ORDER BY, WHERE and RETURN. Let's see below how to use them.

Using FOR iteration:

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 FOR, WHERE and ORDER BY iteration:

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 information and give a 20% increase to two employees:
In this specific example, I create the XML in real time, as my 2 example XML did not have data values, only attributes, which makes it impossible to use together with the query function.

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 database data to XML – FOR XML RAW, AUTO, EXPLICIT, PATH

After much talk about importing and processing XML files, the time has finally come to do the opposite. How to transform data from a SQL Server table to an XML string.

To facilitate this task, SQL Server provides 4 ways to do this with FOR XML: RAW, AUTO, EXPLICI and PATH. Let's find out now what they are for and how to use them.

Creation of the test table:

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 RAW method of FOR XML generates an XML from our table where each column becomes an attribute of the generated XML and each line will represent a node (element).

SELECT *
FROM dbo.Teste_XML
FOR XML RAW

Generated XML:

<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.

SELECT *
FROM dbo.Teste_XML
FOR XML RAW('Produto'), ROOT('Produtos')

Generated XML:

<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 RAW XML we can have the columns returned as XML elements. To do this, simply include the ELEMENTS option:

SELECT *
FROM dbo.Teste_XML
FOR XML RAW('Produto'), ROOT('Produtos'), ELEMENTS

Generated XML (just a snippet so it doesn't get too long):

<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 with the “TV” product category. To handle this, we can use the XSINIL option after the ELEMENTS option, which will add the empty element and create an attribute informing this (xsi:nil=”true”):

SELECT *
FROM dbo.Teste_XML
FOR XML RAW('Produto'), ROOT('Produtos'), ELEMENTS XSINIL

And then the XML with the product is generated like this:

<Produto>
    <Categoria xsi:nil="true" />
    <Descricao>TV</Descricao>
</Produto>

Additionally, we can use the XMLSCHEMA option to transform our XML into a complete XSD:

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 standard use, the table name is the default name of each element.

SELECT *
FROM dbo.Teste_XML
FOR XML AUTO

Generated XML:

<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

The XML PATH is a little different from the other two examples in that column names and aliases are treated as XPATH elements. When you generate a common XML, without customizing, it includes a root element (row), where each line is a child element, one level of hierarchy below and each column is also an element of the XML, plus another level below:

Just like XML AUTO and XML RAW, we can use ROOT(‘NomeDaRaiz’) to create the root element, we can also use the ELEMENTS option with XSINIL as well (to return even null elements).

Note: As XML PATH always returns columns as elements, using only the ELEMENTS option will not have any effect, only if used in conjunction with XSINIL.

Creating hierarchies with XML PATH:

SELECT 
    Id AS '@Id_Produto', -- Atributo
    Categoria AS 'DadosProduto/Categoria', -- Elemento
    Descricao AS 'DadosProduto/Descricao'-- Elemento
FROM 
    dbo.Teste_XML
FOR XML PATH('Produto'), ROOT('Produtos'), ELEMENTS

Generated XML:

FOR XML EXPLICIT

FOR XML's EXPLICIT mode tends to be very different from other modes. This is because it requires a header in a specific format, defining hierarchy and structures. This header must have joined the data using UNION ALL.

The SELECT header must have the following structure:

• First column: It is a number that defines the level of the hierarchy. The column name must be Tag.
• Second column: It is a number that defines the hierarchy level of the parent element (or NULL, if it does not have one and is the root). The column name must be Parent.
• Third column onwards: This is the data that will be part of the XML and will be returned by your XML.

Note that from the beginning we need to define all the columns that will be part of the XML in the header.

The default format for field definition is defined as follows:

<NomeDoElemento>!<NúmeroDaTag>!<NomeDoAtributo>[!<InformacoesAdicionais>]

Where:

• ElementName: It is the name of the parent element that we are generating (In the case of the example, Products)
• TagNumber: It is the number of the hierarchy level of the child elements
• AttributeName: It is the name of each attribute/column of the data that we are exporting to XML (in the case of the example, Category and Description)
• Additional Information: Additional data that can be used in building the XML

Let's see now how this works in practice:

-- Cabeçalho
SELECT 
    1 AS Tag,
   NULL AS Parent,
   NULL AS [Produtos!1!Id],
   NULL AS [Produto!2!Categoria!ELEMENT],
   NULL AS [Produto!2!Descricao!ELEMENT]

UNION ALL

-- Conteúdo
SELECT 
    2 AS Tag,
    1 AS Parent,
    NULL,
    Categoria,
    Descricao
FROM 
    dbo.Teste_XML
FOR	
    XML EXPLICIT

Excerpt from the returned XML:

To make the example cooler, I'll add a new column to our table:

ALTER TABLE dbo.Teste_XML ADD Id INT IDENTITY(1,1)

Generating the XML again:

-- Cabeçalho
SELECT 
    1 AS Tag,
    NULL AS Parent,
    Id AS [Produtos!1!Id_Produto],
    NULL AS [Produto!2!Categoria!ELEMENT],
    NULL AS [Produto!2!Descricao!ELEMENT]
FROM
    dbo.Teste_XML

UNION ALL

-- Conteúdo
SELECT 
    2 AS Tag,
    1 AS Parent,
    Id AS Id_Produto,
    ISNULL(Categoria, ''),
    Descricao
FROM 
    dbo.Teste_XML
ORDER BY 
    [Produtos!1!Id_Produto], 
    [Produto!2!Categoria!ELEMENT]
FOR	
    XML EXPLICIT

Our result will be this:

It is worth mentioning that this ORDER BY used is necessary for the results to be displayed in the correct way. Otherwise, the elements will be generated in the wrong order and the XML will not have the same result.

As we can see, the new added field (ID) is an XML attribute. If you want to turn it into an attribute, just add !ELEMENT to your header:

-- Cabeçalho
SELECT 
    1 AS Tag,
    NULL AS Parent,
    Id AS [Produtos!1!Id_Produto!ELEMENT],
    NULL AS [Produto!2!Categoria!ELEMENT],
    NULL AS [Produto!2!Descricao!ELEMENT]
FROM
    dbo.Teste_XML

UNION ALL

-- Conteúdo
SELECT 
    2 AS Tag,
    1 AS Parent,
    Id AS Id_Produto,
    ISNULL(Categoria, ''),
    Descricao
FROM 
    dbo.Teste_XML
ORDER BY 
    [Produtos!1!Id_Produto!ELEMENT], 
    [Produto!2!Categoria!ELEMENT]
FOR	
    XML EXPLICIT

Staying like this:

In the two examples above, I added an ISNULL() clause to the category, since the “TV” product has no defined category. When this occurs in EXPLICIT mode, and the category is used to sort the results, the elements end up getting lost and those without a category end up together with elements from other categories.

Another way to solve this problem, in addition to adding ISNULL(), is by defining the ELEMENTXSINIL attribute type in the header of our SELECT structure and not using this column that has possible NULL values ​​in the ORDER BY:

-- Cabeçalho
SELECT 
    1 AS Tag,
    NULL AS Parent,
    Id AS [Produtos!1!Id_Produto!ELEMENT],
    NULL AS [Produto!2!Categoria!ELEMENTXSINIL],
    NULL AS [Produto!2!Descricao!ELEMENT]
FROM
    dbo.Teste_XML

UNION ALL

-- Conteúdo
SELECT 
    2 AS Tag,
    1 AS Parent,
    Id AS Id_Produto,
    Categoria,
    Descricao
FROM 
    dbo.Teste_XML
ORDER BY 
    [Produtos!1!Id_Produto!ELEMENT], 
    [Produto!2!Descricao!ELEMENT]
FOR	
    XML EXPLICIT

Generated XML: