SQL – Make XML from a SQL database

Some time ago I have decided to go a little deeper into SQL. And by some time ago I probably mean some years. Anyhow, in this article I will present the ways I know to make XML out of SQL tables.

sqlserver

I am using the following db here. It contains of two tables, the one about the football players info and the other for some info, concerning their cities. Something like this:

tables

So, these are the three methods I know:

Using Elements RAW

This method returns RAW XML from the tables. Pretty much something like this:

In order to get this one, we need to call the following:

Depending on the presence or absence of “elements” and “root” the results may vary.

Using Elements AUTO

Elements auto is a bit more clever. It takes a look at the joined tables and gives us XML, which is based on it. Thus this query:

returns the following:

Using Elements PATH

Element Path is the most sophisticated way of turning sql tables to xml. In it, we may define our own elements in the XML (e.g. the path) like this:

Thus, we would get a new element called Names, which would have the properties FirstName and LastName like this:

Pretty much that’s all. If you want more, take a look at my GitHub account here.

About

VBA Developer