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 '12 at 11:07 AM in Default

learner gravatar image

learner
302 16 18 20

(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 '12 at 06:54 AM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

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

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x1816
x136
x128
x11

asked: Jul 25 '12 at 11:07 AM

Seen: 669 times

Last Updated: Jul 26 '12 at 07:14 AM