Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
@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
Posted 28-Mar-13 23:52pm
Edited 29-Mar-13 0:59am
v4
Comments
pradiprenushe at 29-Mar-13 7:38am
   
Where you are looping here? You have not used any logic for loop. It is not good idea to use looping but if you have to then you need to use cursor on your result which you are using in If exists statement (result which contains all id from XML input field).
pradiprenushe at 29-Mar-13 7:39am
   
Refer this link
http://www.dbforums.com/microsoft-sql-server/1682235-how-loop-through-select-statement-using-ms-sql.html.

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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 from ArrayOfInt.
 
To get all int data from ArrayOfInt, 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 call UPDATE and INSERT query without any IF statement Wink | ;)
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 215
1 George Jonsson 175
2 Kornfeld Eliyahu Peter 159
3 PIEBALDconsult 110
4 Richard MacCutchan 85
0 OriginalGriff 6,080
1 DamithSL 4,648
2 Maciej Los 4,087
3 Kornfeld Eliyahu Peter 3,624
4 Sergey Alexandrovich Kryukov 3,294


Advertise | Privacy | Mobile
Web02 | 2.8.141220.1 | Last Updated 30 Mar 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100