November 18, 2010 at 2:38 pm
Hi, I've gone through the following link and attempted to use XML Indexes to query data.
http://msdn.microsoft.com/en-us/library/ms345117(SQL.90).aspx
My test cases is a table that consists of 250,000 rows of XML Data. The table has a primary key that is an identity column.
CREATE TABLE dbo.ServiceQueue (
ServiceQueueID int not null identity(1,1),
XMLDat xml
);
ALTER TABLE dbo.ServiceQueue
add constraint PK_ServiceQueue primary key clustered (ServiceQueueID);
After creating the structure of the table and importing the data, I built the Primary XML Index.
CREATE PRIMARY XML INDEX IDX_ServiceQueue_PrimaryXML ON dbo.ServiceQueue(XMLDat);
Then I read that I should create Secondary XML indexes and use appropriate queries for them.
The type of query that I'd like to use would be one that would return a particular row from my table where I have a specific value.
The query I tried was
select *
from dbo.ServiceQueue
where XMLDat.exist('/data/CustomerID[. = "1889405"]') = 1
First I tried this with just a Value XML Index.
CREATE XML INDEX IDX_ServiceQueue_ValueXML on dbo.ServiceQueue(XMLDat)
USING XML INDEX IDX_ServiceQueue_PrimaryXML FOR VALUE
I thought this would've worked out... I was looking up a single value and it made sense to me. Maybe the query is written incorrectly.
I then created the other 2 Secondary XML Indexes, hoping one would work.
Still, my execution time is really long.
The XML is very simply formed. I basically imported from another table with SELECT * FROM MyTable FOR XML, AUTO ELEMENTS
So the XML basically looks like
<data><CustomerID>1</CustomerID><CustomerFirstName>Bob</CustomerFirstName></data>
Any help or guidance would be greatly appreciated.
Please do not comment on the usage of XML columns as a solution. It's not my choice in this particular case, and either way I'm finding it to be an interesting exercise.
November 18, 2010 at 3:01 pm
November 18, 2010 at 4:06 pm
Ahh, I see the problem now. It seems I have an untyped XML data type on that table.
http://msdn.microsoft.com/en-us/library/ms184277.aspx
This would be my problem.
Use typed xml data type in the following situations:
*
You have schemas for your XML data and you want the server to validate your XML data according to the XML schemas.
*
You want to take advantage of storage and query optimizations based on type information.
*
You want to take better advantage of type information during compilation of your queries.
Looks like I've a bit more work to do before I can get this going. I'll follow-up with my progress.
Thanks Kendal!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply