使用XML在DymanicTable Name中插入数据 [英] Insert data in DymanicTable Name using XML
本文介绍了使用XML在DymanicTable Name中插入数据的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
这里我有带DynamicName的DataBase表
我希望使用XML在My Dynamic Named Table中插入数据..
这里我试过这个代码,但它不能用,
有人能建议我正确的方式吗?
这里我的实际表名是DynamicName_BasicInfo....
DECLARE @ Insert_Query VARCHAR ( 5000 )= NULL
DECLARE @ TableName VARCHAR ( 100 )= ' DynamicName'
DECLARE @ XML_Details XML =
' < DocumentElement>
< DetailTable>
< UserCode> AAAA< / UserCode>
< UserName> AAAA< / UserName>
< / DetailTable>
< DetailTable>
< UserCode> CCCC< / UserCode>
< UserName> AAAA< / UserName>
< / DetailTable>
< / DocumentElement>'
; TempTable as (
SELECT
x.Details.query(' UserCode')。value(' 。' ,' VARCHAR(MAX)')UserCode,
x.Details。 query(' UserName')。value(' 。',' VARCHAR(MAX)')UserName
FROM @ XML_Details .nodes(' DocumentElement / DetailTable') as x(详情) )
- ---------------------- --------------------------------------------------
- ---------------- -------------------------------------------------- ------
INSERT INTO @ TableName + ' _ BasicInfo'(
UserCode,
UserName)
SELECT * FROM TempTable
- --------------------- -------------------------------------------------- -
- --------------- -------------------------------------------------- -------
解决方案
DECLARE @ Insert_Query NVARCHAR (MAX)= NULL
DECLARE @ TableName VARCHAR (< span class =code-digit> 100 )= ' DynamicName'
DECLARE @ XML_Details XML =
' < documentelement>
< detailtable>
< usercode> AAAA< / usercode>
< username> AAAA< / username>
< / detailtable>
< detailtable>
< usercode> CCCC< / usercode>
< username> AAAA< / username>
< / detailtable>
< / documentelement>'
SET @ Insert_Query = N ' ,TempTable为(
SELECT
x.Details.query(' 'UserCode'')。value(''''',''VARCHAR(MAX)'')UserCode,
x.Details.query(''UserName'')。value(''。'', ''VARCHAR(MAX)'')UserName
FROM @ XML_Details.nodes(''DocumentElement / DetailTable'')as x(Details))
INSERT INTO' + @ TableName + ' _ BasicInfo(UserCode,UserName)
SELECT * FROM TempTable'
EXECUTE sp_executesql @ Insert_Query ,N < span class =code-string>' @ XML_Details XML',@ XML_Details = @ XML_Details
Here I have DataBase Table with DynamicName
ANd I want to Insert data in My Dynamic Named Table using XML..
Here i tried this Code but it cannot works,
Can anyone Suggest me the Right Way?
Here my actual Table name is "DynamicName_BasicInfo"....
DECLARE @Insert_Query VARCHAR(5000) = NULL
DECLARE @TableName VARCHAR(100) = 'DynamicName'
DECLARE @XML_Details XML =
'<DocumentElement>
<DetailTable>
<UserCode>AAAA</UserCode>
<UserName>AAAA</UserName>
</DetailTable>
<DetailTable>
<UserCode>CCCC</UserCode>
<UserName>AAAA</UserName>
</DetailTable>
</DocumentElement>'
;with TempTable as(
SELECT
x.Details.query('UserCode').value('.','VARCHAR(MAX)') UserCode,
x.Details.query('UserName').value('.','VARCHAR(MAX)') UserName
FROM @XML_Details.nodes('DocumentElement/DetailTable') as x(Details))
--------------------------------------------------------------------------
--------------------------------------------------------------------------
INSERT INTO @TableName + '_BasicInfo'(
UserCode,
UserName)
SELECT * FROM TempTable
--------------------------------------------------------------------------
--------------------------------------------------------------------------
解决方案
You need to use dynamic sql:
DECLARE @Insert_Query NVARCHAR(MAX) = NULL DECLARE @TableName VARCHAR(100) = 'DynamicName' DECLARE @XML_Details XML = '<documentelement> <detailtable> <usercode>AAAA</usercode> <username>AAAA</username> </detailtable> <detailtable> <usercode>CCCC</usercode> <username>AAAA</username> </detailtable> </documentelement>' SET @Insert_Query =N'with TempTable as( SELECT x.Details.query(''UserCode'').value(''.'',''VARCHAR(MAX)'') UserCode, x.Details.query(''UserName'').value(''.'',''VARCHAR(MAX)'') UserName FROM @XML_Details.nodes(''DocumentElement/DetailTable'') as x(Details)) INSERT INTO ' + @TableName + '_BasicInfo(UserCode,UserName) SELECT * FROM TempTable' EXECUTE sp_executesql @Insert_Query, N'@XML_Details XML', @XML_Details=@XML_Details
这篇关于使用XML在DymanicTable Name中插入数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文