Stored Procedure with XML As Input Parameter

Question

0.00/5 (No votes)

See more:

Hi,

I have some problem with stored procedure taking an XML as Input parameter.

Xml will be like this

HTML

<DocumentElement>
  <AssignReAssignRo>
    <ActivityID>92</ActivityID>
    <UserID>1489420939</UserID>
    <CampusID>129</CampusID>
    <Aging>-370</Aging>
    <StatusID>1</StatusID>
  </AssignReAssignRo>
  <AssignReAssignRo>
    <ActivityID>90</ActivityID>
    <UserID>-99</UserID>
    <CampusID>129</CampusID>
    <Aging>-156</Aging>
    <StatusID>1</StatusID>
  </AssignReAssignRo>
</DocumentElement>

1. I need to update table based on ActivityID in XML and that is happening.

the problem is that ....
student table ------------------>Activity Table
(studentID- primary key) --> (studentID-foreign key (student table) )

Activity Table May contains multiple records with same studentid(Child Records).
I need to update those child records as well based on the Statusid(if statusid=1 then only need to update),
means for each Record in XML I need to loop through the table and do a search for any child record exists with that student id.(if exists then need to be updated as well)

here is my procedure ...

SQL

ALTER PROCEDURE [dbo].[RmsUpdateRoAssignReAssign1]
(
@XMLAssignRo  AS XML
)
AS
BEGIN
	UPDATE RmsActivity 
				SET 
						RmsProcessorID = DocumentElement.AssignReAssignRo.value('(UserID)[1]', 'INT' ),
						LastUpdatedByID= DocumentElement.AssignReAssignRo.value('(UserID)[1]', 'INT' ),
						LastUpdatedDate=GETDATE(),
						ROAssignedDate=GETDATE(),
						RmsStatusID=
						CASE 
						WHEN DocumentElement.AssignReAssignRo.value('(StatusID)[1]', 'INT' )=1
						THEN 2
						ELSE DocumentElement.AssignReAssignRo.value('(StatusID)[1]', 'INT' )
						END
				FROM	
						@XMLAssignRo.nodes('/DocumentElement/AssignReAssignRo') DocumentElement(AssignReAssignRo)
						INNER JOIN RmsUserSkillSet RS
						ON 
							 RS.RmsSkillSetTypeID =  
							 CASE 
								 WHEN DocumentElement.AssignReAssignRo.value('(Aging)[1]', 'INT' )BETWEEN 0 AND 20  THEN 1		
								     ELSE 	
									 CASE	
										 WHEN DocumentElement.AssignReAssignRo.value('(Aging)[1]', 'INT' ) BETWEEN 0 AND 30  THEN 2
										 ELSE
											 CASE
												WHEN DocumentElement.AssignReAssignRo.value('(Aging)[1]', 'INT' ) >=0 THEN 3	
											 END
									 END
														
							  END 
						INNER JOIN tblRoamingUsers RU
						ON RS.RoamingUserID=RU.RoamingUserID
						AND RU.UserID=DocumentElement.AssignReAssignRo.value('(UserID)[1]', 'INT' )
						INNER JOIN tblCampus C
						ON	C.CampusID=DocumentElement.AssignReAssignRo.value('(CampusID)[1]', 'INT' )
						AND RU.SchoolID=C.SchoolID
				WHERE
						RmsActivityID = DocumentElement.AssignReAssignRo.value('(ActivityID)[1]', 'INT' )
END

Please help me.

Thanks...

Posted 6-Dec-12 19:53pm

vinod kumar from Kotma

Updated 6-Dec-12 20:43pm

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)

v3

Add a Solution

Comments

[no name] 13-Dec-12 13:08pm

You want to perform your update statement based on the xml input value as 1, right?

RDBurmon 17-Jan-13 23:42pm

Where is student id in above XML ?

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)

Khorshed Alam, Dhaka · Answer 1 · 2013-02-21T16:45:00

Try to convert the XML in to a table first then try to update the base table. Have a look.

SQL

DECLARE @XmlVal XML=
'<documentelement>
  <assignreassignro>
    <activityid>92</activityid>
    <userid>1489420939</userid>
    <campusid>129</campusid>
    <aging>-370</aging>
    <statusid>1</statusid>
  </assignreassignro>
  <assignreassignro>
    <activityid>90</activityid>
    <userid>-99</userid>
    <campusid>129</campusid>
    <aging>-156</aging>
    <statusid>1</statusid>
  </assignreassignro>
</documentelement>'


UPDATE r
SET r.UserID=x.UserId
--Other update statements here

FROM RmsActivity AS r INNER JOIN (

SELECT Tbl.Col.value('ActivityID[1]', 'INT') AS ActivityID,
       Tbl.Col.value('UserID[1]', 'INT') AS UserID,
       Tbl.Col.value('CampusID[1]', 'INT') AS CampusID,
       Tbl.Col.value('Aging[1]', 'INT') AS Aging,
       Tbl.Col.value('StatusID[1]', 'INT') AS StatusID
FROM   @XmlVal.nodes('DocumentElement/AssignReAssignRo') AS Tbl(Col)) AS x

ON x.ActivityID=r.ActivityID --Change the join as required
WHERE x.StatusID=1