sqlserver - 從使用 select for xml 生成的 xml 插入

有沒有一種簡單的方法來創建一個 XML（文件、變數、列中的值等），select * from table for xml ...然後自動將此數據插入到不同數據庫中的相同表中？類似的東西insert into table from xml ...。我能找到的所有解決方案都需要明確提供列名。

我最終想要的是一件簡單的事情——我有 Java 服務連接到數據庫。它能夠下載新的字典數據並將其插入（合併）到特定的表中。目前有很多這樣的表，我們對它們有單獨的過程，都指定列名等等。這已經失控了，我正在尋找一些簡化它的方法。

這是我結束的。

假設使用以下命令從表中讀取數據：

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

我從一台伺服器獲取這些數據並將其儲存為元組（模式、表、鍵、xml），其中：

• schema是模式名稱（例如dbo）
• table是表名（例如someTable）
• keys是逗號分隔的“鍵”列表（用於<match>in 的列merge ... on <match>，例如st_id在上面的範例中，如果有更多鍵，它們應該是passed,like,this）
• xml是 xml 格式的數據（簡單）

然後將此數據傳遞到其他一些伺服器，其中定義了一個函式和一個過程。

函式tools.GenerateXMLMergerForTable創建過程的主體（實際上是整個聲明），用於使用已解析的字元串as將讀取的數據合併到模式中的xml表中。table``schema``keys``<match>

過程tools.createXMLMergerForTable執行上述功能並創建採用單個參數（XML 類型）的過程，並將從中讀取的數據合併到為其配置的表中。

T-SQL 如下：

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 程式碼簡單地讀取上面定義的元組，插入它知道的參數，創建隨機後綴，創建合併過程，使用xmlfrom The Tuple 執行它並刪除以前創建的過程。

它似乎像宣傳的那樣工作，但不提供任何保證。

注意：假設函式、過程和隨後創建的過程存在於 schema 中tools。

注意 2：您可能會獲得有關string_split未找到對象的資訊，如果是這樣，那麼只需執行以下操作：

ALTER DATABASE <your database name> 
SET COMPATIBILITY_LEVEL = 130;

原因請參見string_split的文件以及如何更改兼容性級別。

引用自：https://dba.stackexchange.com/questions/286017