set nocount on
-- Sample XML
declare @xmlData XML, @xpath varchar(250)
set @xmlData =
'
<xml>
<schema>
<table id="Wholesale.EmailSignUp" schema="Wholesale" name="EmailSignUp">
<col id="EmailAddress" type="varchar" length="150" nullable="false" quote="''" />
<col id="CompanyName" type="varchar" length="150" nullable="true" quote="''" />
<col id="FirstName" type="varchar" length="25" nullable="true" quote="''" />
<col id="LastName" type="varchar" length="25" nullable="true" quote="''" />
<col id="PositionAtCompany" type="varchar" length="100" nullable="true" quote="''" />
<col id="EntryDate" type="datetime" length="25" nullable="false" quote="''" />
<col id="UpdateDate" type="datetime" length="25" nullable="false" quote="''" />
</table>
<table id="dbo.ProductsInfo" schema="dbo" name="ProductsInfo">
<col id="ProductInfoId" type="uniquidentifier" length="" nullable="false" quote="''" />
<col id="FieldName" type="varchar" length="50" nullable="true" quote="''" />
<col id="DisplayText" type="varchar" length="100" nullable="true" quote="''" />
<col id="Value" type="int" length="" nullable="true" quote="" />
</table>
</schema>
<data>
<table id="Wholesale.EmailSignUp">
<row id="1">
<col id="EmailAddress" value="joe.pesch@impaccompanies.com" />
<col id="CompanyName" value="Impac" />
<col id="FirstName" value="Joe" />
<col id="LastName" value="Pesch" />
<col id="PositionAtCompany" value="VP Software Development" />
<col id="EntryDate" value="1/1/2011 7:00AM" />
<col id="UpdateDate" value="1/1/2011 7:00AM" />
</row>
<row id="2">
<col id="EmailAddress" value="jpesch@impaccompanies.com" />
<col id="CompanyName" value="Impac" />
<col id="FirstName" value="Joe" />
<col id="LastName" value="Pesch" />
<col id="PositionAtCompany" value="VP Software Development" />
<col id="EntryDate" value="1/1/2011 8:00AM" />
<col id="UpdateDate" value="1/1/2011 8:00AM" />
</row>
</table>
<table id="dbo.ProductsInfo" schema="dbo" name="ProductsInfo">
<row id="1">
<col id="ProductInfoId" value="EEC326F3-C445-4B44-8C42-4E4FF84FDE1E" />
<col id="FieldName" value="TESTING" />
<col id="DisplayText" value="This is a test" />
<col id="Value" value="100" />
</row>
<row id="2">
<col id="ProductInfoId" value="3F5F1FB8-5023-4FCD-ACF4-2B7CD25E6043" />
<col id="FieldName" value="TESTING_ANOTHER" />
<col id="DisplayText" value="This is another test" />
<col id="Value" value="200" />
</row>
</table>
</data>
</xml>
'
-- Temp table for inserts
create table #inserts(id varchar(100), data text)
declare @handle int, @datalen int, @txtptr binary(16)
-- Variables for processing
declare @tbl varchar(150), @col varchar(50), @comma varchar(1), @value varchar(max)
-- Get handle to XML document
exec sp_xml_PrepareDocument@handle output, @xmlData
-- Iterate each table in the XML
declare cur cursor for select id from openxml(@handle, '/xml/schema/table', 1) with (id varchar(50))
open cur
while 'For each table in XML' <> '' begin
fetch next from cur into @tbl
if @@fetch_status <> 0 break
if 'Generate the template record' <> '' begin
set @xpath = '/xml/data/table[@id=''' + @tbl + ''']/row'
insert into #inserts values(@tbl + '.0', 'insert into ' + @tbl + '(')
set @comma = ''
set @xpath = '/xml/schema/table[@id=''' + @tbl + ''']/col'
declare @id varchar(50), @type varchar(50), @length varchar(50), @nullable varchar(50), @quote varchar(50)
declare curX cursor for
select [id], [type], [length], [nullable], [quote]
from openxml(@handle, @xpath, 1)
with ([id] varchar(50), [type] varchar(50), [length] varchar(50), [nullable] varchar(50), [quote] varchar(50))
open curX
while 'Process each column' <> '' begin
fetch next from curX into @id, @type, @length, @nullable, @quote
if @@fetch_status <> 0 break
set @col = @comma + @id
select @datalen = datalength(data) from #inserts where id = @tbl + '.0'
select @txtptr = textptr(data) from #inserts where id = @tbl + '.0'
updatetext #inserts.data @txtptr @datalen 0 @col
set @comma = ', '
end
select @datalen = datalength(data) from #inserts where id = @tbl + '.0'
select @txtptr = textptr(data) from #inserts where id = @tbl + '.0'
updatetext #inserts.data @txtptr @datalen 0 ') values('
close curX
deallocate curX
end
if 'Generate the values using template' <> '' begin
set @comma = ''
set @xpath = '/xml/data/table[@id=''' + @tbl + ''']/row'
declare curX cursor for
select [id] from openxml(@handle, @xpath, 1) with ([id] varchar(50))
open curX
while 'Process each row' <> '' begin
fetch next from curX into @id
if @@fetch_status <> 0 break
insert #inserts select @tbl + '.' + @id, data from #inserts where id = @tbl + '.0'
set @xpath = '/xml/data/table[@id=''' + @tbl + ''']/row[@id=' + @id + ']/col'
declare curY cursor for select [id], [value] from openxml(@handle, @xpath, 1) with ([id] varchar(50), [value] varchar(max))
open curY
set @comma = ''
while 'Process each column' <> '' begin
fetch next from curY into @col, @value
if @@fetch_status <> 0 break
set @xpath = '/xml/schema/table[@id=''' + @tbl + ''']/col[@id=''' + @col + ''']'
select @quote = [quote]
from openxml(@handle, @xpath, 1) with ([quote] varchar(10))
set @value = @comma + @quote + replace(@value, '''', '''''') + @quote
select @datalen = datalength(data) from #inserts where id = @tbl + '.' + @id
select @txtptr = textptr(data) from #inserts where id = @tbl + '.' + @id
updatetext #inserts.data @txtptr @datalen 0 @value
set @comma = ','
end
close curY
deallocate curY
select @datalen = datalength(data) from #inserts where id = @tbl + '.' + @id
select @txtptr = textptr(data) from #inserts where id = @tbl + '.' + @id
updatetext #inserts.data @txtptr @datalen 0 ')'
end
close curX
deallocate curX
delete from #inserts where id = @tbl + '.0'
end
end
close cur
deallocate cur
select * from #inserts
drop table #inserts