CODEWITHSUNDEEP
sqlmysqlxml
SketchBookGames
SketchBookGames

Reputation: 514

sql to set an xml value

I'm a novice in mySql. I'm trying to replace a value in the xml column of my table.

my select method works.

SELECT * FROM `comics` WHERE ExtractValue(xml,'comic/pageNumber') = 6

my replace method doesn't. I've been searching for the correct syntax for a bit now... 

SET xml.modify(
replace value of ('comic/pageNumber') with 5
)

some background:

this situation comes up when i delete a comic page.

it leaves a gap in the page numbers, after which i would either:

iterate through all the comics and remove any gaps in the page numbers.

or

iterate through all comics with pageNumber larger than the deleted page, and reduce their pageNumber by 1.

Upvotes: 2

Views: 1815

Answers (3)

RichardTheKiwi
RichardTheKiwi

Reputation: 107726

Tested on MySQL version 5.1

UPDATE `comics`
SET xml = UpdateXML(xml, 
                'comic/pageNumber', 
                concat('<pageNumber>',(ExtractValue(xml,'comic/pageNumber')+1),'</pageNumber>'))
WHERE ExtractValue(xml,'comic/pageNumber') >= 1

Upvotes: 2

dfb
dfb

Reputation: 13289

How about 

UPDATE comics 
SET xml = UpdateXML(xml,'comic/pageNumber', '<pageNumber>5</pageNumber>')
WHERE ExtractValue(xml,'comic/pageNumber') = 6

Upvotes: 4

OrangeDog
OrangeDog

Reputation: 38777

You'd be better off actually storing the fields in the table, rather than a single field with xml in it. Then the following would work. Otherwise there's not much point using a relational database at all.

BEGIN;
DELETE FROM `comics` 
    WHERE `comicID` = :id AND `pageNumber` = :page;
UPDATE `comics` SET `pageNumber` = `pageNumber` - 1 
    WHERE `comicID` = :id AND `pageNumber` > :page;
COMMIT;

Upvotes: -1

Related Questions