Iterating through results to make multiple rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Iterating through results to make multiple rows

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-11-10 : 05:28:36

I have to transfer the data in one table to a denormalised version. The original table contains XML fragments detailing all the information for a product. Depending on the type of product, the xml fragment can contain references for multiple items. Each product, whether it consists of multiple or single items will have a quantity of 1 or more. I need to shred this xml into a table, with 1 entry per item.

For example

'<OrderLine CatalogueItemReference = "211-111" IsWarranty = "0" Quantity = "1" IsBundle = "1">
	<LineItems>
		<LineItem TPNB = "122a" Description = "Table" CatalogueItemReference = "212-122" Quantity = "1"/>
		<LineItem TPNB = "123b" Description = "Chair" CatalogueItemReference = "213-123" Quantity = "4"/>
	</LineItems>
</OrderLine>'

The final result would look something like this:


TPNB	Description
122a	Table
123b	Chair
123b	Chair
123b	Chair
123b	Chair

If the quantity for the OrderLine was 2, then it would look as above, but double the entries.

I have written a scaled down prototype of what I need to achieve. I don't really know how to do the above without using iteration (which I would prefer to avoid if possible).


if OBJECT_ID('bms.TempBasketLine')is not null
drop table bms.TempBasketLine

Create table bms.TempBasketLine
(
  BasketLineID int identity primary key
, PartOrderID int
, CatalogueItemreference varchar (10)
, ParentCatalogueItemreference varchar (10)
, CatalogueItem xml
)

-- Insert Item of quantity of 3
insert into bms.TempBasketLine
(
  PartOrderID
, CatalogueItemreference 
, ParentCatalogueItemreference
, CatalogueItem 
)
select 107,'111-333',null,
'<OrderLine CatalogueItemReference = "111-333" IsWarranty = "0" Quantity = "3" IsBundle = "0">
	<LineItems>
		<LineItem TPNB = "679p" Description = "IPod" CatalogueItemReference = "111-333"  Quantity = "1"/>
	</LineItems>
</OrderLine>'

-- Insert Bundle
insert into bms.TempBasketLine
(
  PartOrderID
, CatalogueItemreference 
, ParentCatalogueItemreference
, CatalogueItem 
)
select 107,'211-111',null,
'<OrderLine CatalogueItemReference = "211-111" IsWarranty = "0" Quantity = "1" IsBundle = "1">
	<LineItems>
		<LineItem TPNB = "122a" Description = "Table" CatalogueItemReference = "212-122" Quantity = "1"/>
		<LineItem TPNB = "123b" Description = "Chair" CatalogueItemReference = "213-123" Quantity = "4"/>
	</LineItems>
</OrderLine>'

-- Insert a bundle: Parent Item
insert into bms.TempBasketLine
(
  PartOrderID
, CatalogueItemreference 
, ParentCatalogueItemreference
, CatalogueItem 
)
select 107,'111-222',null,
'<OrderLine CatalogueItemReference = "111-222" IsWarranty = "0" IsBundle = "0" Quantity = "1">
	<LineItems>
		<LineItem TPNB = "335f" Description = "Fridge" CatalogueItemReference = "111-123" Quantity = "1"/>
	</LineItems>
</OrderLine>'

-- Child Item
insert into bms.TempBasketLine
(
  PartOrderID
, CatalogueItemreference 
, ParentCatalogueItemreference
, CatalogueItem 
)
select 108,'111-123','111-222',
'<OrderLine CatalogueItemReference = "111-123" ParentCatalogueItemReference = "111-222" IsWarranty = "1" IsBundle = "0" Quantity = "1">
	<LineItems>
		<LineItem TPNB = "445w" Description = "Warranty" CatalogueItemReference = "111-123" Quantity = "1"/>
	</LineItems>
</OrderLine>'

select * from bms.TempBasketLine

select 
  PartOrderID
, CatalogueItemReference
, ParentCatalogueItemReference
, CatalogueItem.value('(//OrderLine/@Quantity)[1]','int')as Quantity
, CatalogueItem.value('(//OrderLine/@IsBundle)[1]','bit')as IsBundle
, CatalogueItem.value('(//OrderLine/@IsWarranty)[1]','bit')as IsWarranty
, li.value('@TPNB','varchar(10)')as LineItemTPNB
, li.value('@Description','varchar(max)')as LineItemDescription
, li.value('@CatalogueItemReference','varchar(10)')as LineItemCatalogueItem
, li.value('@Quantity','int')as LineItemQuantity
into #BasketLine
from bms.TempBasketLine
CROSS APPLY CatalogueItem.nodes('//OrderLine/LineItems/LineItem')as L(li)


select * from #BasketLine

drop table #BasketLine

Any help would be appreciated

A product that consists of multiple items (for example, a table and 4 chairs) will have one entry as a product, but multiple line items.

Hearty head pats

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-10 : 05:33:55

[code]-- prepare test data
declare @test table (LineItem varchar(20), Description varchar(20), Quantity int)

insert @test
select '122a', 'Table', 1 union all
select '123b', 'Chair', 4

select * from @test

-- do the work
select t.lineitem,
t.description
from @test t
inner join (
select number
from master..spt_values
where name is null
) q on q.number < t.quantity[/code]

Peter Larsson
Helsingborg, Sweden

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-11-10 : 08:24:20

Ok, I have a solution. It does use iteration, but not for every product and then for every row:


if OBJECT_ID('bms.TempBasketLine')is not null
drop table bms.TempBasketLine

Create table bms.TempBasketLine
(
  BasketLineID int identity primary key
, PartOrderID int
, CatalogueItemreference varchar (10)
, ParentCatalogueItemreference varchar (10)
, CatalogueItem xml
)

-- Insert Item of quantity of 5
insert into bms.TempBasketLine
(
  PartOrderID
, CatalogueItemreference 
, ParentCatalogueItemreference
, CatalogueItem 
)
select 107,'666-333',null,
'<OrderLine CatalogueItemReference = "666-333" IsWarranty = "0" Quantity = "5" IsBundle = "0">
	<LineItems>
		<LineItem TPNB = "886k" Description = "Kettle" CatalogueItemReference = "666-333"  Quantity = "1"/>
	</LineItems>
</OrderLine>'

-- Insert Item of quantity of 3
insert into bms.TempBasketLine
(
  PartOrderID
, CatalogueItemreference 
, ParentCatalogueItemreference
, CatalogueItem 
)
select 107,'111-333',null,
'<OrderLine CatalogueItemReference = "111-333" IsWarranty = "0" Quantity = "3" IsBundle = "0">
	<LineItems>
		<LineItem TPNB = "679p" Description = "IPod" CatalogueItemReference = "111-333"  Quantity = "1"/>
	</LineItems>
</OrderLine>'

-- Insert Item of quantity of 2
insert into bms.TempBasketLine
(
  PartOrderID
, CatalogueItemreference 
, ParentCatalogueItemreference
, CatalogueItem 
)
select 107,'444-333',null,
'<OrderLine CatalogueItemReference = "444-333" IsWarranty = "0" Quantity = "2" IsBundle = "0">
	<LineItems>
		<LineItem TPNB = "321t" Description = "TV" CatalogueItemReference = "444-333"  Quantity = "1"/>
	</LineItems>
</OrderLine>'

-- Insert Bundle
insert into bms.TempBasketLine
(
  PartOrderID
, CatalogueItemreference 
, ParentCatalogueItemreference
, CatalogueItem 
)
select 107,'211-111',null,
'<OrderLine CatalogueItemReference = "211-111" IsWarranty = "0" Quantity = "2" IsBundle = "1">
	<LineItems>
		<LineItem TPNB = "122a" Description = "Table" CatalogueItemReference = "212-122" Quantity = "1"/>
		<LineItem TPNB = "123b" Description = "Chair" CatalogueItemReference = "213-123" Quantity = "4"/>
	</LineItems>
</OrderLine>'

-- Insert a Parent Item
insert into bms.TempBasketLine
(
  PartOrderID
, CatalogueItemreference 
, ParentCatalogueItemreference
, CatalogueItem 
)
select 107,'111-222',null,
'<OrderLine CatalogueItemReference = "111-222" IsWarranty = "0" IsBundle = "0" Quantity = "1">
	<LineItems>
		<LineItem TPNB = "335f" Description = "Fridge" CatalogueItemReference = "111-123" Quantity = "1"/>
	</LineItems>
</OrderLine>'

-- Child Item
insert into bms.TempBasketLine
(
  PartOrderID
, CatalogueItemreference 
, ParentCatalogueItemreference
, CatalogueItem 
)
select 108,'111-123','111-222',
'<OrderLine CatalogueItemReference = "111-123" ParentCatalogueItemReference = "111-222" IsWarranty = "1" IsBundle = "0" Quantity = "1">
	<LineItems>
		<LineItem TPNB = "445w" Description = "Warranty" CatalogueItemReference = "111-123" Quantity = "1"/>
	</LineItems>
</OrderLine>'


select 
  PartOrderID
, CatalogueItemReference
, ParentCatalogueItemReference
, CatalogueItem.value('(//OrderLine/@Quantity)[1]','int')as Quantity
, CatalogueItem.value('(//OrderLine/@IsBundle)[1]','bit')as IsBundle
, CatalogueItem.value('(//OrderLine/@IsWarranty)[1]','bit')as IsWarranty
, li.value('@TPNB','varchar(10)')as LineItemTPNB
, li.value('@Description','varchar(max)')as LineItemDescription
, li.value('@CatalogueItemReference','varchar(10)')as LineItemCatalogueItem
, li.value('@Quantity','int')as LineItemQuantity
into #BasketLine
from bms.TempBasketLine
CROSS APPLY CatalogueItem.nodes('//OrderLine/LineItems/LineItem')as L(li)

--select * from #BasketLine

insert into #BasketLine
select distinct
  PartOrderID
, CatalogueItemReference
, ParentCatalogueItemReference
, CatalogueItem.value('(//OrderLine/@Quantity)[1]','int')as Quantity
, CatalogueItem.value('(//OrderLine/@IsBundle)[1]','bit')as IsBundle
, CatalogueItem.value('(//OrderLine/@IsWarranty)[1]','bit')as IsWarranty
, null as LineItemTPNB
, null as LineItemDescription
, null as LineItemCatalogueItem
, 1
from bms.TempBasketLine
CROSS APPLY CatalogueItem.nodes('//OrderLine/LineItems/LineItem')as L(li)
where CatalogueItem.value('(//OrderLine/@IsBundle)[1]','bit')=1

--select * from #BasketLine

declare @count int
declare @maxquantity int

set @count = 0
set @maxquantity = (select max(quantity*LineItemQuantity) from #BasketLine)

while @maxquantity <> 0
begin
	insert into #BasketLine
	(
	  PartOrderID
	, CatalogueItemReference
	, ParentCatalogueItemReference
	, Quantity
	, IsBundle
	, IsWarranty
	, LineItemTPNB
	, LineItemDescription
	, LineItemCatalogueItem
	, LineItemQuantity
	)
	select  distinct  PartOrderID
			, CatalogueItemReference
			, ParentCatalogueItemReference
			, 1
			, IsBundle
			, IsWarranty
			, LineItemTPNB
			, LineItemDescription
			, LineItemCatalogueItem
			, LineItemQuantity
	from #BasketLine where ((Quantity*LineItemQuantity) - @count) > 1

--select * from #BasketLine

set @count = @count + 1
set @maxquantity = @maxquantity - 1
end

select * from #BasketLine
order by LineItemTPNB

drop table #BasketLine
drop table bms.TempBasketLine

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-11-10 : 08:29:39

Hey Peter

Sorry, didn't realise I had a reply. Thankyou for your efforts though, that looks like a nice clean way to do it (nto sure what master..spt_values is, but am about to look it up - thankyou very much

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-11-10 : 09:04:07

Peter - you are awesome!

That code works like an absolute dream. The only difference is that I've created my own number table in memory.

love your work!!!

Hearty head pats

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-14 : 06:13:17

Thanks. However, if there are more than 255 items the algorithm will fail.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources