January 30, 2009 at 1:25 pm
I have some data stored in an XML type column in SQL 2005 that has multiple values for each node, but which values it has are inconsistent, and I can't figure out how to build a query for that.
For example, I can get the first value like this:
declare @XML XML
select @XML = '{row Val1="Value 1" Val2="Value 2" /}' -- replace braces with carets because of forum
select @XML.value('(/row/@*)[1]','varchar(50)')
But I cannot seem to figure out how to get the second value with a similar query. I've gone through the w3c pages till my eyes are crossed, and searched every combination I can think of in Google and Live.
Every web page I've been to assumes you already know what the attributes are named.
Anyone have any ideas on this?
- 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
February 1, 2009 at 6:47 am
Well I am sure no XML guru either, but doesn't this work?:
select @XML.value('(/row/@*)[2]','varchar(50)')
Though admittedly, every time I think that I have something figured out in XML and then try to apply it, I fall flat on my face. I find it all extremely frustrating too...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 1, 2009 at 6:50 am
[Oh wait, maybe it's this...
select @XML.value('(/row/@*[2])[1]','varchar(50)')
Sorry, I don't have time to test these right now... 🙁
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 1, 2009 at 11:03 am
OK, I'm back and can test them now...
...And actually, both of these work for me. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 2, 2009 at 6:59 am
You know, somehow I thought I had tried those and they didn't get me what I wanted. I was wrong. They work. Thanks. 🙂
- 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
February 2, 2009 at 9:41 am
Glad I could help, Gus. (Suprised, too. I almost never get XML queries right the first time... 🙂 )
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 16, 2009 at 12:24 am
Thanks RBarryYoung,
You know i have started to learn XML in sql2005 concepts from yesterday. I also wanted to display the second value,but i didn't find it in HELP. Anyway I will try it today and come back to you.
GSquard/RBarryYoung,
will you suggest me some good book names to learn about XML in SQL2005?
karthik
March 16, 2009 at 8:35 am
karthikeyan (3/16/2009)
Thanks RBarryYoung,You know i have started to learn XML in sql2005 concepts from yesterday. I also wanted to display the second value,but i didn't find it in HELP. Anyway I will try it today and come back to you.
You know, this is a good point, Karthik. The key is those instance indicators ("[1]"), that you are told that you have to use because you can only return singletons to the .Value method. However, virtually all of the examples use only "[1]" so it is very easy to mistake them for the equivalent of a TOP-like operator, instead of the instance indexer (like an array index value) that they really are.
GSquard/RBarryYoung,
will you suggest me some good book names to learn about XML in SQL2005?
Oh, definitely, you want Pro SQL Server 2008 XML by Michael Coles. Don't worry about the "2008" part. Less than 15% of the book is 2008 specific and there is no comparable book (AFAIK) for just 2005.
You can also read a great introductory SQL 2005 XML article by Michael at this very site here[/url].
Disclaimer: I know Michael, because I met him at PASS-2008. However, I had already read this book and thought that it was great before I met him. Still, he is a great guy, so that might influence me some. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply