November 10, 2011 at 6:28 am
Apologies for this i realize its not the best practice, but i dont have time to learn the in's and outs of xml querying right now and need my answer pretty quick, quicker than i can learn xml querying.
I have a query plan. Its stored in an xml column. I want to search through the query plans for anywhere there is an attribute "Database" that equals a specific name, so where Database="dbName".
Is this possible or do i need to know the path to the node that contains the attribute?
Ive done quite a few searches for tutorials on XML in sql server but there is nothing very compehensive. can you recommend a good book/material that got you up to speed on querying xml?
November 10, 2011 at 7:02 am
The XQuery documentation is horrific. Struggling through it is almost a career all by itself. The way I've had to learn it is by trying things, changing one variable at a time, till I learn on my own how it works. So, unfortunately, the only resource I can recommend on T-SQL XQuery is ask questions on this forum, and try things out. There might be better, but I haven't found it yet.
You don't need to know the path to a node. XQuery allows you to use a double-slash to indicate a variable path of variable depth.
Here's a sample of variable depth XQuery:
DECLARE @XML XML = '
<Items>
<Depth1>
<Database Name="DBName1" />
</Depth1>
<Depth1>
<Depth2>
<Database Name="DBName2" />
</Depth2>
</Depth1>
</Items>';
SELECT X.Y.query('.'),
X.Y.query('.').value('(/Database/@Name)[1]','varchar(100)')
FROM @XML.nodes('//Database') X(Y);
Does that help?
- 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
November 10, 2011 at 9:21 am
Thanks for that. it kind of helps. il adapt it to work against a table column and add a where clause. thanks for the help. really appreciate it!
November 10, 2011 at 9:49 am
If you want to post a sample of the data and the table structure (create table script), we can probably help with the exact query.
One thing that may matter a lot is how you write the Where clause. If it's querying anything in the XML column, the exist() method, against an XML index, can be literally thousands of times faster than using a value() method call against a value.
An example of that is in this discussion: http://www.sqlservercentral.com/Forums/Topic1197494-391-1.aspx
- 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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply