Sql-server – sqlserver – Insert from xml generated with `select for xml`

mergeselectsql serverxml

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.

Best Answer

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:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER FUNCTION tools.GenerateXMLMergerForTable 
(
@tableschema nvarchar(max), @tablename nvarchar(max), @matchcols nvarchar(max), @procname nvarchar(max)
)
RETURNS nvarchar(max)
AS
BEGIN
    declare @str nvarchar(max) = '
    create or alter procedure tools.sp_AutoMergerForTableXXXPROCEDURENAMEXXX @x xml
    as
    begin
        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
                    XXXSETLISTXXX
            when not matched by target
                then
                    insert (XXXFULLCOLUMNLISTXXX)
                    values (XXXFULLCOLUMNLISTXXX);
            EXEC sp_xml_removedocument @handle
        end try
        begin catch
        end catch
    end
    '

    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) + ', '
    from INFORMATION_SCHEMA.COLUMNS where 
        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) + ','
    from INFORMATION_SCHEMA.COLUMNS where 
        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

END
GO

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

go

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> 
SET COMPATIBILITY_LEVEL = 130;

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

Related Question