It possible i over complicated the sample sql code, here a simper version-

Create procedure [dbo].[HealthCreateTestEntities]

(@CustomersTocreate int)

as

begin

declare @CustomerCounter int, @ChildElements int, @Bookings int, @Ancillary int

declare @XMLFileCustomers table

(

?RecordNumber int identity (1,1)

?,XMLRecordType varchar(20)

?,XMLRecord varchar(2000)

)

select @CustomerCounter = 1

--Customers

insert @XMLFileCustomers values('Customer','<?xml version="1.0"?>')

insert @XMLFileCustomers values('Customer','<CustomerContacts xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="CustomerContactSummary.xsd">')

while @CustomerCounter <= @CustomersTocreate

begin

select @ChildElements =

case

?when right(cast(@CustomerCounter as varchar(10)),1) = 1 then 1

?when right(cast(@CustomerCounter as varchar(10)),1) = 2 then 2

?when right(cast(@CustomerCounter as varchar(10)),1) = 3 then 3

?when right(cast(@CustomerCounter as varchar(10)),1) = 4 then 4

?when right(cast(@CustomerCounter as varchar(10)),1) = 5 then 5

?when right(cast(@CustomerCounter as varchar(10)),1) = 6 then 6

?when right(cast(@CustomerCounter as varchar(10)),1) = 7 then 7

?when right(cast(@CustomerCounter as varchar(10)),1) = 8 then 8

?when right(cast(@CustomerCounter as varchar(10)),1) = 9 then 9

?when right(cast(@CustomerCounter as varchar(10)),1) = 0 then 10

end

select @Bookings = @ChildElements, @Ancillary = @ChildElements

--Customer

insert @XMLFileCustomers values('Customer','<CustomerContact>')

?insert @XMLFileCustomers

?select 'Customer','<' + COLUMN_NAME + '>' +

?case

???when COLUMN_NAME like '%code'

???then COLUMN_NAME + cast(@CustomerCounter as varchar)

when data_Type = 'bit'

???then

???case

????when @CustomerCounter % 2 = 0 then '1'

??? else '0'

??? end

?? ELSE

??COLUMN_NAME + cast(@CustomerCounter as varchar)

??END

?+ '</' + COLUMN_NAME + '>' from [Fake_INFORMATION_SCHEMA.COLUMNS]

?where table_name = 'CustomerContact'

?

set @CustomerCounter = @CustomerCounter + 1

?

?END

insert @XMLFileCustomers values('Customer','</ShortBookingSummaries>')

insert @XMLFileCustomers values('Customer','</CustomerContact>')

insert @XMLFileCustomers values('Customer','</CustomerContacts>')

?

select * from @XMLFileCustomers

where XMLRecordType = 'Customer'

end

--booking

declare @XMLFileBookings table

(

?RecordNumber int identity (1,1)

?,XMLRecordType varchar(20)

?,XMLRecord varchar(2000)

)

select @CustomerCounter = 1

while @CustomerCounter <= @CustomersTocreate

begin

select @ChildElements =

case

?when right(cast(@CustomerCounter as varchar(10)),1) = 1 then 1

?when right(cast(@CustomerCounter as varchar(10)),1) = 2 then 2

?when right(cast(@CustomerCounter as varchar(10)),1) = 3 then 3

?when right(cast(@CustomerCounter as varchar(10)),1) = 4 then 4

?when right(cast(@CustomerCounter as varchar(10)),1) = 5 then 5

?when right(cast(@CustomerCounter as varchar(10)),1) = 6 then 6

?when right(cast(@CustomerCounter as varchar(10)),1) = 7 then 7

?when right(cast(@CustomerCounter as varchar(10)),1) = 8 then 8

?when right(cast(@CustomerCounter as varchar(10)),1) = 9 then 9

?when right(cast(@CustomerCounter as varchar(10)),1) = 0 then 10

end

select @Bookings = @ChildElements, @Ancillary = @ChildElements

?while @Bookings > 0

?begin

? insert @XMLFileBookings values('BookingStartTag','<Booking>')

??insert @XMLFileBookings values('Booking','<BookingRef>BR_'+cast(@CustomerCounter as varchar)+'_'+cast(@Bookings as varchar)+'</BookingRef><TotalRevenue>'+cast(@Bookings * 100 as varchar)+'</TotalRevenue>')

??Insert @XMLFileBookings

??select 'Booking','<' + COLUMN_NAME + '>' +

?case

??when COLUMN_NAME like '%code'

???then COLUMN_NAME + cast(@Bookings as varchar)

??when data_Type in ('bigint', 'int', 'decimal')

???then cast(@Bookings as varchar)

??

??else

??COLUMN_NAME + cast(@Bookings as varchar)

??END

?+ '</' + COLUMN_NAME + '>' from [Fake_INFORMATION_SCHEMA.COLUMNS]

?where table_name = 'BookingSummary'

?set @CustomerCounter = @CustomerCounter + 1

end

select @Bookings = @Bookings - 1

select @Ancillary = @ChildElements

select * from @XMLFileBookings

where XMLRecordType = 'Booking'

end

--ANCILLARY?

select @CustomerCounter = 1

declare @XMLFileAncillaries table

(

? RecordNumber int identity (1,1)

?,XMLRecordType varchar(20)

?,XMLRecord varchar(2000)

)

while @CustomerCounter <= @CustomersTocreate

begin

select @ChildElements =

case

?when right(cast(@CustomerCounter as varchar(10)),1) = 1 then 1

?when right(cast(@CustomerCounter as varchar(10)),1) = 2 then 2

?when right(cast(@CustomerCounter as varchar(10)),1) = 3 then 3

?when right(cast(@CustomerCounter as varchar(10)),1) = 4 then 4

?when right(cast(@CustomerCounter as varchar(10)),1) = 5 then 5

?when right(cast(@CustomerCounter as varchar(10)),1) = 6 then 6

?when right(cast(@CustomerCounter as varchar(10)),1) = 7 then 7

?when right(cast(@CustomerCounter as varchar(10)),1) = 8 then 8

?when right(cast(@CustomerCounter as varchar(10)),1) = 9 then 9

?when right(cast(@CustomerCounter as varchar(10)),1) = 0 then 10

end

??while @Ancillary > 0

?

???begin

????insert @XMLFileAncillaries values('AncillaryStartTag','<Ancillary>')

????insert @XMLFileAncillaries values('Ancillary','<BookingRef>BR_'+cast(@CustomerCounter as varchar)+'_'+cast(@Ancillary as varchar)+'</BookingRef><AncillaryRevenue>'+cast(@Ancillary * 100 as varchar)+'</AncillaryRevenue>')

????Insert @XMLFileAncillaries

??select 'Ancillary','<' + COLUMN_NAME + '>' +

?case

??when COLUMN_NAME like '%code'

???then COLUMN_NAME + cast(@Ancillary as varchar)

?????then cast(@Ancillary as varchar)

??else

??COLUMN_NAME + cast(@Ancillary as varchar)

??END

?+ '</' + COLUMN_NAME + '>' from [Fake_INFORMATION_SCHEMA.COLUMNS]

?where table_name = 'AncillarySummary'

?insert @XMLFileAncillaries values('Ancillary','</Ancillary>')

????select @Ancillary = @Ancillary - 1

???end

??select @Bookings = @Bookings - 1

??select @Ancillary = @ChildElements

?end

?set @CustomerCounter = @CustomerCounter + 1

end

select * from @XMLFileAncillaries

where XMLRecordType = 'Ancillary'

exec [dbo].[HealthCreateTestEntities] 1

Hopefully by removing a large part of the case statement the sample code is easier to understand and fix the while loop for

Where a customer record number ends in a 1-10 integer value, create that number of Children

E.g. Customer 2 will have 2 bookings and each of those bookings will have 2 ancillaries.

E.g. Customer 3 will have 3 bookings and each of those bookings will have 3 ancillaries

Thanks again