SQL XML Update Query help required

• User7766

  SSCommitted

  Points: 1969

  More actions

  August 18, 2011 at 3:42 am

  #255688

  Hi,

  I'm having a table with 10 columns in SQL server, From my frontend .NET application i'm generating an XML string for class object and passing it as 'XML' data type to my stored procedure, I want to update table with XML elements. My XML will be in the below format, can anyone please help me out with update query uisng CTE JOINS?

  -- '<CandidateInterviewList>

  --<Candidateshortlistedoperation>

  --<CandidateInterviewDTO>

  -- <Candidateinterviewhistoryid>a8578d7d-0dda-44b1-8b6d-9dd14120e058</Candidateinterviewhistoryid>

  -- <Candidateinterviewid>27220e28-bf7c-45be-8153-3577c4fb1a0f</Candidateinterviewid>

  -- <Candidateid>bc486cea-e76b-432d-a5bb-296ce1904c4e</Candidateid>

  -- <Jobreqid>831ad760-a3b3-4322-96b3-024c7e36946d</Jobreqid>

  -- <Panelid>49f2b2ea-8a56-43fa-b87e-b931c63fa985</Panelid>

  -- <Interviewstartdate>2011-08-16T00:00:00</Interviewstartdate>

  -- <Interviewenddate>2011-08-16T00:00:00</Interviewenddate>

  -- <Interviewstarttime>10:00 AM</Interviewstarttime>

  -- <Interviewendtime>1:00 PM</Interviewendtime>

  -- <Interviewtypeid>971b3e3b-e20d-42a3-b161-17d2625cc7db</Interviewtypeid>

  -- <Statusid>a080e992-213d-4f5e-9883-f6d9d5dece44</Statusid>

  -- <Comments>Test Comments</Comments>

  -- <Datecreated>2011-08-18T14:22:04</Datecreated>

  -- <Modifiedcontent><P><FONT color=#000000 face="Courier New">Dear Candidate,</FONT</Modifiedcontent>

  -- </CandidateInterviewDTO>

  -- </Candidateshortlistedoperation>

  -- </CandidateInterviewList>'

  Thanks..

• User7766

  SSCommitted

  Points: 1969

  More actions

  August 18, 2011 at 6:55 am

  #1370088

  Hi All,

  I'm able to build the query shown below, But the record in the table is not getting updated 🙁

  DECLARE @passedXML xml

  DECLARE @CandidateInterview TABLE (CandidateInterviewID uniqueidentifier,

  CandidateID uniqueidentifier,

  JobReqID uniqueidentifier,

  PanelID uniqueidentifier,

  InterviewStartDate datetime,

  InterviewEndDate datetime,

  InterviewStartTime varchar(50),

  InterviewEndTime varchar(50),

  InterviewTypeID uniqueidentifier,

  StatusID uniqueidentifier,

  Comments varchar(500),

  ModifiedContent nvarchar(max),

  TemplateID uniqueidentifier,

  CostID uniqueidentifier,

  IsCandidateNotified char(1),

  DateCreated datetime,

  DateModified datetime

  )

  SET @passedXML=

  '<CandidateInterviewList>

  <Candidateshortlistedoperation>

  <CandidateInterviewDTO>

  <Candidateinterviewid>BDA73F7D-C5E0-4572-AA76-20C2EE9F4445</Candidateinterviewid>

  <Statusid>a080e992-213d-4f5e-9883-f6d9d5dece44</Statusid>

  </CandidateInterviewDTO>

  </Candidateshortlistedoperation>

  </CandidateInterviewList>'

  ; WITH CTE as(

  SELECT x.record.query('Candidateinterviewid[1]').value('.','uniqueidentifier') CandidateInterviewID,

  x.record.query('Statusid[1]').value('.','uniqueidentifier') StatusID

  FROM @passedXML.nodes('CandidateInterviewList/Candidateshortlistedoperation/CandidateInterviewDTO') as x(record))

  UPDATE @CandidateInterview

  SET StatusID = CTE.Statusid

  FROM @CandidateInterview T

  JOIN CTE ON CTE.Candidateinterviewid=T.CandidateInterviewID

  Can anyone please correct me where i'm going wrong..

  Thanks

• August 18, 2011 at 7:14 am

  This was removed by the editor as SPAM