Solved

XML: replace value of - why is not working

Posted on 2011-03-09
4
444 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

635 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