Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server
Hi,
 
I have some problem with stored procedure taking an XML as Input parameter.
 
Xml will be like this
 
<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 ...
 
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 20:53pm
v3
Comments
Rahul Dhoble at 17-Jan-13 23:42pm
   
Where is student id in above XML ?

1 solution

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

Solution 1

Try to convert the XML in to a table first then try to update the base table. Have a look.
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
  Permalink  
v2

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

  Print Answers RSS
0 Afzaal Ahmad Zeeshan 259
1 Maciej Los 240
2 BillWoodruff 240
3 OriginalGriff 220
4 Sergey Alexandrovich Kryukov 195
0 OriginalGriff 6,419
1 Sergey Alexandrovich Kryukov 6,008
2 DamithSL 5,183
3 Manas Bhardwaj 4,673
4 Maciej Los 4,070


Advertise | Privacy | Mobile
Web04 | 2.8.1411019.1 | Last Updated 21 Feb 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