XML作为输入参数的存储过程 [英] Stored Procedure with XML As Input Parameter
问题描述
我的存储过程存在一些问题,将 XML
作为输入参数。
Xml
将是这样的
< DocumentElement >
< AssignReAssignRo >
< ActivityID > ; 92 < / ActivityID >
< UserID > ; 1489420939 < / UserID >
< CampusID > 129 < / CampusID >
< 陈旧 > -370 < / Aging >
< StatusID > 1 < / StatusID >
< / AssignReAssignRo >
< AssignReAssignRo >
< ActivityID > 90 < / ActivityID >
< UserID > -99 < / UserID >
< CampusID > 129 < / CampusID >
< 老化 > -156 < / Aging >
< StatusID > 1 < span class =code-keyword>< / StatusID >
< / AssignReAssignRo >
< / DocumentElement >
1.我需要在<$ c中更新基于 ActivityID
的表格c $ c> XML 即将发生。
问题在于......
学生表------------------> 活动表
( studentID
- 主键) - > ( studentID
-foreign key( student table
))
活动表
可能包含多个具有相同 studentid
(子记录)的记录。
我需要根据 Statusid
更新这些子记录(如果statusid = 1
那么只需要更新) ,
表示 XML
中的每个记录我需要遍历表并搜索存在该学生ID的任何子记录。(如果存在则需要更新)
这是我的程序...
< pre lang =SQL> ALTER PROCEDURE [dbo]。[RmsUpdateRoAssignReAssign1]
(
@ XMLAssignRo AS XML
)
AS
BEGIN
更新 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
那么 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(' (老化)[1]',' INT') BETWEEN 0 AND 20 那么 1
ELSE
CASE
WHEN DocumentElement.AssignReAssignRo.value(' (Aging)[1]', ' INT') BETWEEN 0 AND 30 那么 2
ELSE
CASE
WHEN Docu mentElement.AssignReAssignRo.value(' (Aging)[1]',' INT')> = 0 那么 3
END
END
END
INNER 加入 tblRoamingUsers RU
ON RS.RoamingUserID = RU.RoamingUserID
AND RU.UserID = DocumentElement.AssignReAssignRo.value(' (UserID)[1]',' INT')
INNER < span class =code-keyword> JOIN tblCampus C
ON C.CampusID = DocumentElement.AssignReAssignRo.value(' (CampusID)[1]',' INT')
< span class =code-keyword> AND RU.SchoolID = C.SchoolID
WHERE
RmsActivityID = DocumentElement.AssignReAssignRo。 value(' (ActivityID)[1]',' INT')
END
请帮帮我。
谢谢...
尝试转换首先将XML放入表中,然后尝试更新基表。看看。
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>'
更新 r
SET r.UserID = x.UserId
- 其他更新这里的陈述
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('跨度> < span class =code-string> CampusID [1]',' INT' ) AS CampusID,
Tbl.Col.value(' Aging [1]',' INT') AS 老化,
Tbl.Col.value(' StatusID [1 ]',' INT') AS StatusID
FROM @ XmlVal .nodes(' DocumentElement / AssignReAssignRo') AS Tbl(Col)) AS x
ON x.ActivityID = r.ActivityID - - 根据需要更改联接
WHERE x.StatusID = 1
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...
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
这篇关于XML作为输入参数的存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!