How to return a table from the xml in sql server

Question

How to return a table from the xml in sql server

Navigation

#1 by (1 votes)

1

My xml looks like this:

<Operacao Id="1">
  <Contatos>
    <Contato Id="2013">
      <Emails>
        <Email Id="40"/>
        <Email Id="41"/>
      </Emails>
    </Contato>
    <Contato Id="2014">
      <Emails>
        <Email Id="44"/>
        <Email Id="45"/>
        <Email Id="47"/>
      </Emails>
    </Contato>
  </Contatos>
</Operacao>'

and would like to get a return like this:

1   2013    40
1   2013    41
1   2014    44
1   2014    45
1   2014    47

This query below only returns the first line:

select 
    c.value('(@Id)[1]','int') as [Operaca.Id],
    c.value('(Contatos/Contato/@Id)[1]','int') as [Contato.Id],
    c.value('(Contatos/Contato/Emails/Email/@Id)[1]','int') as [Email.Id]
from @xml.nodes('/Operacao') as t(c)

How do I return the 5 lines?

sql-server xml

asked by anonymous 28.09.2017 / 19:30

1 answer

Carousel multiple passing 1 item at a time Add a div in the middle of an image

score 1 · Accepted Answer

You can do this as follows:

DECLARE @xml as xml
SET @xml = '<Operacao Id="1">
  <Contatos>
    <Contato Id="2013">
      <Emails>
        <Email Id="40"/>
        <Email Id="41"/>
      </Emails>
    </Contato>
    <Contato Id="2014">
      <Emails>
        <Email Id="44"/>
        <Email Id="45"/>
        <Email Id="47"/>
      </Emails>
    </Contato>
  </Contatos>
</Operacao>';   

SELECT
    c.value('(../../../../@Id)[1]','int') as [OperacaId],
    c.value('(../../@Id)[1]','int') as [ContatoId],
    c.value('(@Id)[1]','int') as [EmailId]
FROM @xml.nodes('/Operacao/Contatos/Contato/Emails/Email') as t(c)