February 5, 2008 at 7:30 pm
Guys,
What level of indexing is supported for the XML data type?
How is the indexing (indexes) supported in sql server 2005 for XML data type
If the data is stored in the following xml format, what is the effective way to query SSN
Any suggestions and inputs would help
Thanks
February 5, 2008 at 11:55 pm
You can define primary and secondary indexes on an XML column in the table. A primary XML index is the same as a clustered index. A secondary XML index is the same as a non-clustered index. Basically the XML BLOB is shredded and stored as an index in an internal table. Note that the original XML BLOB in the table is still there - i.e. an XML index create a lot of *extra* data.
Look up 'XML index' in the index of SQL Server 2005 Books Online rather than having someone explain the best practices/methods on the forum - BOL is quite comprehensive on the subject.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
February 6, 2008 at 12:00 am
Btw - if you're trying to extract data from the nodes within the XML BLOB, check out the nodes(), query(), value() XML functions too in BOL.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply