Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Automated SQL code to XML test data files using systems Tables Expand / Collapse
Author
Message
Posted Sunday, August 18, 2013 3:34 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, August 18, 2014 2:46 AM
Points: 67, Visits: 554
I created a stored procedure which produces xml test data from any table using [INFORMATION_SCHEMA.COLUMNS]
The stored proc is supposed to create 3 xml tests files - Customers, Booking, and Ancillary.
Where a customer record number ends in a 1-10 integer value, create that number of Children
select Customer 1 then there will be 2 bookings and each of those bookings will have 2 ancillaries.
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

Customer seems to be working perfectly but I cannot seem to make the logic work with the child relationship with Booking and Ancillary plus the SQL code does not work.

Once the sql script is created it should be able generate test xml files for any table in the system.

Any insight would be helpful in how to fix the booking and ancillary problem, Thank you.

Create table
/****** Object:  Table [dbo].[Fake_INFORMATION_SCHEMA.COLUMNS]    Script Date: 08/18/2013 22:09:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Fake_INFORMATION_SCHEMA.COLUMNS](
[TABLE_CATALOG] [nvarchar](128) NULL,
[TABLE_SCHEMA] [nvarchar](128) NULL,
[TABLE_NAME] [sysname] NOT NULL,
[COLUMN_NAME] [sysname] NULL,
[ORDINAL_POSITION] [int] NULL,
[COLUMN_DEFAULT] [nvarchar](4000) NULL,
[IS_NULLABLE] [varchar](3) NULL,
[DATA_TYPE] [nvarchar](128) NULL,
[CHARACTER_MAXIMUM_LENGTH] [int] NULL,
[CHARACTER_OCTET_LENGTH] [int] NULL,
[NUMERIC_PRECISION] [tinyint] NULL,
[NUMERIC_PRECISION_RADIX] [smallint] NULL,
[NUMERIC_SCALE] [int] NULL,
[DATETIME_PRECISION] [smallint] NULL,
[CHARACTER_SET_CATALOG] [sysname] NULL,
[CHARACTER_SET_SCHEMA] [sysname] NULL,
[CHARACTER_SET_NAME] [sysname] NULL,
[COLLATION_CATALOG] [sysname] NULL,
[COLLATION_SCHEMA] [sysname] NULL,
[COLLATION_NAME] [sysname] NULL,
[DOMAIN_CATALOG] [sysname] NULL,
[DOMAIN_SCHEMA] [sysname] NULL,
[DOMAIN_NAME] [sysname] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Fake_INFORMATION_SCHEMA.COLUMNS] ([TABLE_CATALOG], [TABLE_SCHEMA], [TABLE_NAME], [COLUMN_NAME], [ORDINAL_POSITION], [COLUMN_DEFAULT], [IS_NULLABLE], [DATA_TYPE], [CHARACTER_MAXIMUM_LENGTH], [CHARACTER_OCTET_LENGTH], [NUMERIC_PRECISION], [NUMERIC_PRECISION_RADIX], [NUMERIC_SCALE], [DATETIME_PRECISION], [CHARACTER_SET_CATALOG], [CHARACTER_SET_SCHEMA], [CHARACTER_SET_NAME], [COLLATION_CATALOG], [COLLATION_SCHEMA], [COLLATION_NAME], [DOMAIN_CATALOG], [DOMAIN_SCHEMA], [DOMAIN_NAME]) VALUES (N'DWStaging', N'dbo', N'CustomerContact', N'AddressID', 1, NULL, N'YES', N'bigint', NULL, NULL, 19, 10, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
INSERT [dbo].[Fake_INFORMATION_SCHEMA.COLUMNS] ([TABLE_CATALOG], [TABLE_SCHEMA], [TABLE_NAME], [COLUMN_NAME], [ORDINAL_POSITION], [COLUMN_DEFAULT], [IS_NULLABLE], [DATA_TYPE], [CHARACTER_MAXIMUM_LENGTH], [CHARACTER_OCTET_LENGTH], [NUMERIC_PRECISION], [NUMERIC_PRECISION_RADIX], [NUMERIC_SCALE], [DATETIME_PRECISION], [CHARACTER_SET_CATALOG], [CHARACTER_SET_SCHEMA], [CHARACTER_SET_NAME], [COLLATION_CATALOG], [COLLATION_SCHEMA], [COLLATION_NAME], [DOMAIN_CATALOG], [DOMAIN_SCHEMA], [DOMAIN_NAME]) VALUES (N'DWStaging', N'dbo', N'CustomerContact', N'AddressOptin', 2, NULL, N'YES', N'bit', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
INSERT [dbo].[Fake_INFORMATION_SCHEMA.COLUMNS] ([TABLE_CATALOG], [TABLE_SCHEMA], [TABLE_NAME], [COLUMN_NAME], [ORDINAL_POSITION], [COLUMN_DEFAULT], [IS_NULLABLE], [DATA_TYPE], [CHARACTER_MAXIMUM_LENGTH], [CHARACTER_OCTET_LENGTH], [NUMERIC_PRECISION], [NUMERIC_PRECISION_RADIX], [NUMERIC_SCALE], [DATETIME_PRECISION], [CHARACTER_SET_CATALOG], [CHARACTER_SET_SCHEMA], [CHARACTER_SET_NAME], [COLLATION_CATALOG], [COLLATION_SCHEMA], [COLLATION_NAME], [DOMAIN_CATALOG], [DOMAIN_SCHEMA], [DOMAIN_NAME]) VALUES (N'DWStaging', N'dbo', N'CustomerContact', N'AddressLine1', 3, NULL, N'YES', N'nvarchar', 60, 120, NULL, NULL, NULL, NULL, NULL, NULL, N'UNICODE', NULL, NULL, N'Latin1_General_CI_AS', NULL, NULL, NULL)
INSERT [dbo].[Fake_INFORMATION_SCHEMA.COLUMNS] ([TABLE_CATALOG], [TABLE_SCHEMA], [TABLE_NAME], [COLUMN_NAME], [ORDINAL_POSITION], [COLUMN_DEFAULT], [IS_NULLABLE], [DATA_TYPE], [CHARACTER_MAXIMUM_LENGTH], [CHARACTER_OCTET_LENGTH], [NUMERIC_PRECISION], [NUMERIC_PRECISION_RADIX], [NUMERIC_SCALE], [DATETIME_PRECISION], [CHARACTER_SET_CATALOG], [CHARACTER_SET_SCHEMA], [CHARACTER_SET_NAME], [COLLATION_CATALOG], [COLLATION_SCHEMA], [COLLATION_NAME], [DOMAIN_CATALOG], [DOMAIN_SCHEMA], [DOMAIN_NAME]) VALUES (N'DWStaging', N'dbo', N'CustomerContact', N'AddressLine2', 4, NULL, N'YES', N'nvarchar', 60, 120, NULL, NULL, NULL, NULL, NULL, NULL, N'UNICODE', NULL, NULL, N'Latin1_General_CI_AS', NULL, NULL, NULL)
INSERT [dbo].[Fake_INFORMATION_SCHEMA.COLUMNS] ([TABLE_CATALOG], [TABLE_SCHEMA], [TABLE_NAME], [COLUMN_NAME], [ORDINAL_POSITION], [COLUMN_DEFAULT], [IS_NULLABLE], [DATA_TYPE], [CHARACTER_MAXIMUM_LENGTH], [CHARACTER_OCTET_LENGTH], [NUMERIC_PRECISION], [NUMERIC_PRECISION_RADIX], [NUMERIC_SCALE], [DATETIME_PRECISION], [CHARACTER_SET_CATALOG], [CHARACTER_SET_SCHEMA], [CHARACTER_SET_NAME], [COLLATION_CATALOG], [COLLATION_SCHEMA], [COLLATION_NAME], [DOMAIN_CATALOG], [DOMAIN_SCHEMA], [DOMAIN_NAME]) VALUES (N'DWStaging', N'dbo', N'AncillarySummary', N'CustomerID', 1, NULL, N'YES', N'bigint', NULL, NULL, 19, 10, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
INSERT [dbo].[Fake_INFORMATION_SCHEMA.COLUMNS] ([TABLE_CATALOG], [TABLE_SCHEMA], [TABLE_NAME], [COLUMN_NAME], [ORDINAL_POSITION], [COLUMN_DEFAULT], [IS_NULLABLE], [DATA_TYPE], [CHARACTER_MAXIMUM_LENGTH], [CHARACTER_OCTET_LENGTH], [NUMERIC_PRECISION], [NUMERIC_PRECISION_RADIX], [NUMERIC_SCALE], [DATETIME_PRECISION], [CHARACTER_SET_CATALOG], [CHARACTER_SET_SCHEMA], [CHARACTER_SET_NAME], [COLLATION_CATALOG], [COLLATION_SCHEMA], [COLLATION_NAME], [DOMAIN_CATALOG], [DOMAIN_SCHEMA], [DOMAIN_NAME]) VALUES (N'DWStaging', N'dbo', N'AncillarySummary', N'AddressID', 2, NULL, N'YES', N'bigint', NULL, NULL, 19, 10, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
INSERT [dbo].[Fake_INFORMATION_SCHEMA.COLUMNS] ([TABLE_CATALOG], [TABLE_SCHEMA], [TABLE_NAME], [COLUMN_NAME], [ORDINAL_POSITION], [COLUMN_DEFAULT], [IS_NULLABLE], [DATA_TYPE], [CHARACTER_MAXIMUM_LENGTH], [CHARACTER_OCTET_LENGTH], [NUMERIC_PRECISION], [NUMERIC_PRECISION_RADIX], [NUMERIC_SCALE], [DATETIME_PRECISION], [CHARACTER_SET_CATALOG], [CHARACTER_SET_SCHEMA], [CHARACTER_SET_NAME], [COLLATION_CATALOG], [COLLATION_SCHEMA], [COLLATION_NAME], [DOMAIN_CATALOG], [DOMAIN_SCHEMA], [DOMAIN_NAME]) VALUES (N'DWStaging', N'dbo', N'AncillarySummary', N'EmailAddress', 3, NULL, N'YES', N'nvarchar', 255, 510, NULL, NULL, NULL, NULL, NULL, NULL, N'UNICODE', NULL, NULL, N'Latin1_General_CI_AS', NULL, NULL, NULL)
INSERT [dbo].[Fake_INFORMATION_SCHEMA.COLUMNS] ([TABLE_CATALOG], [TABLE_SCHEMA], [TABLE_NAME], [COLUMN_NAME], [ORDINAL_POSITION], [COLUMN_DEFAULT], [IS_NULLABLE], [DATA_TYPE], [CHARACTER_MAXIMUM_LENGTH], [CHARACTER_OCTET_LENGTH], [NUMERIC_PRECISION], [NUMERIC_PRECISION_RADIX], [NUMERIC_SCALE], [DATETIME_PRECISION], [CHARACTER_SET_CATALOG], [CHARACTER_SET_SCHEMA], [CHARACTER_SET_NAME], [COLLATION_CATALOG], [COLLATION_SCHEMA], [COLLATION_NAME], [DOMAIN_CATALOG], [DOMAIN_SCHEMA], [DOMAIN_NAME]) VALUES (N'DWStaging', N'dbo', N'AncillarySummary', N'AncillaryBrandCode', 4, NULL, N'YES', N'nvarchar', 30, 60, NULL, NULL, NULL, NULL, NULL, NULL, N'UNICODE', NULL, NULL, N'Latin1_General_CI_AS', NULL, NULL, NULL)
INSERT [dbo].[Fake_INFORMATION_SCHEMA.COLUMNS] ([TABLE_CATALOG], [TABLE_SCHEMA], [TABLE_NAME], [COLUMN_NAME], [ORDINAL_POSITION], [COLUMN_DEFAULT], [IS_NULLABLE], [DATA_TYPE], [CHARACTER_MAXIMUM_LENGTH], [CHARACTER_OCTET_LENGTH], [NUMERIC_PRECISION], [NUMERIC_PRECISION_RADIX], [NUMERIC_SCALE], [DATETIME_PRECISION], [CHARACTER_SET_CATALOG], [CHARACTER_SET_SCHEMA], [CHARACTER_SET_NAME], [COLLATION_CATALOG], [COLLATION_SCHEMA], [COLLATION_NAME], [DOMAIN_CATALOG], [DOMAIN_SCHEMA], [DOMAIN_NAME]) VALUES (N'DWStaging', N'dbo', N'BookingSummary', N'AccommodationTypeCode', 1, NULL, N'YES', N'nvarchar', 20, 40, NULL, NULL, NULL, NULL, NULL, NULL, N'UNICODE', NULL, NULL, N'Latin1_General_CI_AS', NULL, NULL, NULL)
INSERT [dbo].[Fake_INFORMATION_SCHEMA.COLUMNS] ([TABLE_CATALOG], [TABLE_SCHEMA], [TABLE_NAME], [COLUMN_NAME], [ORDINAL_POSITION], [COLUMN_DEFAULT], [IS_NULLABLE], [DATA_TYPE], [CHARACTER_MAXIMUM_LENGTH], [CHARACTER_OCTET_LENGTH], [NUMERIC_PRECISION], [NUMERIC_PRECISION_RADIX], [NUMERIC_SCALE], [DATETIME_PRECISION], [CHARACTER_SET_CATALOG], [CHARACTER_SET_SCHEMA], [CHARACTER_SET_NAME], [COLLATION_CATALOG], [COLLATION_SCHEMA], [COLLATION_NAME], [DOMAIN_CATALOG], [DOMAIN_SCHEMA], [DOMAIN_NAME]) VALUES (N'DWStaging', N'dbo', N'BookingSummary', N'AccomodationArrivalDate', 2, NULL, N'YES', N'datetime', NULL, NULL, NULL, NULL, NULL, 3, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
INSERT [dbo].[Fake_INFORMATION_SCHEMA.COLUMNS] ([TABLE_CATALOG], [TABLE_SCHEMA], [TABLE_NAME], [COLUMN_NAME], [ORDINAL_POSITION], [COLUMN_DEFAULT], [IS_NULLABLE], [DATA_TYPE], [CHARACTER_MAXIMUM_LENGTH], [CHARACTER_OCTET_LENGTH], [NUMERIC_PRECISION], [NUMERIC_PRECISION_RADIX], [NUMERIC_SCALE], [DATETIME_PRECISION], [CHARACTER_SET_CATALOG], [CHARACTER_SET_SCHEMA], [CHARACTER_SET_NAME], [COLLATION_CATALOG], [COLLATION_SCHEMA], [COLLATION_NAME], [DOMAIN_CATALOG], [DOMAIN_SCHEMA], [DOMAIN_NAME]) VALUES (N'DWStaging', N'dbo', N'BookingSummary', N'AddressCountry', 3, NULL, N'YES', N'nvarchar', 30, 60, NULL, NULL, NULL, NULL, NULL, NULL, N'UNICODE', NULL, NULL, N'Latin1_General_CI_AS', NULL, NULL, NULL)
INSERT [dbo].[Fake_INFORMATION_SCHEMA.COLUMNS] ([TABLE_CATALOG], [TABLE_SCHEMA], [TABLE_NAME], [COLUMN_NAME], [ORDINAL_POSITION], [COLUMN_DEFAULT], [IS_NULLABLE], [DATA_TYPE], [CHARACTER_MAXIMUM_LENGTH], [CHARACTER_OCTET_LENGTH], [NUMERIC_PRECISION], [NUMERIC_PRECISION_RADIX], [NUMERIC_SCALE], [DATETIME_PRECISION], [CHARACTER_SET_CATALOG], [CHARACTER_SET_SCHEMA], [CHARACTER_SET_NAME], [COLLATION_CATALOG], [COLLATION_SCHEMA], [COLLATION_NAME], [DOMAIN_CATALOG], [DOMAIN_SCHEMA], [DOMAIN_NAME]) VALUES (N'DWStaging', N'dbo', N'BookingSummary', N'AddressID', 4, NULL, N'YES', N'bigint', NULL, NULL, 19, 10, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)

Based on the Master XML I created the following SQLto XML test datat script:

---SQL Customer --it seems works
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 in ('bigint', 'int', 'decimal')
then cast(@CustomerCounter as varchar)
when COLUMN_NAME = 'CustomerDOB' or COLUMN_NAME like '%date'
then cast(dateadd(month, @CustomerCounter, cast('01-12-2012' as date)) as nvarchar)
when COLUMN_NAME like '%Time'
then '20:59:15'
when COLUMN_NAME = 'LandlinePhoneSource'
then 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	does not work 

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)
when data_Type in ('bit') ---case when @CustomerCounter % 2 = 0 then '1' else '0'
then 'Booking' +''+ 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  also does not work
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)
when data_Type in ('bigint', 'int', 'decimal')
then cast(@Ancillary as varchar)
when data_Type in ('bit') ---case when @CustomerCounter % 2 = 0 then '1' else '0'
then 'Ancillary' +''+ 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'

---the 1 is the number of customers selected, it could be any number.
exec [dbo].[HealthCreateTestEntities] 1

Post #1485619
Posted Monday, August 19, 2013 12:24 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, August 18, 2014 2:46 AM
Points: 67, Visits: 554
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
Post #1485658
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse