XQuery in SQL Server 2005

http://msdn.microsoft.com/en-us/library/ms345122.aspx

 

 

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.

Copy

/* 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