Category: T-SQL

Last time I promised to start looking at performance and XML indexes. I’ll get there but I realised I needed a decent test data set and couldn’t easily lay my hands on one that matched my needs so had to start building one myself. Part way through the process I figured I may as well squeeze an extra post out of the process and blog the creation process.

In my experience creating XML is a less common task than reading it with one semi-exception (that being the old trick of using FOR XML PATH(”) to concatenate strings) but it can be an important one. The biggest piece of XML I’ve built was for a government mandated return, it was the only XML I built on that job but a sizeable proportion of the entire department’s work culminated in it. The switch from a set of Excel macros which took over eight hours to run to something that could rebuilt in minutes had a huge impact on our efficiency, particularly when there would be multiple rounds of validation failures against initial attempts at submission.

There are a few variants when it comes to converting queries into XML but in my opinion the only one you really need to know is the XML PATH approach, the others being either fiddly or not providing enough control.

Because all the cool kids are using it and I had a copy available from his recent live steamed courses I’m going to use a copy of the Stack Overflow database that Brent Ozar maintains. You can get your own copy here. I’m using the 2013 version but if you want to follow along any variant should work here. I’ve used TOP to limit results returned so that they’re short enough to place in the Blog.

How Do I Build XML In SQL Server?

At the simplest level we just drop FOR XML PATH after a query, we give a value to the path which becomes the element each row is nested in and give a root value that wraps the whole output.

SELECT TOP 10
	U.Id AS 'UserId',
	U.DisplayName,
	U.UpVotes,
	U.DownVotes
FROM dbo.Users AS U
FOR XML PATH('User'), ROOT('Users');

Which gets something like this (this is going to be a post where the SQL notebook version and its embedded query results is going to be really helpful, you can find these here):

<Users>
  <User>
    <UserId>17</UserId>
    <DisplayName>Nick Berardi</DisplayName>
    <UpVotes>884</UpVotes>
    <DownVotes>215</DownVotes>
  </User>
  <User>
    <UserId>19</UserId>
    <DisplayName>Mads Kristiansen</DisplayName>
    <UpVotes>36</UpVotes>
    <DownVotes>12</DownVotes>
  </User>
</Users>

How do I Create Attributes In XML With SQL Server?

Adding Attributes is easy, we just give the relevant column a name commencing with an @ symbol.

SELECT TOP 2
	U.Id AS '@UserId',
	U.DisplayName,
	U.UpVotes,
	U.DownVotes
FROM dbo.Users AS U
FOR XML PATH('User'), ROOT('Users');

And now the XML looks like this:

<Users>
  <User UserId="17">
    <DisplayName>Nick Berardi</DisplayName>
    <UpVotes>884</UpVotes>
    <DownVotes>215</DownVotes>
  </User>
  <User UserId="19">
    <DisplayName>Mads Kristiansen</DisplayName>
    <UpVotes>36</UpVotes>
    <DownVotes>12</DownVotes>
  </User>
</Users>

How Do I Nest Elements In XML With SQL Server?

If we want to start grouping elements under parent tags then we just name the columns with the needed path. If two consecutive fields have a common element to the path they get grouped together. Here we nest up and down votes in a votes element.

SELECT TOP 2
	U.Id AS '@UserId',
	U.DisplayName,
	U.UpVotes AS 'Votes/UpVotes',
	U.DownVotes AS 'Votes/DownVotes'
FROM dbo.Users AS U
FOR XML PATH('User'), ROOT('Users');

And now we have this:

<Users>
  <User UserId="17">
    <DisplayName>Nick Berardi</DisplayName>
    <Votes>
      <UpVotes>884</UpVotes>
      <DownVotes>215</DownVotes>
    </Votes>
  </User>
  <User UserId="19">
    <DisplayName>Mads Kristiansen</DisplayName>
    <Votes>
      <UpVotes>36</UpVotes>
      <DownVotes>12</DownVotes>
    </Votes>
  </User>
</Users>

How Do I Add Repeating Sub Elements to XML With SQL Server

Things get a little messier when it comes to adding repeating elements but the approach is relatively simple. An XML element can contain element’s itself so all we need to do is push in a sub query that generates that XML. We’ve already seen how to do this above.
I’m going to get some details on badges for each user, limited and grouped in this case to keep the results presentable.

Note in the sub query we specify the TYPE directive. This tells the server to keep the results as XML, if we don’t do this then the server assumes you want the results formatted as text, escapes all the special characters and makes a big mess.

SELECT TOP 2
U.Id AS '@UserId',
U.DisplayName,
U.UpVotes AS 'Votes/UpVotes',
U.DownVotes AS 'Votes/DownVotes',
(	SELECT TOP 2
		B.Name, MIN(B.Date) AS FirstAwarded FROM dbo.Badges AS B
	WHERE B.UserId = U.Id
	GROUP BY B.Name
	FOR XML PATH ('Badge'), TYPE
) AS 'Badges'
FROM dbo.Users AS U
FOR XML PATH('User'), ROOT('Users');

And now we get this:

<Users>
  <User UserId="17">
    <DisplayName>Nick Berardi</DisplayName>
    <Votes>
      <UpVotes>884</UpVotes>
      <DownVotes>215</DownVotes>
    </Votes>
    <Badges>
      <Badge>
        <Name>.net</Name>
        <FirstAwarded>2009-02-10T05:11:54.607</FirstAwarded>
      </Badge>
      <Badge>
        <Name>asp.net</Name>
        <FirstAwarded>2009-04-15T03:45:30.720</FirstAwarded>
      </Badge>
    </Badges>
  </User>
  <User UserId="19">
    <DisplayName>Mads Kristiansen</DisplayName>
    <Votes>
      <UpVotes>36</UpVotes>
      <DownVotes>12</DownVotes>
    </Votes>
    <Badges>
      <Badge>
        <Name>Beta</Name>
        <FirstAwarded>2008-09-16T00:00:00</FirstAwarded>
      </Badge>
      <Badge>
        <Name>Citizen Patrol</Name>
        <FirstAwarded>2008-09-15T08:55:05.157</FirstAwarded>
      </Badge>
    </Badges>
  </User>
</Users>

I’m not a fan of sub queries directly in the SELECT statement of a query and possibly a little too keen on using APPLY so I’d probably re-write the above like the following.
The ‘*’ on the field name is a place holder for nothing so we don’t force in another element level. Equally we could exclude the Root of ‘users’ and use that as the field name and get the same result.

SELECT TOP 2
U.Id AS '@UserId',
U.DisplayName,
U.UpVotes AS 'Votes/UpVotes',
U.DownVotes AS 'Votes/DownVotes',
B.x AS '*'
FROM dbo.Users AS U
OUTER APPLY
(
	SELECT B.Name, MIN(B.Date) AS FirstAwarded FROM dbo.Badges AS B
	WHERE B.UserId = U.Id
	GROUP BY B.Name
	FOR XML PATH ('Badge'), ROOT('Badges'), TYPE
) B(x)
FOR XML PATH('User'), ROOT('Users')
;

And that should cover 99% of needs when it comes to building XML. You might need to take the sub query concept a little further, potentially needing sub queries in your sub queries but with the above you can put together pretty much anything.

Querying XML In SQL Server Series

• Part 1 – Introduction
• Part 2 – Simple Paths and Attributes With the Value Method
• Part 3 – Handling Repeating Regions With the Nodes Method
• Part 4 – Filtering With The Exist Method
• Part 5 – Introduction to the Query Method and XQuery
• Part 6 – Sequencing Elements and Finding Positions
• Part 7 – Creating XML

Cheat Sheet

Posts in SQL Notebook form

Back in Part 3 of this series we covered what I consider to be the essentials of querying XML. Between the values and nodes methods you can break up XML into rows and extract the values into columns and from there you can do anything you could with data lifted from a standard table in the database.

However there’s a lot that you can do that might perform better or look neater if a bit more of the query work was handled inside the XML and a bit less on the SQL side of the equation.

You can see what Microsoft has to say about the exist method here but you can probably guess a lot from the name and what we’ve seen of the values and nodes methods so far. Exist runs a query and returns a bit field, giving a 1 if the query produces a result and 0 if the result is empty.

Let’s start out as we always do with our sample XML.

CREATE TABLE #MyXML (MyXML XML NOT NULL);

INSERT INTO #MyXML(MyXML)
VALUES
(
'<?xml version="1.0" encoding="UTF-8"?>
<items termsofuse="https://boardgamegeek.com/xmlapi/termsofuse">
   <item type="boardgame" id="91514">
      <thumbnail>https://cf.geekdo-images.com/R_VAhiLCzl5RXKwSluvEbg__thumb/img/zpzD1TJLfuNEjuTjpPbN1y1mpss=/fit-in/200x150/filters:strip_icc()/pic3271388.jpg</thumbnail>
      <image>https://cf.geekdo-images.com/R_VAhiLCzl5RXKwSluvEbg__original/img/hVn3HtD2_5vKNjhEBkL1qFB4FaU=/0x0/filters:format(jpeg)/pic3271388.jpg</image>
      <name type="primary" sortindex="1" value="Rhino Hero" />
      <name type="alternate" sortindex="1" value="Rino Ercolino" />
      <name type="alternate" sortindex="1" value="Super Rhino!" />
      <name type="alternate" sortindex="1" value="Super Rino!" />
      <description>
        Super Rhino! presents players with an incredibly heroic &amp;ndash; and regrettably heavy &amp;ndash; rhinoceros who is eager to climb a tall building and leap other tall buildings in a single bound. 
      </description>
      <yearpublished value="2011" />
      <minplayers value="2" />
      <maxplayers value="5" />
      <poll name="suggested_numplayers" title="User Suggested Number of Players" totalvotes="61">
         <results numplayers="1">
            <result value="Best" numvotes="0" />
            <result value="Recommended" numvotes="7" />
            <result value="Not Recommended" numvotes="29" />
         </results>
         <results numplayers="2">
            <result value="Best" numvotes="10" />
            <result value="Recommended" numvotes="40" />
            <result value="Not Recommended" numvotes="3" />
         </results>
         <results numplayers="3">
            <result value="Best" numvotes="44" />
            <result value="Recommended" numvotes="11" />
            <result value="Not Recommended" numvotes="0" />
         </results>
         <results numplayers="4">
            <result value="Best" numvotes="18" />
            <result value="Recommended" numvotes="30" />
            <result value="Not Recommended" numvotes="1" />
         </results>
         <results numplayers="5">
            <result value="Best" numvotes="5" />
            <result value="Recommended" numvotes="25" />
            <result value="Not Recommended" numvotes="13" />
         </results>
         <results numplayers="5+">
            <result value="Best" numvotes="0" />
            <result value="Recommended" numvotes="3" />
            <result value="Not Recommended" numvotes="24" />
         </results>
      </poll>
      <playingtime value="15" />
      <minplaytime value="5" />
      <maxplaytime value="15" />
      <minage value="5" />
   </item>
</items>'
);

SELECT * FROM #MyXML;

Imagine we want to find all the results elements that had a result with more than 15 votes. We might write it like this.

SELECT
R2.ResultsXML.value('../@numplayers','varchar(50)') AS NumPlayers
FROM #MyXML AS X
CROSS APPLY X.MyXML.nodes('/items/item/poll/results/result') AS R2(ResultsXML)
WHERE R2.ResultsXML.value('./@numvotes','int') > 15;

But if you run that you’ll notice we get an extra row because Numplayers =4 has two different results that meet this condition. We could of course sweep this issue under the carpet with DISTINCT but that’s not going to perform well with larger data sets. We might also be able to produce something more complicated using EXISTS like this.

SELECT
R.ResultXML.query('.') AS ResultLevelXML,
R.ResultXML.value('./@numplayers','varchar(50)') AS NumPlayers
FROM #MyXML AS X
CROSS APPLY X.MyXML.nodes('/items/item/poll/results') AS R(ResultXML)
WHERE EXISTS (
SELECT 1
FROM R.ResultXML.nodes('./result') AS R2(ResultsXML)
WHERE R2.ResultsXML.value('./@numvotes','int') > 15
);

That’s a lot more code to type out, it’s harder to read and a quick peek at the query plans for the above two makes me think that second query is going to get ugly if you threw more data at it.

How can I use the exist method to filter results from XML in SQL Server?

We can use exist to test a piece of xml to see if a matching element exists inside it, any time a result is returned we get a 1 and if nothing is found we get a 0. This can then be used to test within the WHERE clause of a TSQL statement like the one below.

SELECT
R2.ResultsXML.value('./@numplayers','varchar(50)') AS NumPlayers
FROM #MyXML AS X
CROSS APPLY X.MyXML.nodes('/items/item/poll/results') AS R2(ResultsXML)
WHERE ResultsXML.exist('./result[@numvotes > 15]') = 1;

This looks a lot neater to me both in code view and as a query plan. Hopefully I’ll get the chance to do some performance tests in a later post in this series once I’ve built a suitable data set to test against. Anecdotally I can confirm I’ve had no issues using this approach in a work environment.

The exist method doesn’t have to be used in the where clause we can use it to give us useful columns in our results too.

How can I use the exist method to test for existence of XML elements in SQL Server?

The exist method can be added to a column definition to show if a particular element or value exists within the XML without filtering it out. Consider this example that lists all results and gives us a flag to show if any of the result level elements had 30 or more votes.

SELECT
R2.ResultsXML.value('./@numplayers','varchar(50)') AS NumPlayers,
ResultsXML.exist('./result[@numvotes > 30]') AS HasThirtyVotes
FROM #MyXML AS X
CROSS APPLY X.MyXML.nodes('/items/item/poll/results') AS R2(ResultsXML);

This approach can be really useful if there are optional elements to a piece of XML. I’ve used this where the XML represents a process performed in distinct steps and there’s a need to see which of those steps of the process have been started without needing any more detail. Exist allows you to quickly dip in and see what’s there and what isn’t without going any further than that.

Why Am I Getting Errors When Using The Node and Exist Methods Together?

A word of warning, while testing the above code I received syntax errors in SSMS like the one in the image below.
The good news is this appears to be an issue with SSMS rather than the code, it runs just fine but it is a little off putting to be told there’s a problem.

That’s all for this post, next time round we’re going to delve deeper into what more we can do inside XML with the query method.

Querying XML In SQL Server Series

• Part 1 – Introduction
• Part 2 – Simple Paths and Attributes With the Value Method
• Part 3 – Handling Repeating Regions With the Nodes Method
• Part 4 – Filtering With The Exist Method
• Part 5 – Introduction to the Query Method and XQuery
• Part 6 – Sequencing Elements and Finding Positions
• Part 7 – Creating XML

Cheat Sheet

Posts in SQL Notebook form