How to construct a complete XML in T-SQL?

Navigation
7

I'm working on a procedure that returns a field in XML, so far nothing 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 as I need to repeat a process through a cursor, I do not know how to do 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. Actually the SQL statement I am putting together is called by a Trigger and needs to gather data to populate an XML field from another table . The biggest problem is that I only know of a way to assemble an XML by T-SQL, this form is concatenating strings and later transforming into XML.

    
asked by anonymous 28.02.2014 / 16:40

2 answers

3

Based on the question example, I made a code that transforms 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 , you simply apply the query to the "%" and "%" tables and store the value in a variable. So it's easy to put the value in any other table field!

    
06.03.2014 / 14:24
2

The most performative way to do this is by using the FOR XML('SuaTagRaiz') statement. I do not know how your table is, but suppose a table of states (on my system, States ). The sentence like this: 

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

Generate 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>
    
28.02.2014 / 18:04
Java Cryptography with AES, how does it work? Get an inverse relationship with Laravel