asked
Update using XQuery
I have the below **First Table** Declare @t1 Table(Id int , PaymentXML XML) Insert Into @t1 Select 1, ' MasterCard ************5811 4 2007 ' Union All Select 2 , ' MasterCard ****1234567890 3 2010 ' Union All Select 3 , ' MasterCard ****45678 10 2011 ' Union All Select 4 , ' MasterCard ****1234567890 5 1997 ' Select * From @t1 **Second Table** Declare @t2 Table(Id int) Insert Into @t2 Select 1 Union All Select 2 Select * From @t2 I need to write an update statement such that for every matching row of @t1 and @t2 table, the PaymentXML column nodes will be updated as under > a) will be blank (i.e. ) > > b) will be blank (i.e. ) > > c) will be zero(0) (i.e. 0 ) > > d) will be zero(0) (i.e. 0 ) I have given a very basic shot but need help as I am new to xquery DECLARE @cc_type VARCHAR(10) SELECT @cc_type = '' Update @t1 SET PaymentXML.modify( ' replace value of (/CreditCard/@cc_type.Text())[1] with sql:variable("@cc_type") ') From @t1 a Join @t2 b On
a.Id =
b.Id Thanks in advance
@learner I guess you would have been prompted an error. There are quite a few wrongs in your code 1. The cc_type is an element not an attribute. So to put "@" in front of it is wrong 2. To update an attribute, the correct syntax is `replace value of (/CreditCard/@cc_type)[1]` 3. Since cc_type is an element, the actual update code should have been something like `replace value of (/CreditCard/cc_type[1]/text())[1]` 4. XML is case sensitive, so the XML method text() cannot be called like Text() Hence, I beleive your final update code should be looking something like Update @t1 SET PaymentXML.modify( ' replace value of (/CreditCard/cc_type[1]/text())[1] with sql:variable("@cc_type") ') From @t1 a Join @t2 b On
a.Id =
b.Id If you still have any ambiguity then feel free to ask.
8 People are following this question.
