CODEWITHSUNDEEP
sqlsql-serverxml
JYousef
JYousef

Reputation: 181

T-SQL Delete a node from XML

I need help, deleting the Error node where the TabID ="Claim", I'm, using Sql server 2012

<ClaimErrors ClaimID="73">
  <Error ErrorMessage="offer timeframe" TabID="Offer" FieldName="PostMarkDate" ErrorType="FrontEnd" NoteID="2" />
  <Error ErrorMessage=" code is not valid" TabID="Claim" FieldName="None" NoteID="3811257" ErrorType="BackEnd Error" />
</ClaimErrors>



declare @XML table (data xml)
INsert into @XML select '<ClaimErrors ClaimID="73"><Error ErrorMessage="offer timeframe" TabID="Offer" FieldName="PostMarkDate" ErrorType="FrontEnd" NoteID="2" />
<Error ErrorMessage="code is not valid" TabID="Claim" FieldName="None" NoteID="3811257" ErrorType="BackEnd" /></ClaimErrors>'
update @XML set  data.modify('delete //ClaimErrors/Error[text()][contains(.,"TabID=Claim")]')

I'm using data.modify('delete //ClaimErrors/Error/ but with no hope.

any help will be appreciated.. Thanks

Upvotes: 0

Views: 73

Answers (1)

radar
radar

Reputation: 13425

declare @XML table (data xml)
INsert into @XML select '<ClaimErrors ClaimID="73"><Error ErrorMessage="offer timeframe" TabID="Offer" FieldName="PostMarkDate" ErrorType="FrontEnd" NoteID="2" />
<Error ErrorMessage="code is not valid" TabID="Claim" FieldName="None" NoteID="3811257" ErrorType="BackEnd" /></ClaimErrors>'
declare @var nvarchar(256)= 'Claim'
update @XML set  data.modify('delete (/ClaimErrors/Error[@TabID=sql:variable("@var")])[1]')
select data from @XML

Upvotes: 1

Related Questions