x

Update using XQuery

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

more ▼

asked Jul 25, 2012 at 11:07 AM in Default

avatar image

learner
302 17 19 22

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

@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.

more ▼

answered Jul 26, 2012 at 06:54 AM

avatar image

Usman Butt
14k 6 13 21

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2076
x186
x162
x15

asked: Jul 25, 2012 at 11:07 AM

Seen: 1021 times

Last Updated: Jul 26, 2012 at 07:14 AM

Copyright 2016 Redgate Software. Privacy Policy