T-SQL Tuesday #16: Using Aggregate Functions in XML
This blog entry is participating in T-SQL Tuesday #16, hosted this month by Jes Schultz Borland(Blog|@grrl_geek). You are invited to visit her blog to join the blog party. You are welcome to write your own participating blog post for the party or just to read more blogs participating in this month’s theme: Aggregation.
When I first heard the topic, I was worried that I would not be able to come up with a topic that would allow me to impart some knowledge that is not already known by everyone. I think I managed to do that. For my part in this month’s T-SQL Tuesday, I am going to give a brief demonstration on how to use aggregate functions in XML queries.
The XML We’ll Use
The easiest part of this exercise was coming up with the XML to use. One of the reasons I think DBAs should know how to query XML data is to programmatically parse and query execution plans. I have a half dozen or so old query plans on my desktop, so I just grabbed an old sqlplan (it was named BadPlan.sqlplan, by the way), opened it in Notepad, and grabbed the XML out of it. I deleted the top line from it, of course: <?xml version=”1.0″ encoding=”utf-16″?>.
The XML is too long according to my blog platform, so i have attached the XML: SQL Plan XML. simply copy the contents of the XML into the below declare statement. You can also download all of the code samples inclduing the XML from here: AggregatesInXML.sql.
Declare @XML XML = 'Add XML Content Here';
Querying the XML
There are five aggregate functions that you can use XML that we will look at today: count(), sum(), min(), max(), and avg(). The syntax for using these are fairly straightforward. There are different ways to do this, but we’re going to use the simplest approach.
Sample syntax for using the functions will look like this:
Select @XML.query('declare namespace <namespace identifier>="<namespace URL>"; <aggregate function>(//<namespace identifier>:<node>)')
Here is how i would get the count of the number of times the RelOp node appears in the plan:
Select @XML.query('declare namespace spsql="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; count(//spsql:RelOp)')
Or if I wanted it for a specific subpath only:
Select @XML.query('declare namespace spsql="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; count(//spsql:QueryPlan/spsql:RelOp)')
I could even use the full path:
Select @XML.query('declare namespace spsql="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; count(/spsql:ShowPlanXML/spsql:BatchSequence/spsql:Batch/spsql:Statements/spsql:StmtSimple/spsql:QueryPlan/spsql:RelOp)')
If you are entering the code along as we go, you undoubtedly noticed that SSMS treats the return value as XML. We can use the value() method instead of query to return it as a specific data type:
Select @XML.value('declare namespace spsql="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; count(//spsql:RelOp)', 'int')
For the other aggregate functions, I’m going to show just the value() method:
Select @XML.value('declare namespace spsql="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; count(//spsql:RelOp)', 'int') Select @XML.value('declare namespace spsql="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; min(//spsql:RelOp/@EstimateIO)', 'decimal(11, 4)') Select @XML.value('declare namespace spsql="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; max(//spsql:RelOp/@EstimateIO)', 'decimal(11, 4)') Select @XML.value('declare namespace spsql="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; sum(//spsql:RelOp/@EstimateIO)', 'decimal(11, 4)') Select @XML.value('declare namespace spsql="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; avg(//spsql:RelOp/@EstimateIO)', 'decimal(11, 4)')
Results:
———–
2
(1 row(s) affected)
—————————————
72.3752
(1 row(s) affected)
—————————————
1856.7500
(1 row(s) affected)
—————————————
1929.1252
(1 row(s) affected)
—————————————
964.5626
(1 row(s) affected)
You can download all of the above code samples from here: AggregatesInXML.sql.
T-SQL Tuesday #16: Using Aggregate Functions in XML
[…] You are invited to visit her blog to join the blog party. You are welcome to write… [full post] SQLSoldier SQLSoldier sql servert-sqlt-sql tuesdaytips & tricks 0 0 […]
Jes Schultz Borland
Thanks for participating! Great info – I didn’t know how to do this!
Less Than Dot - Blog - Awesome
[…] MCM (B | T) gives us examples of using aggregates to get information from an XML query plan in T-SQL Tuesday #16: Using Aggregate Functions in XML […]