Transforming XML data into a rowset using the nodes() method

Usually there are a couple of scenarios in which you want to take XML data and convert it into a regular relational rowset.  In SQL 2000, you probably used OpenXML for this, which works pretty well if you are only working on a single piece of XML data, but as soon as you want to…


SQL Server Query Optimizer Blog

Ian Jose, an Architect here in SQL Engine working on the Query Optimizer, has just started a blog.  Lots of good tips and practical advice for getting the most out of SQL Server’s query optimizer. 


Using fixed values does not result in smaller instances…

XML Schema has an interesting feature where you can define a value as being “fixed”.  The meaning here is basically that an attribute or element has to have a particular value, and if it is not there in the instance, then that value is implied.  However, it is important to realize that this doesn’t have…


More Gadgets! “gadgets” folks have updated their blog to include some gadgets that you can add to  I’m trying out the “Flickr” gadget as well as the “to-do” list gadget.  Are you using yet?


Answer: Fulltext and XML in SQL Server 2005

A little while ago I threw out this quiz regarding XML and SQL Server 2005’s full text search support.  As I stated in the quiz, there is a subtle difference between what you can get with contains() and the fulltext support.  Specifically, the difference lies in how our full text engine injects whitespace between element content. …


Jinghao blogs about SQL Server in Chinese

Jinghao (my manager here in the SQL Server Engine team) has started up a blog about SQL Server.  His blog is targetting Chinese speakers.  However, if you are like me and don’t read Chinese you can get the fish to translate it for you.


Peter Jackson to Exec Produce Halo movie!

This one is making the rounds here at work.  In case you don’t recognize the name, he is the guy who made the Lord of the Rings movies. Peter Moore, corporate vice president of Worldwide Marketing and Publishing for Xbox at Microsoft, also announced that triple Academy Award winners Peter Jackson and Fran Walsh…


Quiz: Fulltext and XML in SQL Server 2005

Using SQL Server’s fulltext engine is a great way to optimize your XQuery statements which use XQuery’s contains function in a predicate.  However, a bit of care has to be taken, because sometimes you might get results which you don’t quite expect. Let’s assume that I have a fulltext catalog on the table [t1], and that…


XQuery Contains vs Fulltext Contains

The XQuery/XPath 2.0 functions and operators specification defines a function called “contains.” It performs a substring match on the two input strings and returns true if the value of the 2nd string is contained in the value of the 1st string. An example: select convert(xml, ”).query(‘contains(“foo”, “oo”)’) This query will return the value “true” because…


TEMPDB optimizations for SQL Server 2005 – Help your XML Datatype performance! Wei mentions a couple of optimizations that can be made for TEMPDB heavy applications.  Since XML datatype uses TEMPDB heavily for LOB storage, I recommend checking this out and instrumenting these if you are seeing some performance issues.