Get XML data in to table

Question

i have following XML

<Queue>
    <UserName>UserName</UserName>
    <Type>1</Type>
    <Portal name="Portal1">
        <IndexesForSelect>
            <Index>Index1</Index>
            <Index>Index2</Index>
        </IndexesForSelect>
    </Portal>
    <Portal name="Portal2">
        <IndexesForSelect>
            <Index>Index3</Index>
            <Index>Index4</Index>
        </IndexesForSelect>
    </Portal>
</Queue>

and i need to get it to a table in below format

Portal      Index
---------------------------
Portal1     Index1
Portal1     Index2
Portal2     Index3
Portal2     Index4

I really appreciate if anyone can help me.

I have tried the following code but it only returns first Index of every Portal

declare @T table
(
  XMLCol xml
)

insert into @T values
('<Queue>
      <UserName>UserName</UserName>
      <Type>1</Type>
      <Portal name="Portal1">
        <IndexesForSelect>
          <Index>Index1</Index>
          <Index>Index2</Index>
        </IndexesForSelect>
      </Portal>
      <Portal name="Portal2">
        <IndexesForSelect>
          <Index>Index3</Index>
          <Index>Index4</Index>
        </IndexesForSelect>
      </Portal>
    </Queue>')
SELECT  items.value('../@name','varchar(max)') AS [Portal],
        items.value('(Index)[1]','varchar(max)') AS [Index]
FROM @T AS T CROSS APPLY T.XMLCol.nodes('Queue/Portal/IndexesForSelect') c (items)

Answer 1

select T1.N.value('@name', 'varchar(max)') as Portal,
       T2.N.value('.', 'varchar(max)') as [Indes]
from @T as T
  cross apply XMLCol.nodes('/Queue/Portal') as T1(N)
  cross apply T1.N.nodes('IndexesForSelect/Index') as T2(N)

Answer 2

Try using Export / Import functionality provided by SQL management studio.

To read about it Go here