Sql SERVER从FORM XML插入动态全局命名表 [英] sql server insert into dynamic global named table from from xml

问题描述

我要将数据插入到动态名为：

的临时全局表中

SET @SQL = '
CREATE TABLE '+Cast(@TableName as VARCHAR(60))+'
(
    Adr VARCHAR(1000)
)';
execute (@SQL);

我能够检索我需要的值，如下所示：

SELECT Recipient.query('.').value('.','varchar(15)')
FROM   @not.nodes('Data/MgRec' ) xmlData(ref) CROSS APPLY
    ref.nodes('Recipient') AS Recipients(Recipient) ;

这给了我一些值的列表.. 现在，当我想插入这些值时：

SET @SQL = 'INSERT INTO '+ Cast(@TableName as VARCHAR(60))+' (Adr) 
 Select Recipient.query(''.'').value(''.'',''varchar(15)'')
FROM  '+ Cast(@not as VARCHAR(60))  +'.nodes(''Data/MgRec'' ) xmlData(ref) CROSS APPLY
    ref.nodes(''Recipient'') AS Recipients(Recipient)' ;
    execute (@SQL);
SET @SQL = 'select * from ' +Cast(@TableName as VARCHAR(60))
execute (@SQL);

这里我被卡住了，错误：

目标字符串大小太小，无法表示XML实例

有什么建议吗？

Bellow@Not XML：

<Data>
  <MgRec>
    <Recipient>10800234</Recipient>
    <Recipient>24900005</Recipient>
    <Recipient>24900004</Recipient>
    <Recipient>10201026</Recipient>
    <Recipient>66600019</Recipient>
    <Recipient>14042243</Recipient>
  </MgRec>
</Data>

推荐答案

为什么不正确设置查询参数？

DECLARE @SchemaName sysname = N'dbo',
        @TableName sysname = N'YourTable';

DECLARE @SQL nvarchar(MAX);
SET @SQL = '
CREATE TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName)+ N'
(
    Adr VARCHAR(1000)
)
INSERT INTO ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName)+ N' (Adr)
SELECT Recipient.value(''(./text())[1]'',''varchar(15)'')
FROM @not.nodes(''Data/MgRec'' ) xmlData(ref)
     CROSS APPLY ref.nodes(''Recipient'') AS Recipients(Recipient) ;';
EXEC sys.sp_executesql @SQL, N'@not xml', @not;

db<>fiddle

这篇关于Sql SERVER从FORM XML插入动态全局命名表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！