Insert repeating tag xml data to multiple sql table columns

I’m having following XML and query.

declare @xml as xml
set @xml = '<root>
<header1></hedaer1>
<header2></hedaer2>
<ItemNo></ItemNo>
<ItemQty></ItemNo>
<ItemNo></ItemNo>
<ItemQty></ItemNo>
<ItemNo></ItemNo>
<ItemQty></ItemNo>
</root>'


INSERT INTO #ItemDetails
                        ([header1]
                        ,[header2]
                        ,[ItemNo1]
                        ,[ItemQty1]
                        ,[ItemNo2]
                        ,[ItemQty2]
                        --upto ItemNo10 is possible
                        ,[ItemNo10]
                        ,[ItemQty210]
                        )
            SELECT
                X1.value('(header1/text())[1]','int') AS header1,
                X1.value('(header2/text())[1]','nvarchar(36)') AS header2,
                X1.value('(ItemNo/text())[1]','nvarchar(10)') AS ItemNo1,
                X1.value('(ItemQty/text())[1]','nvarchar(10)') AS ItemQty1,
                X1.value('(ItemNo/text())[2]','nvarchar(10)') AS ItemNo2,
                X1.value('(ItemQty/text())[2]','nvarchar(10)') AS ItemQty2,
                ----insert upto ItemNo10 
                X1.value('(ItemNo/text())[10]','nvarchar(10)') AS ItemNo10,
                X1.value('(ItemQty/text())[10]','nvarchar(10)') AS ItemQty10
            FROM
            @xml.nodes('/root') AS TEMPTABLE(X1)

header tags are there and Item tag can be repeated up to 10 tags. I want to insert those item no and qty in separate columns. Here I mentioned only 2 tags for Item but in real situation there are 5 tags in XML. So in worst case scenario it will be 50 columns. Is there any easy way to loop and insert those item data rather than inserting one by one to columns?

Thank you

Answer

…

declare @xml as xml
set @xml = '<root>
<header1>h1</header1>
<header2>h2</header2>
<ItemNo>A</ItemNo>
<ItemQty>1</ItemQty>
<ItemtagA>A1</ItemtagA>
<ItemNo>B</ItemNo>
<ItemQty>2</ItemQty>
<ItemtagB>B2</ItemtagB>
<ItemNo>C</ItemNo>
<ItemQty>3</ItemQty>
<ItemtagA>A3</ItemtagA>
<ItemNo>D</ItemNo>
<ItemQty>4</ItemQty>
<ItemtagC>C4</ItemtagC>
<ItemNo>E</ItemNo>
<ItemQty>5</ItemQty>
<ItemtagA>A5</ItemtagA>
<ItemtagB>B5</ItemtagB>
<ItemtagC>C5</ItemtagC>
<!-- ..... -->
</root>';

select *
from
(
select 
    e.value('text()[1]', 'varchar(20)') as val,
    -- if a "tag" appears for all items or is missing for all items.. then...
    --concat(e.value('local-name(.)', 'varchar(20)'), row_number() over (partition by e.value('local-name(.)', 'varchar(20)') order by n.e)) as colname

    -- if a tag is missing from a few items but exists in others then ...assume ItemNo denotes the start of an item
    concat(e.value('local-name(.)', 'varchar(20)'), nullif(count(case when e.value('local-name(.)', 'varchar(20)') = 'ItemNo' then 1 end) over(order by n.e), 0)) as colname

from @xml.nodes('root/*') as n(e)
) as t
pivot
(
max(val) for colname in 
(
    header1, header2, --header11,header21 ...when a tag exists for all or is missing for all items
    ItemNo1, ItemQty1, ItemtagA1, ItemtagB1, ItemtagC1, 
    ItemNo2, ItemQty2, ItemtagA2, ItemtagB2, ItemtagC2,
    ItemNo3, ItemQty3, ItemtagA3, ItemtagB3, ItemtagC3,
    ItemNo4, ItemQty4, ItemtagA4, ItemtagB4, ItemtagC4,
    ItemNo5, ItemQty5, ItemtagA5, ItemtagB5, ItemtagC5,
    ItemNo6, ItemQty6, ItemtagA6, ItemtagB6, ItemtagC6,
    /*....ItemNo7, ...., ItemtagC9*/
    ItemNo10, ItemQty10, ItemtagA10, ItemtagB10, ItemtagC10
)
) as pvt;