How to return a table from xml in sql server

Question

My xml is similar to 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 me 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?

Answer 1

you can do it this way.:

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)