Solved

XML: replace value of - why is not working

Posted on 2011-03-09
4
431 Views
Last Modified: 2012-05-11
i have this xml
<InvoiceList xmlns="http://schemas.adventure-works.com/Invoices">
  <Invoice InvoiceNo="1000">
    <Customer>Kim Abercrombie</Customer>
    <Items>
      <Item Product="1" Price="1.99" Quantity="2" />
      <Item Product="3" Price="2.49" Quantity="1" />
    </Items>
  </Invoice>
  <Invoice InvoiceNo="1001">
    <Customer>Sean Chai</Customer>
    <Items>
      <Item Product="1" Price="2.45" Quantity="2" />
    </Items>
  </Invoice>
</InvoiceList>

why is not working
UPDATE #Stores
SET Invoices.modify('declare default element namespace "http://schemas.adventure-works.com/Invoices";
  replace value of (/InvoiceList/Invoice/Items[@Price="1.99"]/@Price)[1]
  with "1.69"')
WHERE StoreID = 1
0
Comment
Question by:enrique_aeo
4 Comments
 
LVL 3

Expert Comment

by:CarlsbergFTW
ID: 35080794
There isn't any 'StoreID' value in the xml you posted tho your query is using it to define specific data.
0
 

Author Comment

by:enrique_aeo
ID: 35080868
it is exits
XML-storeID.jpg
0
 
LVL 6

Accepted Solution

by:
jonaska earned 400 total points
ID: 35082987
Your xpath is incomplete as I see it.
Should be:
UPDATE #Stores
SET Invoices.modify('declare default element namespace "http://schemas.adventure-works.com/Invoices";
  replace value of (/InvoiceList/Invoice/Items/Item[@Price="1.99"]/@Price)[1]
  with "1.69"')
WHERE StoreID = 1
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 100 total points
ID: 35083214
The solution offered is absolutely correct, however just a word of caution, doing this type of replacement is risky at best.  If there are two values with price 1.99 in the same node (see below), then only the first will be replaced.
<InvoiceList xmlns="http://schemas.adventure-works.com/Invoices">
  <Invoice InvoiceNo="1000">
    <Customer>Kim Abercrombie</Customer>
    <Items>
      <Item Product="1" Price="1.99" Quantity="2" />
      <Item Product="3" Price="1.99" Quantity="1" />
    </Items>
  </Invoice>
  <Invoice InvoiceNo="1001">
    <Customer>Sean Chai</Customer>
    <Items>
      <Item Product="1" Price="2.45" Quantity="2" />
    </Items>
  </Invoice>
</InvoiceList>

Open in new window

0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now