SQL Server 2012 XML – Combining Column Contents Without Loops

I have a table called sizeguide with the definition you can see below.

I am interested in the SizeGuideXML column, which is type XML.

I had a mission the objective was, for each SizeGuideDesc, get all the possible sizes for all the markets, plus height, weight, etc when available.

I could not achieve this in one select, so I had to go through it row by row unfortunately – as I am an advocate of the "think in sets" which is more a T-SQL approach but I am not so familiar with XML.

So the question:

Is there a way to get this done via T-SQL without row by row processing?
any ideas – for the next XML challenge.

this was the result:

the xml programming that I have used comes from here:

How to query the XML data type? – Example

--=====================================================
-- script to get all the sizeguides
-- marcelo miorelli
-- 23-sep-2015
--=====================================================


print @@servername
-- server must be SQLAPPLON1

use product
go



--sp_help 'sizeguide'
--sp_count 'sizeguide'


SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

declare @x xml
declare @i int
declare @z int
declare @the_name nvarchar(108)

declare @size table(
                     i int not null identity(1,1) primary key clustered
                    ,sizeGuideDesc nvarchar(108)
                    ,SizeGuideXML xml

                    )

declare @radhe table(
               i int not null identity(1,1) primary key clustered
               ,THE_NAME nvarchar(108)
               ,col0 varchar(108)
               ,col1 varchar(108)
               ,col2 varchar(108)
               ,col3 varchar(108)
               ,col4 varchar(108)
               ,col5 varchar(108)
               ,col6 varchar(108)
               ,col7 varchar(108)
               ,col8 varchar(108)
               ,col9 varchar(108)
               ,col10 varchar(108)

                    )



insert into @size
select SizeGuideDesc,
       SizeGuideXML
  from sizeguide

select @i = 1
      ,@z = @@ROWCOUNT

while @i < @z begin

    select @x = sizeguideXML
          ,@the_name = sizeGuideDesc
      from @size 
      where i = @i

      -------------------------------------------------------------------------------------
      insert into @radhe
        SELECT @the_name as the_name 
               ,null as col0
               ,null as col1
               ,null as col2
               ,null as col3
               ,null as col4
               ,null as col5
               ,null as col6
               ,null as col7
               ,null as col8
               ,null as col9
               ,null as col10
        Union All
        SELECT 
             NULL as the_name 
            ,  x.y.value('@Market', 'varchar(100)') as col0
            , x.y.value('HCol[1]/HValue[1]', 'varchar(100)') as col1
            , x.y.value('HCol[2]/HValue[1]', 'varchar(100)') as col2
            , x.y.value('HCol[3]/HValue[1]', 'varchar(100)') as col3
            , x.y.value('HCol[4]/HValue[1]', 'varchar(100)') as col4
            , x.y.value('HCol[5]/HValue[1]', 'varchar(100)') as col5
            , x.y.value('HCol[6]/HValue[1]', 'varchar(100)') as col6
            , x.y.value('HCol[7]/HValue[1]', 'varchar(100)') as col7
            , x.y.value('HCol[8]/HValue[1]', 'varchar(100)') as col8
            , x.y.value('HCol[9]/HValue[1]', 'varchar(100)') as col9
            , x.y.value('HCol[10]/HValue[1]', 'varchar(100)') as col10
        From @X.nodes('SizeGuide/Headers/HConv/HCols')x(y)
        Union All
        SELECT 
             NULL as the_name 
            ,   x.y.value('VTitle[1]', 'varchar(100)') 
            ,  x.y.value('VCols[1]/VCol[1]/VData[1]', 'varchar(100)') 
            ,  x.y.value('VCols[1]/VCol[2]/VData[1]', 'varchar(100)') 
            ,  x.y.value('VCols[1]/VCol[3]/VData[1]', 'varchar(100)') 
            ,  x.y.value('VCols[1]/VCol[4]/VData[1]', 'varchar(100)') 
            ,  x.y.value('VCols[1]/VCol[5]/VData[1]', 'varchar(100)') 
            ,  x.y.value('VCols[1]/VCol[6]/VData[1]', 'varchar(100)') 
            ,  x.y.value('VCols[1]/VCol[7]/VData[1]', 'varchar(100)') 
            ,  x.y.value('VCols[1]/VCol[8]/VData[1]', 'varchar(100)') 
            ,  x.y.value('VCols[1]/VCol[9]/VData[1]', 'varchar(100)') 
            ,  x.y.value('VCols[1]/VCol[10]/VData[1]', 'varchar(100)') 
        From @X.nodes('SizeGuide/Values/VRow')x(y)

      -------------------------------------------------------------------------------------

     select @i = @i + 1

end  -- while

select * from @radhe