如何将XML变量合并到存储过程中的SQL表 [英] How to Merge XML variable to a SQL Table in a stored procedure
问题描述
我有一个XML变量作为参数传递给SP。
我想将XML与SQL表合并。
我只是想从XML变量创建动态视图,从该视图我可以合并到原始SQL表。
这可能吗?
否则有一种方法可以将XML变量直接合并到SQL Table。
例如:
我有一个XML变量,其值如下:
DECLARE @XMLDoc xml ='
< NewDataSet>
< Test_Reports>
< ByteArray> 1< / ByteArray>
< ColumnTest> A< / ColumnTest>
< ColumnTest2> B< / ColumnTest2>
< T> TestValue< / T>
< / Test_Reports>
< / NewDataSet>
',
我想与具有相同列结构的表Test_Reports合并。 />
如果此数据不存在,则插入表格。
CREATE 表 [dbo]。[Test_Reports](
[ByteArray] [ int ] NULL ,
[ColumnTest] [ varchar ]( 50 ) NULL ,
[ColumnTest2] [ varchar ]( 50 ) NULL
) ON [ PRIMARY ]
请帮帮我。
谢谢&此致,
Mathi。
你想要吗?做这样的事情?
查询:
INSERT INTO [dbo]。[Test_Reports]
([ByteArray],
[ColumnTest] ,
[ColumnTest2])
SELECT x.Test_Reports.value(' < span class =code-string>(ByteArray)[1]',' int'),
x.Test_Reports.value(' (ColumnTest)[1]' ,' varchar(50)'),
x.Test_Reports.value(' (ColumnTest2)[1]',' < span class =code -string> varchar(50)')
FROM @ XMLDoc 。节点(' / NewDataSet / Test_Reports') AS x(Test_Reports)
WHERE NOT EXISTS ( SELECT 1
FROM [dbo]。[Test_Reports]
WHERE ([ByteArray] = x.Test_Reports.value(' (ByteArray)[1]',' int'))
AND ([ColumnTest] = x.Test_Reports.value(' (ColumnTest )[1]',' varchar(50)'))
AND ([ColumnTest2] = x.Test_Reports.value(' (ColumnTest2)[1]',' varchar(50)'))) ;
Hi,
I have an XML Variable passed as parameter to SP.
I wanted to merge that XML with the SQL Table.
I just had a thought to create a dynamic view from the XML variable and from that view I can merge to the original SQL Table.
Is this possible ?
Else there is a way to directly merge the XML variable to SQL Table.
For Example:
I have a XML variable whose value is as below:
DECLARE @XMLDoc xml = '
<NewDataSet>
<Test_Reports>
<ByteArray>1</ByteArray>
<ColumnTest>A</ColumnTest>
<ColumnTest2>B</ColumnTest2>
<T>TestValue</T>
</Test_Reports>
</NewDataSet>
',
I wanted to merge with the table Test_Reports with the same Column structure.
If this data doen't exist then insert into the table.
CREATE TABLE [dbo].[Test_Reports](
[ByteArray] [int] NULL,
[ColumnTest] [varchar](50) NULL,
[ColumnTest2] [varchar](50) NULL
) ON [PRIMARY]
Please help me in this.
Thanks & Regards,
Mathi.
Hi,
Do you want to do something like this?
Query:
INSERT INTO [dbo].[Test_Reports] ([ByteArray], [ColumnTest], [ColumnTest2]) SELECT x.Test_Reports.value('(ByteArray)[1]', 'int'), x.Test_Reports.value('(ColumnTest)[1]', 'varchar(50)'), x.Test_Reports.value('(ColumnTest2)[1]', 'varchar(50)') FROM @XMLDoc.nodes('/NewDataSet/Test_Reports') AS x(Test_Reports) WHERE NOT EXISTS (SELECT 1 FROM [dbo].[Test_Reports] WHERE ( [ByteArray] = x.Test_Reports.value('(ByteArray)[1]', 'int') ) AND ( [ColumnTest] = x.Test_Reports.value('(ColumnTest)[1]', 'varchar(50)') ) AND ( [ColumnTest2] = x.Test_Reports.value('(ColumnTest2)[1]', 'varchar(50)') ));
这篇关于如何将XML变量合并到存储过程中的SQL表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!