Is there a simple way to create an XML (file, variable, value in column, whatever) with select * from table for xml ... and later automatically insert this data into identical table in different database? Something like insert into table from xml .... All solutions I can find require providing column names explicitly.

What I want in the end is a simple thing - I have Java service connecting to the database. It is able to download new dictionary data and insert (merge) it into specific tables. Currently there are quite a few such tables and we have separate procedures for them, all specifying columns names and so on. This is getting out of hand and I'm looking for some way of simplifying it.

  • Something like this?
    – AMtwo
    Feb 25, 2021 at 11:35
  • I guess so, but mother of God! I really hope that during 11 years that passed since text that you linked was written things have changed, as I'm unable to understand how it works. Thank a lot anyway. Feb 25, 2021 at 11:55
  • Ok, it seems that the best way to do it would be via OPENXML, as described in this link. Feb 25, 2021 at 12:05
  • Could you please provide more context on your current schema and workflow? Do all the Tables have the same columns or different columns? Do all the procedures do the same logic or there's different logic per table? Right now what you described doesn't sound like a problem, or if it is one, it's unclear how XML would help. If anything you may want to use Dynamic SQL instead, or another feature like Triggers but I'm not 100% sure on what your actual problem is.
    – J.D.
    Feb 25, 2021 at 12:10
  • Assume tables T1, T2... Tn on servers S1, S2... Sn. Tn on server Sm is identical to table Tn on other servers. All procedures updating it are nearly identical with ... merge ... when matched then update set <a lot of columns>. Now that you said it's unclear how XML would help I see that main problem is still there - I won't be able to avoid specifying columns names in update or insert sub-clauses in merge. It seems that dynamic SQL is the only solution - or maybe now, maybe I could automatically generate procedure for each table. Thanks for the comment. Feb 25, 2021 at 12:16

1 Answer 1


Here is what I ended with.

Assuming that data is read from the table using the following:

select * from someTable where st_id between 100 and 200
for xml path, root('rows')

I get this data from one server and store it as a tuple (schema, table, keys, xml), where:

  • schema is schema name (e.g. dbo)
  • table is table name (e.g. someTable)
  • keys is comma-separated list of "keys" (columns used for <match> in merge ... on <match>, e.g. st_id in example above, if there are more keys they should be passed,like,this)
  • xml is data as an xml (easy)

This data is then passed to some other server, where there is one function and one procedure defined.

Function tools.GenerateXMLMergerForTable creates body (whole declaration in fact) of procedure used for merging data read from xml into table table in schema schema using parsed string keys as <match>.

Procedure tools.createXMLMergerForTable executes aforementioned function and creates procedure that takes single parameter (of type XML) and merges data read from it into table that it was configured for.

T-SQL follows:

CREATE OR ALTER FUNCTION tools.GenerateXMLMergerForTable 
@tableschema nvarchar(max), @tablename nvarchar(max), @matchcols nvarchar(max), @procname nvarchar(max)
RETURNS nvarchar(max)
    declare @str nvarchar(max) = '
    create or alter procedure tools.sp_AutoMergerForTableXXXPROCEDURENAMEXXX @x xml
        begin try
            DECLARE @handle INT, @PrepareXmlStatus INT  

            EXEC @PrepareXmlStatus= sp_xml_preparedocument @handle OUTPUT, @x

            merge XXXTABLENAMEXXX as tgt
            using (SELECT  * FROM OPENXML(@handle, ''/rows/row'', 2) WITH XXXTABLENAMEXXX)
                as src (XXXFULLCOLUMNLISTXXX)
            on XXXMATCHLISTXXX
            when matched 
                then update set
            when not matched by target
                    insert (XXXFULLCOLUMNLISTXXX)
                    values (XXXFULLCOLUMNLISTXXX);
            EXEC sp_xml_removedocument @handle
        end try
        begin catch
        end catch

    declare @collist nvarchar(max) = '', @setlist nvarchar(max) = '', @matchlist nvarchar(max) = '', @fullcollist nvarchar(max) = ''

    select @collist = @collist + quotename(COLUMN_NAME) + ','
        , @setlist = @setlist + quotename(column_name) + ' = src.' + quotename(column_name) + ', '
        TABLE_SCHEMA = @tableschema and TABLE_NAME = @tablename and COLUMN_NAME not in (select * from string_split(@matchcols, ','))
    order by ordinal_position

    select @fullcollist = @fullcollist + quotename(COLUMN_NAME) + ','
        TABLE_SCHEMA = @tableschema and TABLE_NAME = @tablename
    order by ordinal_position

    select @matchlist = @matchlist + 'tgt.' + QUOTENAME(trim(value)) + '=src.' + QUOTENAME(trim(value)) from string_split(@matchcols, ',')

    declare @result nvarchar(max) = replace(@str, 'XXXCOLUMNLISTXXX', trim(', ' from @collist))
    set @result = REPLACE(@result, 'XXXSETLISTXXX', trim(', ' from @setlist))
    set @result = REPLACE(@result, 'XXXTABLENAMEXXX', @tablename)
    set @result = REPLACE(@result, 'XXXMATCHLISTXXX', REPLACE(trim(@matchlist), ' ', ' and '))
    set @result = REPLACE(@result, 'XXXPROCEDURENAMEXXX', @procname)
    set @result = REPLACE(@result, 'XXXFULLCOLUMNLISTXXX', trim(', ' from @fullcollist))

    RETURN @result


create or alter procedure tools.createXMLMergerForTable @schema nvarchar(max), @table nvarchar(max), @matchcols nvarchar(max), @procsuffix nvarchar(max)
    declare @body nvarchar(max) = tools.GenerateXMLMergerForTable(@schema, @table, @matchcols, @procsuffix)
    exec sp_executesql @body


Java code simply reads tuple defined above, inserts parameters that it knows off, creates random suffix, creates merging procedure, executes it using xml from The Tuple and drops previously created procedure.

It seems to work as advertised, though no guarantees are provided.

NOTE: it is assumed that function, procedure and subsequently created procedures live in schema tools.

NOTE 2: It is possible that you will get information about string_split object not found, if so then just do:

ALTER DATABASE <your database name> 

For reasons see documentation of string_split and how to change compatibility level.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.