How To Break SQL Server’s XML Data Collection



When we first met, it was about 2011. I had to load files full of you into a table. It wasn’t so bad.


You were well-formed, and didn’t change from file to file. Even using SQL Server 2005, I could take you.

Later on, we got really close when I awkwardly started working on sp_BlitzCache, like a divorce-dad trying to figure out what you like so our weekend visits wouldn’t be so painful.

We learned to get along. Heck, we had some good times.

But the plan cache isn’t the only place that you get used. No, Microsoft uses you in all sorts of crazy, mixed-up places.

  1. Extended Events
  2. Deadlocks
  3. Service Broker (I know, I know…)
  4. SSIS… things. Probably.

There’s likely a bunch more, but hey. There’s only so much sunshine.

It’s Time We Talk

No, I’m not leaving you for JSON — JSON isn’t looking for anything serious. Heck, we’re not even sure if JSON will get deprecated for whatever is popular on Hacker News in a couple weeks.

It’s just that, when it comes to storing information about deadlocks, you haven’t been handling yourself so well.

I think you’re on Predefined Entities. I think you have a problem.

You see, if someone creates a table that has a funny character in it — “, &, >, or < — you don’t sanitize all your inputs. Then you throw an error when we try to parse you.

The worst part is that you get it right sometimes.

Call me Sometimes.

But other times… Other times!

Lordy Lordy, Lordy

Even execution plan XML gets this right.


We Got Some Breaking Up to Do

If you don’t change your ways, (hint — that’s a Feedback Item, you should go vote for it if you want me to like you), you’re just going to be another known limitation in a long line of scripts.

Thanks for reading!

Previous Post
Why sp_BlitzLock Can’t Show Complete Columnstore Deadlock Information
Next Post
[Video] Office Hours 2018/4/4 with Special Guest Pinal Dave

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.