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

[![enter image description here][1]][1]

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:
[![enter image description here][2]][2]

the xml programming that I have used comes from here:

[How to query the XML data type? - Example][3]





    --=====================================================
    -- 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


  [1]: https://i.sstatic.net/bnddI.jpg
  [2]: https://i.sstatic.net/lx86h.jpg
  [3]: http://dba.stackexchange.com/questions/115907/how-to-query-the-xml-data-type-example