XQuery Problem

Question

XQuery Problem

Mahesh Bote

SSCrazy Eights

Points: 9107
More actions
January 16, 2009 at 7:08 am

#198851

[font="Verdana"]Hi all,
I have one table with XML column in it. It has data like(with 2 authors for each book):
<store>
<book category="WEB">
<title lang="en">XQuery Kick Start</title>
<author>James McGovern</author>
<author>Per Bothner</author>
<year>2003</year>
<price>49.99</price>
</book>
</store>

Now I want to keep records with Author = Per Bothner. I tried the below query, but it is removing all the records. Can anybody tell me how to keep one element's value while removing other's?
Create Table Store
(IdInt,
Metadata XML)
Go
Insert Into Store Values (1, '<store>
<book category="WEB">
<title lang="en">XQuery Kick Start</title>
<author>James McGovern</author>
<author>Per Bothner</author>
<year>2003</year>
<price>49.99</price>
</book>
</store>')
Insert Into Store Values (2, '<store>
<book category="WEB">
<title lang="en">SSIS Quick Start</title>
<author>John McDonald</author>
<author>Per Bothner</author>
<year>2004</year>
<price>79.99</price>
</book>
</store>')
Insert Into Store Values (3, '<store>
<book category="WEB">
<title lang="en">SSIS Quick Start</title>
<author>John Bothner</author>
<author>Per Bothner</author>
<year>2004</year>
<price>79.99</price>
</book>
</store>')
Go
Select * From Store
Where Store.Metadata.exist('/author[not(contains(., "Per Bothner"))]') >= 0
Go
Mahesh[/font]
MH-09-AM-8694

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ronald H SSCarpal Tunnel Points: 4053 More actions · Answer 1

Thanks for trying to make the table description at least... Though just empty rows isn't going to help us much I think?

Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2

GSquared SSC Guru Points: 260824 More actions · Answer 2

I just tried your test case, and it returned all rows. That seems to be the opposite of what you posted. What am I reading incorrectly?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 3

[font="Verdana"]No, you are right. I have not mentioned the Delete statement.

Delete From Store

Where Store.Metadata.exist('/author[not(contains(., "Per Bothner"))]') >= 0

It should delete those elements whos author is other than "Per Bothner".

Mahesh[/font]

MH-09-AM-8694

Mark Cowne One Orange Chip Points: 26966 More actions · Answer 4

Mahesh Bote (1/16/2009)
[font="Verdana"]No, you are right. I have not mentioned the Delete statement.
Delete From Store
Where Store.Metadata.exist('/author[not(contains(., "Per Bothner"))]') >= 0
It should delete those elements whos author is other than "Per Bothner".
Mahesh[/font]

"exist" will return either 0 or 1 so your expression will use both.

/author should be either //author or /store/book/author

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 5

[font="Verdana"]

Mark (1/16/2009)
Mahesh Bote (1/16/2009)
[font="Verdana"]No, you are right. I have not mentioned the Delete statement.
Delete From Store
Where Store.Metadata.exist('/author[not(contains(., "Per Bothner"))]') >= 0
It should delete those elements whos author is other than "Per Bothner".
Mahesh[/font]
"exist" will return either 0 or 1 so your expression will use both.
/author should be either //author or /store/book/author

Thanks Mark for extra information. However my concern is to delete the specific element from XML.

Mahesh[/font]

MH-09-AM-8694

noeld SSC Guru Points: 96590 More actions · Answer 6

How about?

IF OBJECT_ID('Store') IS NOT NULL

DROP TABLE Store

go

CREATE TABLE Store

(Id Int,

Metadata XML)

Go

Insert Into Store Values (1, '<store>

<book category="WEB">

<title lang="en">XQuery Kick Start</title>

<author>James McGovern</author>

<author>Per Bothner</author>

<year>2003</year>

<price>49.99</price>

</book>

</store>')

Insert Into Store Values (2, '<store>

<book category="WEB">

<title lang="en">SSIS Quick Start</title>

<author>John McDonald</author>

<author>Per Bothner</author>

<year>2004</year>

<price>79.99</price>

</book>

</store>')

Insert Into Store Values (3, '<store>

<book category="WEB">

<title lang="en">SSIS Quick Start</title>

<author>John Bothner</author>

<author>Per Bothner</author>

<year>2004</year>

<price>79.99</price>

</book>

</store>')

Go

UPDATE Store SET Metadata.modify('delete /store/book/author[not(contains(., "Per Bothner"))]')

Go

SELECT *

From Store

Go

* Noel

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 7

[font="Verdana"]Thank you very much noel, it works.

Mahesh[/font]

MH-09-AM-8694