need help on XML datatype data; Very Urgent

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

need help on XML datatype data; Very Urgent

Author

Topic

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2009-12-17 : 09:37:01

Hi All

I have a table with XML datatype column and the data will be like below

XMLData :

<food><name>Belgian Waffles</name><price>$5.95</price><description>two of our famous Belgian Waffles with plenty of real maple syrup</description> <calories>650</calories></food>
<food><name>Strawberry Belgian Waffles</name><price>$7.95</price><description>light Belgian waffles covered with strawberrys and whipped cream</description><calories>900</calories></food>
<food><name>Berry-Berry Belgian Waffles</name><description>light Belgian waffles covered with an assortment of fresh berries and whipped cream</description><calories>900</calories></food>

here i have PRICE attribute only in first rows. If i want to get only those rows how can i do.

Right now i am using like this

XMLData Like '%<Price>%'

is there any other alternative which gives results fast.

I am going to have lacs of records.

I have enabled FULL TEXT search also.

I am trying to use CONTAINS predicate, its searching only text values not the attributes.

Please help me.

Its urgent

Thanks in advance

Thanks

Jack

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-17 : 10:29:03

This?

declare @t table (id int, XMLData xml)
insert @t
          select 1, '<food><name>Belgian Waffles</name><price>$5.95</price><description>two of our famous Belgian Waffles with plenty of real maple syrup</description> <calories>650</calories></food>'
union all select 2, '<food><name>Strawberry Belgian Waffles</name><price>$7.95</price><description>light Belgian waffles covered with strawberrys and whipped cream</description><calories>900</calories></food>'
union all select 3, '<food><name>Berry-Berry Belgian Waffles</name><description>light Belgian waffles covered with an assortment of fresh berries and whipped cream</description><calories>900</calories></food>'

select * from @t where XMLData.exist('food/price') = 1

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-12-17 : 12:53:02

And remember. Avoid XML whenever possible.

An infinite universe is the ultimate cartesian product.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-17 : 13:11:32

quote:
Avoid XML whenever possible.

Nice to see a good evaluation of the pros and cons. Please feel free to elaborate on your views.

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-12-17 : 13:30:32

There's no need to reinvent the wheel...
See Rob's blog here

http://weblogs.sqlteam.com/robv/archive/2003/09/28/182.aspx

An infinite universe is the ultimate cartesian product.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-17 : 13:59:53

The last line of that blog entry is "XML and XSL were the best way to do it. :)". I don't see how this supports your assertion.

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-12-17 : 16:19:21

Wow, talk about cherry picking.

"And if anyone is interested, I do use XML too, but only for one project, that requires a heavily formatted HTML document. XML and XSL were the best way to do it. :)"

An infinite universe is the ultimate cartesian product.

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2009-12-18 : 05:31:33

thanks for your reply friends....

how can i use this in that select statement

declare @tagname varchar(250)
set @tagname = 'price'

because that tagname is getting generated dynamically.moreover i dont want to give element name like 'food'.

can i able to get the results simply giving by tagname.like

select * from @t where XMLData.exist('@tagname') = 1

thanks in advance

Thanks

Jack

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-18 : 05:55:09

[code]declare @tagname varchar(250)
set @tagname = 'price'

declare @t table (id int, XMLData xml)
insert @t
select 1, '<food><name>Belgian Waffles</name><price>$5.95</price><description>two of our famous Belgian Waffles with plenty of real maple syrup</description> <calories>650</calories></food>'
union all select 2, '<food><name>Strawberry Belgian Waffles</name><price>$7.95</price><description>light Belgian waffles covered with strawberrys and whipped cream</description><calories>900</calories></food>'
union all select 3, '<food><name>Berry-Berry Belgian Waffles</name><description>light Belgian waffles covered with an assortment of fresh berries and whipped cream</description><calories>900</calories></food>'

select * from @t where XMLData.exist('food/descendant::node()[local-name(.) = sql:variable("@tagname")]') = 1[/code]

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-18 : 06:06:59

quote:
Originally posted by cat_jesus

Wow, talk about cherry picking.

"And if anyone is interested, I do use XML too, but only for one project, that requires a heavily formatted HTML document. XML and XSL were the best way to do it. :)"

cat_jesus - your assertion was "Avoid XML whenever possible.". I don't see how this quote supports that assertion. Could you explain?

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-12-18 : 10:05:07

Ryan, that quote doesn't- it wasn't meant to. I added that quote because you cherry picked the data. In other words, you took the very last sentence out of context.

XML should only be used when absolutely necessary. It (usually)adds unnecessary complexity. I have yet to see a good reason to use XML that didn't involve sending data to web servers and even then it's usually not really needed and doesn't help.

An infinite universe is the ultimate cartesian product.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-18 : 13:18:54

Hehe

. Yes - what you mean is "only use XML when appropriate", rather than "avoid XML whenever possible". I was only playing with you

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources