begin tran
DECLARE @xml as xml,@delimiter as varchar(10)
declare @childLocationCode varchar(50)
SET @delimiter ='-'
--declare @newChildLocationCode varchar(50)
--declare @newChildLocationName varchar(50)
declare @childRack varchar(10)
declare @childShelf varchar(10)
declare @childSection varchar(10)
declare @childRow varchar(10)
declare @childLocationId uniqueidentifier
declare @childUpdate bit
declare @locationId uniqueidentifier
declare kurParent cursor for
Select locationId from [wms].[location] where [locationTypeID] = 'warehouse' --and locationid = '17ABC0C1-8B66-E411-B829-083E8E98C8A4'
open kurParent
fetch next from kurParent
into @locationId
while @@FETCH_STATUS = 0
begin
print '2'
declare kur cursor
for Select l.locationCode, l.locationId
from [wms].[location] lp
join [wms].[location] l on lp.locationID = l.parentID
where lp.locationID = @locationId;
open kur
fetch next from kur
into @childLocationCode,@childLocationId
while @@FETCH_STATUS = 0
begin
print '3'
set @childUpdate = 0
if @childLocationCode is not null
begin
--select @childLocationCode '@childLocationCode'
SET @xml = cast(('<X>'+replace(@childLocationCode,@delimiter ,'</X><X>')+'</X>') as xml)
select row_number() over (order by (select null)) r, xn.value into #tempData from(
SELECT N.value('.', 'varchar(10)') as value FROM @xml.nodes('X') as T(N))xn
set @childRack = null
set @childShelf = null
set @childSection = null
set @childRow = null
select @childRack = value from #tempData where r=2
select @childShelf = value from #tempData where r=3
select @childSection = value from #tempData where r=4
select @childRow = value from #tempData where r=5
--select @childRack '@childRack', @childShelf '@childShelf', @childSection '@childSection', @childRow '@childRow'
drop table #tempData
--if @childUpdate = 1
Update wms.location set Rack = @childRack, Shelf = @childShelf , Section = @childSection, [Row] = @childRow where locationID = @childLocationId
end
fetch next from kur
into @childLocationCode,@childLocationId
end
close kur
deallocate kur
fetch next from kurParent
into @locationId
end
close kurParent
deallocate kurParent
--select * from wms.location where [locationTypeID] = 'warehouse'
rollback
--SELECT PARSENAME(REPLACE('Hello John Smith', ' ', '.'), 2)
--SELECT PARSENAME(REPLACE('Hello-John-Smith', '-', '.'), 1)
--DECLARE @xml as xml,@str as varchar(100),@delimiter as varchar(10)
--SET @str='A,B,C,D,E'
--SET @delimiter =','
--SET @xml = cast(('<X>'+replace(@str,@delimiter ,'</X><X>')+'</X>') as xml)
--SELECT @xml
--DECLARE @xml as xml,@str as varchar(100),@delimiter as varchar(10)
--SET @str='Z,B,C,D,A'
--SET @delimiter =','
--SET @xml = cast(('<X>'+replace(@str,@delimiter ,'</X><X>')+'</X>') as xml)
--select row_number() over (order by (select null)) r, xn.value from(
--SELECT N.value('.', 'varchar(10)') as value FROM @xml.nodes('X') as T(N))xn
