How to build a complete XML in T-SQL?

Question

I am working on a project that returns an XML field, so far not bad. The problem arises when I have initial data coming from a query and intermediate data coming from another query.

I tried to solve with string concatenation and then convert to XML, but since I need to repeat a process through a cursor, I don’t know how to make this concatenation.

DECLARE @XML_RETORNO XML;
SET @XML_RETORNO = '<root>'
+'<nome>Marcelo</nome>'
+'<itens>'
-- Quero começar um cursor aqui
+'<item tipo="carro">Fusca</item>'
+'<item tipo="carro">Gol</item>'
+'<item tipo="moto">CG-150</item>'
-- Termino do cursor
+'</itens>'
+'</root>';

SELECT CAST(@XML_RETORNO AS XML)

Edited

This example is just an illustration of what I need. In fact the SQL statement I’m mounting is called by a Trigger and needs to gather data to fill a XML field of another table. The biggest problem is that I only know one way to mount an XML by T-SQL, this way is by concatenating strings and later transforming into XML.

Answer 1

Based on the question example, I made a code that turns a result into a similar XML:

SELECT 
(
SELECT
  nome,
  (
  SELECT 
    tipo AS 'item/@tipo',
    descricao as 'item'
    FROM item
    WHERE item.pessoa = pessoa.id
    ORDER BY descricao
    FOR XML PATH(''), Type
  ) as 'itens'
FROM pessoa
ORDER BY nome
FOR XML PATH('pessoa'), Type
)
FOR XML PATH('root')

Sqlfiddle

The result is:

<root>
    <pessoa>
        <nome>Luiz</nome>
        <itens>
            <item tipo="moto">CG-150</item>
            <item tipo="carro">Uno</item>
        </itens>
    </pessoa>
    <pessoa>
        <nome>Marcelo</nome>
        <itens>
            <item tipo="carro">Fusca</item>
            <item tipo="carro">Gol</item>
        </itens>
    </pessoa>
</root>

To get the result inside a Trigger, just you apply to query in the "tables" INSERTED and DELETED and store the value in a variable. So it’s easy to put the value in any other table field!

Answer 2

The most performative way to do this is by using the statement FOR XML('SuaTagRaiz'). I don’t know what your table looks like, but suppose a table of states (in my system, States). The sentence so:

SELECT * 
FROM States
FOR XML PATH('State'), ROOT('States')

Generates an XML like this:

<States>
  <State>
    <StateID>1</StateID>
    <CountryID>1</CountryID>
    <Name>Paraná</Name>
  </State>
  <State>
    <StateID>2</StateID>
    <CountryID>1</CountryID>
    <Name>Acre</Name>
  </State>
  <State>
    <StateID>3</StateID>
    <CountryID>1</CountryID>
    <Name>Alagoas</Name>
  </State>
  <State>
    <StateID>4</StateID>
    <CountryID>1</CountryID>
    <Name>Amapá</Name>
  </State>
  <State>
    <StateID>5</StateID>
    <CountryID>1</CountryID>
    <Name>Amazonas</Name>
  </State>
  <State>
    <StateID>6</StateID>
    <CountryID>1</CountryID>
    <Name>Bahia</Name>
  </State>
  <State>
    <StateID>7</StateID>
    <CountryID>1</CountryID>
    <Name>Ceará</Name>
  </State>
  <State>
    <StateID>8</StateID>
    <CountryID>1</CountryID>
    <Name>Distrito Federal</Name>
  </State>
  <State>
    <StateID>9</StateID>
    <CountryID>1</CountryID>
    <Name>Espírito Santo</Name>
  </State>
  <State>
    <StateID>10</StateID>
    <CountryID>1</CountryID>
    <Name>Goiás</Name>
  </State>
  <State>
    <StateID>11</StateID>
    <CountryID>1</CountryID>
    <Name>Maranhão</Name>
  </State>
  <State>
    <StateID>12</StateID>
    <CountryID>1</CountryID>
    <Name>Mato Grosso</Name>
  </State>
  <State>
    <StateID>13</StateID>
    <CountryID>1</CountryID>
    <Name>Mato Grosso do Sul</Name>
  </State>
  <State>
    <StateID>14</StateID>
    <CountryID>1</CountryID>
    <Name>Minas Gerais</Name>
  </State>
  <State>
    <StateID>15</StateID>
    <CountryID>1</CountryID>
    <Name>Pará</Name>
  </State>
  <State>
    <StateID>16</StateID>
    <CountryID>1</CountryID>
    <Name>Paraíba</Name>
  </State>
  <State>
    <StateID>17</StateID>
    <CountryID>1</CountryID>
    <Name>Pernambuco</Name>
  </State>
  <State>
    <StateID>18</StateID>
    <CountryID>1</CountryID>
    <Name>Piauí</Name>
  </State>
  <State>
    <StateID>19</StateID>
    <CountryID>1</CountryID>
    <Name>Rio de Janeiro</Name>
  </State>
  <State>
    <StateID>20</StateID>
    <CountryID>1</CountryID>
    <Name>Rio Grande do Norte</Name>
  </State>
  <State>
    <StateID>21</StateID>
    <CountryID>1</CountryID>
    <Name>Rio Grande do Sul</Name>
  </State>
  <State>
    <StateID>22</StateID>
    <CountryID>1</CountryID>
    <Name>Rondônia</Name>
  </State>
  <State>
    <StateID>23</StateID>
    <CountryID>1</CountryID>
    <Name>Roraima</Name>
  </State>
  <State>
    <StateID>24</StateID>
    <CountryID>1</CountryID>
    <Name>Santa Catarina</Name>
  </State>
  <State>
    <StateID>25</StateID>
    <CountryID>1</CountryID>
    <Name>São Paulo</Name>
  </State>
  <State>
    <StateID>26</StateID>
    <CountryID>1</CountryID>
    <Name>Sergipe</Name>
  </State>
  <State>
    <StateID>27</StateID>
    <CountryID>1</CountryID>
    <Name>Tocantins</Name>
  </State>
</States>