A few blog posts ago, I showed how easy it is to generate JSON, JavaScript or arrays with the FOR XML clause. But in the examples, the output is transformed to text. Selecting this output is the same as a normal select. But what if you really want to output XML? Does this work the same? Let’s create the same example table again, and rebuild it to output XML.

Creating resources
Just as in my last blog post, let’s use the airline example:

There is a DISTINCT or GROUP BY needed in this query, to return only the unique records. As we’ll see later on, DISTINCT doesn’t work for this query, so a GROUP BY is used. This results in a dataset where every Airline code is joined to every airport they fly to:

Generating XML in CROSS APPLY
But in the previous post, we got a string as output. But now, we want to return XML instead of a normal resultset. But what happens if we put the FOR XML clause in this query? If we do so, it throws an exception:

Apparently column 1 of the CROSS APPLY result has no column name. Looking at the result that comes from the CROSS APPLY, the column name is generated automatically:

Giving a column alias to the column in the select doesn’t work. But you can also provide a column list to a cross apply:

SELECT AT.Airline, CONVERT(XML, CA.Airport_XML) AS Airport_XML
FROM #TMP_AirlineTickets AT
CROSS APPLY
(
SELECT Departure_Airport_Code AS DAC
FROM #TMP_AirlineTickets A
AND A.Airline = AT.Airline
FOR XML PATH('')
) CA (Airport_XML)
GROUP BY AT.Airline, CA.Airport_XML

By adding a column list to the CROSS APPLY, we can now use the column name Airport_XML in the outer select. By doing that, we get the airline name, and the departure airport code list:

Returning XML
So when you want to generate XML in a query, it’s possible to do that with a CROSS APPLY. It’s just a easy as writing a normal query, but it makes you a lot more flexible when you need to output XML from SQL Server. With this, there is no need for CLR’s or external applications to generate the XML you need, but you can just do it with T-SQL.

Every new project brings its own challenges and pitfalls. New projects also give you a chance to be creative in finding solutions, and try new things. This keeps it interesting, and allows you to learn new things. And sometimes, the things you avoided for all these years, turn out to be your best option.

A missing feature in SQL Server
For a project, we needed to generate JSON or JavaScript from T-SQL. Even though the whole world seems to use JSON at this point, there is no feature to generate JSON from T-SQL or SQL Server. There are more than enough people who would like such a feature (just look at the Connect item made for this). Maybe they’ll put it in the vNext of SQL Server, who knows. But at this moment, we need to generate the JSON ourselves.

Generating the data array
First of all, we need to generate an array or list. The problem with this, is the number of rows. For example, if you only have 2 rows per product, you can join the table onto itself, and concatenate the columns like that. But what if you have more rows per product, or you don’t know how many rows per product are returned?

One of the ways to resolve this, is by using the FOR XML clause. You can use this in a CROSS APPLY, to create an array/list of data, grouped by another column.

So let’s create an example, by using airlines. When you fly to another state, country or continent, you’ll book a flight with an airline. Those airlines have a name, a departure airport code, and of course a ticket price. Let’s put this data in a table:

The next step is to use this in a query that creates an XML column for all airlines in the table:

SELECT
AT.Airline,
(SELECT
CONCAT(',', SUB.Departure_Airport_Code)
FROM #TMP_AirlineTickets SUB
WHERE SUB.Airline = AT.Airline
ORDER BY SUB.Departure_Airport_Code ASC
FOR XML PATH('')
) AS Array_Airport_Codes
FROM #TMP_AirlineTickets AT
WHERE 1 = 1
GROUP BY AT.Airline

The result is an array of airport departure codes:

As you can see, this creates the array/list for all airlines in the table. With STUFF I replace the first character, because I don’t want my result to start with a comma. This can be done with a RIGHT clause, that USES LEN – 1 to strip of the first character on the left, but this looks better in my opinion:

SELECT
AT.Airline,
STUFF((SELECT
CONCAT(',', SUB.Departure_Airport_Code)
FROM #TMP_AirlineTickets SUB
WHERE SUB.Airline = AT.Airline
ORDER BY SUB.Departure_Airport_Code ASC
FOR XML PATH('')
), 1, 1, '') AS Array_Airport_Codes
FROM #TMP_AirlineTickets AT
WHERE 1 = 1
GROUP BY AT.Airline

The result looks the same, except the first comma is gone:

One of the best things when creating demos like this for a blog post, is that you can make it as unrealistic as you want. What I mean by that, is I’m making it easy to generate XML, in order to keep the examples clear and understandable. But what if you want to use this in the real world? You’re probably seeing datasets that aren’t as easy to use as the demo above. The strings you want to concatenate are filled with special characters, etc. Luckily there’s a solution for that. Rob Farley (Blog | @rob_farley) wrote a really nice blog post about that.

So now we have our array/list ready, but how do we convert this to JSON or JavaScript? The main difference (sorry if I insult my developer friends with this statement), is that in JavaScript everything needs to be enclosed in double quotes (“). This isn’t needed if you create JSON. In the following example, I’ll create a JSON object. But because just airport codes isn’t what we need, I’ll adjust the query so it’ll add the prices to the object as well. This is done by an additional CONCAT in the XML select, which concatenates the airport code and the price into 1 string:

The GROUP BY can be replaced by a DISTINCT. But either one of those is needed to make sure you’re only returning unique records:

By parsing the second result in the query in an online tool, you can see that the result is valid JSON:

By adding some double quotes in the script above, you can easily generate JavaScript from T-SQL. But if you just want to use the FOR XML to generate arrays, this will also work perfectly.

Why would you want to use this?
Generating JSON or JavaScript might come in handy for webdevelopers, that can get data from a database, and use that directly on their website. By using the FOR XML clause to generate JSON, you make sure your queries are flexible enough to generate valid JSON, even if your dataset grows. Generating arrays like this can also come in handy for developers that need data from their databases. Instead of creating an array in code, they can now use the array that is returned from the database.

So even though I tried to work my way around using FOR XML, it turned out to be the best option thinkable!