Choosing Paths for Selective XML Index – Part 2 – Using the SXI XEvents

In part 1 of this series, we tried a simple example from the Books Online without any SXI index at all, and an SXI with all the paths covered. No XEvents were emitted in either case. Now let’s change the XSI around (by dropping and recreating, although we could use ALTER, so envision a drop […]

Choosing Paths for Selective XML Index – Part 1

About a month ago, I wrote a series of blog entries on the Selective XML Index introduced in SQL Server 2008 SP1. Just to summarize before I start up again, the Selective XML Index (I’ve abbreviate as SXI) is a feature introduced to allow indexing of only certain paths in an XML column. It uses […]

Selective XML Index – Why is this compelling?

OK. VM with the SP1 CTP is shut down. Fun's over (until I think of something new and cool or you think of something and tell me about it). Time to answer Aaron's implied question about "what makes this so special it should be shipped in a Service Pack, no less?". This could be a […]

Selective XML Indexes – Learning the rules through error messages

Experimenting without docs is a good way to get lots of error messages along the way. I'm learning by attrition, and it seems like I'd be better off knowing the rules. It's not the best way to learn anything, but you can at least learn the limits through the error messages. I was tired of […]

Selective XML Index – Secondary Selective XML Indices

I've only been talking so far about the "primary" Selective XML Index. But you can also create 0-n "secondary" Selective XML Indexes. The syntax looks a little bit like secondary "non-selective" XML Indexes in that you use the "USING [related XML index]". You specify one (and only one) pathspec. Additionally, the XML value that the […]

Selective XML Index – Implementation Details

A while after I posted part 1, there was a twitter message from Aaron (@AaronBertrand), a well-known "smart person" in the SQL Server space, about using feature packs as "ship vehicles", especially when they (possibly) have the effect of requiring keeping all instances in-sync at the service pack level to keep things working and possibly even making SP […]

Selective XML Indexes in SQL Server – First Try…it works

OK, so let's try something. Load a bunch of documents. Although, actually this part works with an empty table. create selective xml index fooidx2 on foo(thexml) for ( pathname1 = 'foo/bar/baz2' ); Msg 6379, Level 16, State 201, Line 1 selective XML index 'fooidx' already exists on column 'thexml' in table 'foo'. Multiple selective XML […]

Getting started with Selective XML Indexes in SQL Server

So, it was announced that SQL2012 SP1 CTP4 was released yesterday. You've likely already heard this (more than once, everyone seems to revel in repeating announcements from the team). And it contains a new feature (I was surprised, thought that wasn't supposed to happen, new features in service packs, meanwhile…) called Selective XML Indexes. Interesting concept, […]

Saving the contents of a SQL Server XML column to a file

While preparing for my TechEd talk on filetables and full-text search, I came across this tidbit that I thought was interesting. With the advent of XML support in databases, specifically SQL Server's XML data type in SQL Server 2005, there has been two main ways to store XML; in files on the file system, and […]

Does everybody get that? (XML or relational for “multi-table” XML documents)

This post is an example of the "bring your own problem" approach in the classroom, inspired by upcoming SQLskills Immersion Events in August. See previous posting for the reason behind the blog post title. SQL Server is a relational database that supports XML. But just because your data is received in XML format doesn't necessarily […]

XEvents in SQL Server 2012: No more “lazy XML” in event harvesting scripts

I've said before how thrilled I am will the new extended event UI in SSMS for SQL Server 2012. However, you might be one of the early adopters who made up their own scripts to define extended event sessions, and use hardcoded scripts to harvest the results. So, you may run into this problem if […]

SQL Server 2008 XML: Let there be ‘let’

SQL Server 2008 adds support for the 'let' clause in FLWOR expressions. The for and let clauses have a similar purpose, to bind content (tuples) to variables.  Either one can begin a FLWOR expression: declare @x xml = ''; select @x.query(' for $i in (1,2,3) return $i '); > returns 1 2 3 declare @x […]

XQuery 1.0 et al are now W3C recommendations

Michael Rys (and who would know better/sooner about this) just announced on his blog that XQuery 1.0 and associated specs (including XPath 2.0 and XSLT 2.0) are now official W3C recommendations. Congratulations to the working group on this. The specification process is also underway for a standard XQuery Update Facility and XQuery/XPath Full-Text query facility. The […]

What’s that stuff good for, anyway?

There's an interesting article that caught my attention in this month's (July 2006) MSDN magazine by John Mollman about building the MSDN aggregation system. The system uses SQL Server 2005 Service Broker for reliable messaging, activation procedural written in T-SQL, SQLCLR for interactions with the world outside the database and the XML data type and […]


I've been looking at the DLINQ syntax a bit more (I'm participating in a BOF at TechEd next week), and its always struck me how, at its most basic, its reminiscent of XQuery FWOR. Yes I did leave out the L (Let) on purpose. I guess its the relative placement of the SELECT part of […]

Move over developers! SQL Server XQuery is actually a DBA tool

While teaching SQL Server 2005 to developers, I'd always point out the irony in the fact that the new EVENTDATA() function, used to return information in a DDL trigger or EVENT NOTIFICATION, returns XML and would be used almost exclusively by DBAs. This seems almost like a nefarious plot to force DBAs, who would be […]

XQuery methods and determinism

If I store XML in an XML data type column its sometimes useful for performance to be able to hoist the value of a particular item to its own persisted computed column. The column can then be used in SQL queries without having to recalculate the XQuery each time its accessed. For example, if I […]


I've always been slightly puzzled by the permission "EXECUTE" on an XML SCHEMA COLLECTION. Say I have an XML SCHEMA COLLECTION named MySchemas and a table that uses it: CREATE TABLE MyDocuments (   id INT PRIMARY KEY IDENTITY,   thexml XML (MySchemas) ) And suppose I have a user named FRED that I grant access […]

For all you XML-phobes that lke DDL triggers and Event Notifications

After speaking yesterday and recanting my story (for about the 100th time) about EventData being XML as a “nefarious plot” to require DBAs to learn XML, I wrote this fairly simple XQuery (actually dead simple XPath) function to change EventData into a rowset. It works based on the fact that you’ve deposited EventData into a […]

SQL Server 200x wish list – part 2, XML and XQuery

OK, because it was Michael that asked originally, XML/XQuery wishes for SQL Server 200x. In no particular order. Full compliance with "standard" W3C XQuery. If the standards committee finishes before SQL Server 200x ships. Else it will become a "standard" no one follows. Hmm…like SQL. Reason for this is twofold. 1. Folks who get a […]

SqlTypes.SqlXml and impersonation

I've been working on a student question about using Impersonation inside of a stored procedure. This one's worth sharing. You can do impersonation using the .NET SqlClient data provider using code roughly like this: WindowsIdentity w = SqlContext.WindowsIdentity; WindowsImpersonationContext c = w.Impersonate(); // do something here c.Undo(); The rule is that in the "do something […]

XML Schema for the truly lazy

Here's something I've noticed you can do with SQL Server 2005 and Visual Studio 2005. Take a set of XML documents with the same basic structure. Load one into Visual Studio 2005. Click on the XML menu, Create XML Schema. Make any refinements to the XML schema that is produced that you want, based on […]

Go ahead, make a validation mistake…

One of the nice surprises in the April CTP is that SQL Server 2005 XML schema validation errors come with a location now. That's handy. Here's an example: Msg 6926, Level 16, State 1, Line 1 XML Validation: Invalid simple type value: '1134'. Location: /*:Invoice[1]/*:LineItems[1]/*:LineItem[1]/*:Sku[1] Even better would be a line and column number that […]

Mailing the graphic showplan to a friend

I've always liked the graphic showplan in SQL Server query analyzer. The biggest hassle with it came when you wanted to send the plan to a friend. Or maybe MS support, but support is your friend too… right? You could send screenshots (which had the annoying habit of never displaying those hover-over stats) or go back […]

FOR XML…XMLSCHEMA and schema validation

When reading the CTPNotes file from the new Feb CTP build I stumbled across the fact that the XML schema that contains SQL data types ( is now built-in to the server. Although this may not mean much to most people, it gave me the chance to try something that Dan Sullivan thought up for […]

Fun with static XQuery evaluation – 4 – answers and wrapup

Here’s the answers to the question from Fun With static XQuery evaluation – 2 — start with a schema collection CREATE XML SCHEMA COLLECTION ages AS ‘<xs:schema xmlns:xs=”” targetNamespace=”urn:ages” xmlns:tns=”urn:ages”> <xs:element name=”age” type=”xs:int”/> </xs:schema> ‘ GO DECLARE @x xml(ages) SET @x = ‘<age>12</age>’ — fails ??! SELECT @x.query(‘string(/age)’) GO This fails because there can be […]

Fun with static XQuery evaluation – 3

After the last two entries, you might be thinking "I guess I can never use text() as a node test with typed XML again". Not so. The error message reads: 'text()' is not supported on simple typed or '' elements. So what's left? Mixed content, for one thing. Mixed content consists of a mixture of […]

Fun with static XQuery evaluation – 2

Reference back to the previous entry. Now that we know the rules, let's try them out: — snip — Data(),text() and string() accessors XQuery has a function fn:data() to extract scalar, typed values from nodes, a node test text() to return text nodes, and the function fn:string() that returns the string value of a node. […]

Fun with static XQuery evaluation – 1

There's been lots of puzzled faces lately when I try to explain doing XQuery against strongly typed XML (XML typed by a SCHEMA COLLECTION) vs untyped XML. The largest FAQ is why when I have this document: <person>   <name>bob</name>   <age>51</age> </person> using the value function (after assignment to @person) @person.value('/person/age', 'int') returns my […]

XQuery and the useful XML index

Haven't been blogging as much recently, as I've been teaching and travelling quite a bit. Last week though, I received an interesting query on XQuery query plans (that's more uses of the word query in one sentence than I've seen before). We were looking at SQL query plans when XQuery is involved. I'd remembered that: […]

HTTP Endpoints and FORMAT=NONE

HTTP Endpoints in SQL Server 2005 are normally a way to support SQL Server clients that speak the SOAP protocol. It turns out that, although the HTTP endpoint requires a SOAP request, the response need not necessarily be SOAP or even XML. You can return something other than the vanilla document-literal SOAP format; the way […]

Another improvement in Dec CTP

Another little thing I'd found had changed in Dec CTP. I'd reported a bug on this one and knew it was gonna be fixed eventually, but better sooner than later. I came up when Dan Sullivan suggested his "universal web service" based on SQL Server's XML data type and XML Schema Collections. Here's the prototype: […]

A First Look at Dec CTP

OK, I couldn't help myself. The Dec 2004 CTP-specific readme had quite a number of things that are new. There are many improvements in SSIS, for example. The “real“ readme and the known issues file (sqlki.chm) still have July dates on them. But there are always a few little things I have queued up to try with a […]

More ADO.NET 2.0 and XML data type

When you retreive a rowset that contains an XML data type column or use an XML data type column as parameter input, you have two choices. You can use the vanilla .NET type, System.String, or a special types from the System.Data.SqlTypes namespace called SqlXml. ADO.NET 2.0 handles conversion when you use strings (as long as […]

How do I get (N) rows from a SQL Server XQuery?

Every time I demonstrate using XQuery against a table that contains an SQL Server XML data type to a new class, a get about one out of four people with puzzled looks. And they're the ones who know XPath and XSLT. The part that seems weird to them is that a XQuery against what they […]

Formatting XML/XQuery in SQL, and caveats

And now, for something a little technically lighter… I've taken to using a convention when writing statements that involve XML/XQuery and SQL together. I put the XML input or XQuery program on separate lines than the SQL part of the statement, this seems to make both query languages and also inline XML data easier to read when they're […]

XQuery Schema Validation Clarification

Rereading the post about this from last night, it appears that I may have used an ambiguous analogy when attempting to figure why this feature works the way it does, and given folks the wrong idea. It has to do with whether the results of the query would be wrong or the query itself is “incorrect”. Using […]

Early XQuery schema validation

Dan Sullivan pointed this out to me a couple of weeks ago. I'd forgotten about it, but it came back to bite me in a demo last week. It's a feature. If you have an XML column, variable, or UDF return value that's tied to an XML SCHEMA COLLECTION, any XQuery against that XML will […]

Thought on Microsoft client XQuery

I've known for a couple of weeks now that XQuery and the new XML-SQL client mapping have been dropped from System.Xml in .NET 2.0. The XQuery implementation over the XML data type in SQL Server 2005 is NOT going away, of course. Just the client-side bits. Folks are encouraged to keep using XSLT 1.0 and […]

Least privilege and HTTP endpoints

A few weeks ago, I was surprised by an error message when attempting to create an HTTP endpoint with CREATE ENDPOINT. The error was "You do not have permission to perform this operation". The reason I was surprised was that SQL Server 2005 was running in a domain environment and I was logged on as […]

Office 2003 XML schemas and SQL Server 2005

After lots of interesting (but fruitful) experiences with the XQuery engine in SQL Server 2005, I decided that I'd rather have an XML schema collection for everything than put [1] (that's the numeric predicate meaning "item 1 using 1-based ordinals") everywhere in the XQuery that I needed a singleton. It's a good idea to use "typed XML" anyway. Dan […]

XML and SQL Server: stream, column, or string?

With the introduction of the XML data type (using an XML data model) comes there's a choice of pulling XML out of the database as a stream or column. Using an XML data type as a column type will get you a column of type XML. That's SqlXml to you in .NET. SELECT … FOR […]

HTTP Endpoints and Windows XP SP2

SQL Server 2005's HTTP endpoint functionality uses http.sys, the HTTP stack in the kernel, to do it's processing. Until lately Windows Server 2003 was the only OS to implement http.sys and HTTP endpoints only worked when SQL Server 2005 ran on this OS. Windows XP SP2 shipped with http.sys and HTTP endpoints are now enabled […]

Using the data() function in XQuery

Some folks have asked why I appear to use the XQuery data() function on occasion, but not always, when I'm doing demos. If you want the atomic value of an attribute, rather than the attribute node itself, data() is your friend. data() makes an atomic value from any XQuery item, but with attribute nodes it's […]

HTTP endpoint session support in SQL Server 2005

I've been trying out the new session support in HTTP endpoints. You manipulate this through SOAP headers, and looking through the list of SOAP headers supported allowed me to understand this feature from an entirely different perspective. It looks like the session support makes this into more of an alternate client stack than a mechanism […]

SqlTypes.SqlXml and XmlReader.Create

As Mark Fussell will tell you, one of the coolest things in System.Xml 2.0 is the static creator methods on XmlReader and XmlWriter. It made his top 10 list, albeit at number 10. I've been looking into how the client side handles SQL Server 2005's XML data type and was bemoaning the fact that System.Data.SqlTypes.SqlXml […]

About Web Services and “Schema + Any”

About a month ago, there was another chapter in the long discussion about DataSets and web services. An old cohort Scott Hanselman fired off the first salvo, which was taken up by Ted Neward. Doug Purdy responds that Purchase Orders are root of all evil and lots of other chimed in. I'm just catching up… This comes […]

A Comment About Comments (XQuery Comments)

I was checking some of the SQL Server 2005 features on the way over on the plane. The latest build that I have is SQL Express build, fairly recent. Many of the XQuery features have been updated to Nov 2003 XQuery spec. Very cool. But comments wasn't one of them. A nit, I know. The {– […]