/*
	Import from an XML file example

	Created					06/15/15	dcm

	This is an example of how to import data from an XML file into SQL variables.

	I have defined the following XML file with some setup parameters for a project.

		<setupfile>
		  <row>
			<earlydatedays>183</earlydatedays>
			<defaultfactor>0</defaultfactor>
			<showdefaults>N</showdefaults>
			<maxfactor>10</maxfactor>
			<includedefaultvendors>N</includedefaultvendors>
			<includeduplicates>N</includeduplicates>
		  </row>
		</setupfile>

	Refer to the comments below for details on the loading of the variables.


*/
declare @filename varchar(200), @dir varchar(200),  @task varchar(8000), @error varchar(1), @xmldoc xml,  @validxml int, @idoc int
	, @earlydatedays int, @defaultfactor numeric(19,6), @showdefaults varchar(1), @maxfactor numeric(19,6), @includedefaultvendors varchar(1), @includeduplicates varchar(1)

select @error = 'N'
create table #variables (xml xml)
create table #setups (earlydatedays int, defaultfactor numeric(19,6), showdefaults varchar(1), maxfactor numeric(19,6), includedefaultvendors varchar(1), includeduplicates varchar(1))

select @filename = 'C:\temp\Replacementvariables.xml'		-- we need to ask the user where the XML file is located and it's name - here I have hard coded it.
															-- you could use the '[%0]' query input technique to get the file directory and name.

-- now we define the select command required to import data
select @task = ' Select * from OPENROWSET (BULK '''   + @filename + ''' , SINGLE_BLOB) as TEMP'

begin try
	-- the table #variables was defined above as having one variable = 'XML' and its datatype is XML.
	-- executing this select statement will insert the XML information into the #variables temporary table
	insert into #variables with (rowlock) (xml)
	EXECUTE (@task)
end try
begin catch
	-- the begin catch function is only executed if the begin try loop fails for any reason.
	select @error = 'Y'
	select 'File not found:  ' + @filename as 'Message'
end catch
if @error <> 'Y'	-- we only set @error to 'Y' if the begin catch function trapped an error.  If not, we continue.
	begin
		-- we then prepare the code to assign the @idoc variable a value using the sp_xml_preparedocument function
		select @xmldoc = [xml] from #variables 
		set @validxml = 2
		exec sp_xml_preparedocument @idoc OUTPUT, @xmldoc

		-- then we use the OPENXML command to place the values found in the XML file onto the table #setups.  
		-- Note: there could be multiple 'row' definitions for the setup records.  If so, then these would show up as separate records in the #setups temporary table
		insert into #setups
		select *
		from OPENXML (@idoc, '/setupfile/row',2)
		WITH (earlydatedays int, defaultfactor numeric(19,6), showdefaults varchar(1), maxfactor numeric(19,6), includedefaultvendors varchar(1), includeduplicates varchar(1))
		-- this next statement simply removes  the @idoc variable - its use is complete
		EXEC sp_xml_removedocument  @idoc
	end
-- Now we can use normal SQL to read the data from the #setups table and assign values
select  @earlydatedays = earlydatedays 
	, @defaultfactor =  defaultfactor
	, @showdefaults = showdefaults
	, @maxfactor = maxfactor
	, @includedefaultvendors = includedefaultvendors
	, @includeduplicates = includeduplicates
from #setups
select @earlydatedays, @defaultfactor, @showdefaults, @maxfactor, @includedefaultvendors, @includeduplicates

drop table #variables
drop table #setups