I have the below
First Table
Declare @t1 Table(Id int , PaymentXML XML)
Insert Into @t1
Select 1, '<CreditCard>
<cc_display_name />
<cc_type>MasterCard</cc_type>
<cc_no>************5811</cc_no>
<cc_expire_month>4</cc_expire_month>
<cc_expire_year>2007</cc_expire_year>
</CreditCard>' Union All
Select 2 , '<CreditCard>
<cc_display_name />
<cc_type>MasterCard</cc_type>
<cc_no>****1234567890</cc_no>
<cc_expire_month>3</cc_expire_month>
<cc_expire_year>2010</cc_expire_year>
</CreditCard>' Union All
Select 3 , '<CreditCard>
<cc_display_name />
<cc_type>MasterCard</cc_type>
<cc_no>****45678</cc_no>
<cc_expire_month>10</cc_expire_month>
<cc_expire_year>2011</cc_expire_year>
</CreditCard>' Union All
Select 4 , '<CreditCard>
<cc_display_name />
<cc_type>MasterCard</cc_type>
<cc_no>****1234567890</cc_no>
<cc_expire_month>5</cc_expire_month>
<cc_expire_year>1997</cc_expire_year>
</CreditCard>'
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
asked
Jul 25 '12 at 11:07 AM
in Default
learner
302
●
12
●
17
●
19