Passing XML parameter to stored proc and if exists update if not insert

Question

4.00/5 (1 vote)

See more:

SQL-Server-2008

@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

<?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:

SQL

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 22:52pm

Georg Machacek

Updated 28-Mar-13 23:59pm

v4

Add a Solution

Comments

pradiprenushe 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 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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Answer 1 · 2013-03-30T13:38:00

Georg Machacek wrote:
It does not loop thrue the XML.

When you use:

SQL

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:

SQL

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:

SQL

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)

SQL

UPDATE QmFlex_Locations SET	
    LocActiv = 'True',
    LocKrt = @LocKrt
WHERE LocId IN (SELECT CONVERT(INT, ColumnVal) AS LocId FROM @tmp)

To insert new data, use:

SQL

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 ;)