Automated SQL code to XML test data files using systems Tables

Question

Post reply

Automated SQL code to XML test data files using systems Tables

azdeji

SSCrazy

Points: 2535
More actions
August 18, 2013 at 3:34 pm

#282110

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
--BOOKINGdoes 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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

azdeji SSCrazy Points: 2535 More actions · Answer 1

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