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
