1

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

enter image description here

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

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
0
3

I used your @x from your previous question in my query below. I create a sample table with 2 rows that should behave just like your real table although my Name value is similar because I used @x twice (ID is not similar):

declare @table table(id int, value xml);
insert into @table(id, value) values(1, @x), (2, @x);

Select t.id
    , x.y.value('@title', 'varchar(100)') as 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
From @table t
Cross Apply value.nodes('SizeGuide')x(y)
Union All
Select t.id
    , null
    , 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
From @table t
Cross Apply value.nodes('SizeGuide/Headers/HConv/HCols')x(y)
Order By Id, Name desc, col0

Ouput:

id | name       | col0 | col1    | col2 | col3 | col4  | col5   | col6   | col7
1  | Baby - All | NULL | NULL    | NULL | NULL | NULL  | NULL   | NULL   | NULL
1  | NULL       | AT   | newborn | 0-3M | 3-6M | 6-12M | 12-18M | 18-24M | 2-3J
1  | NULL       | DE   | newborn | 0-3M | 3-6M | 6-12M | 12-18M | 18-24M | 2-3J
1  | NULL       | FR   | newborn | 0-3m | 3-6m | 6-12m | 12-18m | 18-24m | 2-3a
1  | NULL       | UK   | newborn | 0-3m | 3-6m | 6-12m | 12-18m | 18-24m | 2-3y
1  | NULL       | US   | newborn | 0-3m | 3-6m | 6-12m | 12-18m | 18-24m | 2-3y
2  | Baby - All | NULL | NULL    | NULL | NULL | NULL  | NULL   | NULL   | NULL
2  | NULL       | AT   | newborn | 0-3M | 3-6M | 6-12M | 12-18M | 18-24M | 2-3J
2  | NULL       | DE   | newborn | 0-3M | 3-6M | 6-12M | 12-18M | 18-24M | 2-3J
2  | NULL       | FR   | newborn | 0-3m | 3-6m | 6-12m | 12-18m | 18-24m | 2-3a
2  | NULL       | UK   | newborn | 0-3m | 3-6m | 6-12m | 12-18m | 18-24m | 2-3y
2  | NULL       | US   | newborn | 0-3m | 3-6m | 6-12m | 12-18m | 18-24m | 2-3y

I didnt used all columns and row from your xml in order to keep this shorter but you can easily add them.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.