将XML参数传递给存储过程如果存在则更新(如果不存在则更新) [英] Passing XML parameter to stored proc and if exists update if not insert
问题描述
@LocXml
正在传递一个整数列表。我想查看它是否存在以及是否更新如果不存在插入。我发布的存储过程只是更新或插入XML中的第一个参数。
它不会循环使用XML。
<? xml version = 1.0 ? >
< ArrayOfInt xmlns:xsi = http://www.w3.org/2001/XMLSchema-instance xmlns:xsd = http://www.w3.org / 2001 / XMLSchema >
< int < span class =code-keyword>> 1013 < / int >
< int < span class =code-keyword>> 1015 < / int >
< int < span class =code-keyword>> 1016 < / int >
< int < span class =code-keywor d>> 1017 < / int >
< / ArrayOfInt >
存储过程:
USE [SVR_CheckIt_Test]
GO
/ * *****对象:StoredProcedure [dbo]。[QmFlex_LocKrt_Save]脚本日期:03/29/2013 09:08:28 ****** /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo]。[QmFlex_LocKrt_Save]
(
@LocCus nVarChar ( 60 ),
@ LocObj int ,
@ LocKrt int ,
@ LocXml XML
)
AS
IF EXISTS ( SELECT QMFlex_Locations.LocId FROM QmFlex_Locations WHERE QmFlex_Locations.LocId =( SELECT xmlTable.ID.value(' int [1] ',' int')< span class =code-keyword> from @ LocXml .nodes(' / ArrayOfInt')xmlTable(ID)))
BEGIN
- 更新此处
更新 QmFlex_Locations
SET
LocActiv = ' True',
LocKrt = @ LocKrt
WHERE LocId =( SELECT xmlTable.ID.value(' int [1]',' int')来自 @ LocXml .nodes(' / ArrayOfInt)xmlTable(ID))
END
ELSE
BEGIN
INSERT INTO QmFlex_Locations(QMFlex_Locations.LocId,QMFlex_Locations.LocActiv,QMFlex_Locations.LocKrt,QMFlex_Locations.LocWpr,QMFlex_Locations.LocMld)
VALUES
(
( SELECT xmlTable.ID.value(' int [1]',' int')来自 @ LocXml .nodes(' / ArrayOfInt')xmlTable(ID)),
' True' ,
@ LocKrt ,
0 ,
0
)
< span class =code-keyword> END
END
感谢您的光临你的帮助
Georg
Georg Machacek写道:它不会循环使用XML。
使用时:
SELECT xmlTable.ID.value(' int [1] ',' int')来自 @ LocXml .nodes(' / ArrayOfInt' )xmlTable(ID)
它不能通过xml节点循环,因为:''int [1] ''
,表示从<获取第一条记录code> ArrayOfInt 。
从<获取所有int
数据code> ArrayOfInt ,使用代码:
DECLARE @ xmlDoc AS XML
SET < span class =code-sdkkeyword> @ xmlDoc = ' N< arrayofint xmlns:xsi =http ://www.w3.org/2001/XMLSchema-instancexmlns:xsd =http://www.w3.org/2001/XMLSchema>
< int> 1013< / int>
< int> 1015< / int>
< int> 1016< / int>
< int> 1017< / int>
< / arrayofint>'
DECLARE @ tmp 表(ColumnHeader NVARCHAR ( 50 ),ColumnVal( 30 ))
INSERT INTO @ tmp (ColumnHeader,ColumnVal)
SELECT bar.value(' local-name(。)',' nvarchar(50)') as ColumnHeader,
bar.value (' (./。)',' varchar(30)') as ColumnVal
FROM @ xmlDoc .nodes(' / * / *') as xml(bar)
SELECT ColumnHeader AS H,ColumnVal AS V
FROM @ tmp
返回:
HV
int 1013
int 1015
int 1016
int 1017
上面的代码将数据输入@tmp
表。现在,您可以插入新的或更新现有数据。
要更新,请使用:
更新 SET
QL.LocActiv = ' True',
QL.LocKrt = @ LocKrt
FROM QmFlex_Locations AS QL RIGHT JOIN ( SELECT CONVERT ( INT ,ColumnVal) AS LocId FROM @ tbl ) AS T
ON QL.LocId = T.LocId
或(与您的查询类似)
U PDATE QmFlex_Locations SET
LocActiv = ' True',
LocKrt = @ LocKrt
WHERE LocId IN ( SELECT CONVERT ( INT ,ColumnVal) AS LocId FROM @ tmp )
要插入新数据,使用:
INSERT INTO QmFlex_Locations( LocId,LocActiv,LocKrt,LocWpr,LocMld)
SELECT CONVERT ( INT ,ColumnVal) AS LocId, True' AS LocActiv, @ LocKrt AS LocKrt, 0 AS LocWpr, 0 AS LocMld
FROM @ tmp
注意:您可以在没有任何IF
语句的情况下调用UPDATE
和INSERT
查询;)
@LocXml
is passing a list of integer. I want to look whether it exists and if than update if not exists insert. The stored procedure I posted just update or insert the first parameter in the XML.
It does not loop thrue the XML.
<?xml version="1.0"?>
<ArrayOfInt xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<int>1013</int>
<int>1015</int>
<int>1016</int>
<int>1017</int>
</ArrayOfInt>
Stored Procedure:
USE [SVR_CheckIt_Test]
GO
/****** Object: StoredProcedure [dbo].[QmFlex_LocKrt_Save] Script Date: 03/29/2013 09:08:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[QmFlex_LocKrt_Save]
(
@LocCus nVarChar(60),
@LocObj int,
@LocKrt int,
@LocXml XML
)
AS
IF EXISTS (SELECT QMFlex_Locations.LocId FROM QmFlex_Locations WHERE QmFlex_Locations.LocId = (SELECT xmlTable.ID.value('int[1]','int') from @LocXml.nodes('/ArrayOfInt') xmlTable(ID)))
BEGIN
--UPDATE HERE
UPDATE QmFlex_Locations
SET
LocActiv = 'True',
LocKrt = @LocKrt
WHERE LocId = (SELECT xmlTable.ID.value('int[1]','int') from @LocXml.nodes('/ArrayOfInt') xmlTable(ID))
END
ELSE
BEGIN
INSERT INTO QmFlex_Locations (QMFlex_Locations.LocId,QMFlex_Locations.LocActiv,QMFlex_Locations.LocKrt,QMFlex_Locations.LocWpr,QMFlex_Locations.LocMld)
VALUES
(
(SELECT xmlTable.ID.value('int[1]','int') from @LocXml.nodes('/ArrayOfInt') xmlTable(ID)),
'True',
@LocKrt,
0,
0
)
END
END
Thanks for your help
Georg
Georg Machacek wrote:It does not loop thrue the XML.
When you use:
SELECT xmlTable.ID.value('int[1]','int') from @LocXml.nodes('/ArrayOfInt') xmlTable(ID)
it can''t loop thru the xml nodes, because of:''int[1]''
, which means get the first record fromArrayOfInt
.
To get allint
data fromArrayOfInt
, use code:
DECLARE @xmlDoc AS XML SET @xmlDoc ='N<arrayofint xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <int>1013</int> <int>1015</int> <int>1016</int> <int>1017</int> </arrayofint>' DECLARE @tmp TABLE (ColumnHeader NVARCHAR(50), ColumnVal(30)) INSERT INTO @tmp (ColumnHeader, ColumnVal) SELECT bar.value('local-name(.)','nvarchar(50)') as ColumnHeader, bar.value('(./.)','varchar(30)') as ColumnVal FROM @xmlDoc.nodes('/*/*') as xml(bar) SELECT ColumnHeader AS H, ColumnVal AS V FROM @tmp
which returns:
H V int 1013 int 1015 int 1016 int 1017
Above code gets data into@tmp
table. Now, you can insert new or update existing data.
To update, use:
UPDATE SET QL.LocActiv = 'True', QL.LocKrt = @LocKrt FROM QmFlex_Locations AS QL RIGHT JOIN (SELECT CONVERT(INT, ColumnVal) AS LocId FROM @tbl) AS T ON QL.LocId = T.LocId
or (similar to your query)
UPDATE QmFlex_Locations SET LocActiv = 'True', LocKrt = @LocKrt WHERE LocId IN (SELECT CONVERT(INT, ColumnVal) AS LocId FROM @tmp)
To insert new data, use:
INSERT INTO QmFlex_Locations (LocId, LocActiv, LocKrt, LocWpr, LocMld) SELECT CONVERT(INT, ColumnVal) AS LocId, 'True' AS LocActiv, @LocKrt AS LocKrt, 0 AS LocWpr, 0 AS LocMld FROM @tmp
Note: You can callUPDATE
andINSERT
query without anyIF
statement ;)
这篇关于将XML参数传递给存储过程如果存在则更新(如果不存在则更新)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!