Multiple delete in xml column
- From: jrl124c <jplucoff@xxxxxxxxx>
- Date: Mon, 28 Jan 2008 15:00:10 -0800 (PST)
In the paper, "Introduction to XQuery in SQL Server 2005,
http://msdn2.microsoft.com/en-us/library/ms345122.aspx,
it gives an example below of deleting one element and says it could be
modified to do multiple elements.
What is the best approach to do this when the elements to be deleted
are based on a column in another table?
I'm doing this in a trigger, but code below only evaluates sql:column
for first row in Deleted table.
MSDN says that sql:column is not supported with JOIN, must use APPLY.
But I can't see how to do that either.
UPDATE SecurityAccount
SET SecuredObjectPermissions.modify('
delete (/SecuredObjectPermissions/
SecuredObjectPermission[SecuredObject=sql:column("d.SecuredObjectID")])
')
FROM DELETED d WHERE SecurityAccountID = d.UserAccountID and
IsAccountDeleted = 0
Thanks.
Excerpt from MSDN article:
-------------------------------------------------------------------------------------
Example: Deleting a skill
The following example illustrates the use of the delete keyword to
delete a skill for a specified candidate.
The following stored procedure is written based on the assumption that
the user will pass a string value of one skill as the second argument
to the stored procedure. This stored procedure can be modified to
accept an XML fragment that contains one or more skill elements,
thereby allowing the user to delete multiple skill nodes with a single
invocation of the stored procedure.
/* Stored procedure to delete a specified skill element for a
candidate */
CREATE PROCEDURE [DeleteSkillInfo]
@JobCandidateID [int],
@Skill [varchar](200)
AS
BEGIN
SET NOCOUNT ON;
UPDATE [CandidateInfoXMLDataType]
SET Resume.modify('
delete (/JobCandidate/Skills/Skill[.=sql:variable("@Skill")])
')
WHERE JobCandidateID = @JobCandidateID
END
This stored procedure can easily be modified to accept an XML fragment
which contains one or more skill elements thereby allowing the user to
delete multiple skill nodes with a single invocation of stored
procedure.
.
- Prev by Date: Re: XQuery SQL 2005 Insert block of xml into existing xml type
- Next by Date: Re: XQuery SQL 2005 Insert block of xml into existing xml type
- Previous by thread: XQuery SQL 2005 Insert block of xml into existing xml type
- Next by thread: Re: How do you read an XML schema in SQL 2005
- Index(es):
Relevant Pages
|
Loading
