/* 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. 183 0 N 10 N N 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