XML Generation Made Easy: How to Use SSMS for Quick and Accurate Results

XML (eXtensible Markup Language) is one of the most used file formats tailored for storing and exchanging data between different systems. It gained popularity due to its simple, hierarchical structure and high compatibility with various platforms.

When we work with XML, it’s crucial to have the best tools that help us simplify the generation process of an XML file and ensure accuracy.

Before engaging in the knowledge process, we have to be aware that XML files don’t carry any info about how data must be displayed (for this purpose we have HTML).

XML file structure

At the beginning of this article, I mentioned something about the hierarchical structure of an XML file (now I guess that you re-read the article introduction and looked for where I talked about this, right? 😁)

Its structure is also known as a tree structure because it starts from a root element (at the top) and branches from the root to the child elements.

https://way2tutorial.com

The visual representation of the tree structure displayed above is as follows:

<employee>
  <emp_info id="10">
    <first_name>Lucas</first_name>
    <middle_name>Theodore</middle_name>
    <last_name>Jones</last_name>

    <contact_info>

      <company_info>
        <comp_name>IBM</comp_name>
        <comp_location>
          <street>Tower-1, Infocity</street>
          <city>US</city>
          <phone>000-478-1414</phone>
        </comp_location>
        <designation>BI Developer</designation>
      </company_info>

      <phone>000-987-4745</phone>
      <email_address>email@myemail.com</email_address>

    </contact_info>
  <emp_info>  
</employee>

SQL to XML

Being widely used for exchanging, XML files must be flawless. In SQL Server, you can generate XML files by using the FOR XML clause.

Yes, you read that right! All you need to do is specify this clause at the end of your SELECT statement together with a mode (raw, auto, explicit, or path)

USE AdventureWorks2012
GO
SELECT Cust.CustomerID,
       OrderHeader.CustomerID,
       OrderHeader.SalesOrderID,
       OrderHeader.Status
FROM Sales.Customer Cust 
INNER JOIN Sales.SalesOrderHeader OrderHeader
ON Cust.CustomerID = OrderHeader.CustomerID
FOR XML AUTO;

In a FOR XML clause, you specify one of the following modes:

• RAW — this mode generates a single <row> element per row in the rowset returned by the query
• AUTO — it generates nesting in the resulting XML by using heuristics based on the way the SELECT statement is specified.
• EXPLICIT — it gives you way more control over the shape of the XML. You can add attributes and elements to end up having the desired result.

Example:

SELECT 1    as Tag,
       NULL as Parent,
       E.BusinessEntityID as [Employee!1!EmpID],
       NULL       as [Name!2!FName],
       NULL       as [Name!2!LName]
FROM   HumanResources.Employee AS E
INNER JOIN Person.Person AS P
ON  E.BusinessEntityID = P.BusinessEntityID
UNION ALL
SELECT 2 as Tag,
       1 as Parent,
       E.BusinessEntityID,
       FirstName,
       LastName
FROM   HumanResources.Employee AS E
INNER JOIN Person.Person AS P
ON  E.BusinessEntityID = P.BusinessEntityID
ORDER BY [Employee!1!EmpID],[Name!2!FName]
FOR XML EXPLICIT;

Result:

• PATH — together with the nested FOR XML query capability offers the flexibility of the EXPLICIT in a simple manner.

It’s showtime 😃

For simplicity and accessibility, I used the AdventureWorks database (you can download it from here).

Let’s assume that our colleagues from the HR department want a report about the employers who were hired between 2010 and 2012. Being a certain period we think about building that report on an XML file.

Together with our colleague from the HR department, we established what insights they want to have into their report, so we ended up having this select statement:

select e.BusinessEntityID, concat(p.FirstName,' ', p.lastname) as FullName, 
e.[jobtitle] as JobTitle, e.BirthDate, e.gender as Gender,
e.hiredate as HireDate, d.Name as Field, d.groupName as Department 
from Person.Person as p
 join HumanResources.Employee as e
  on p.BusinessEntityID = e.BusinessEntityID
 join HumanResources.EmployeeDepartmentHistory as dh
  on e.BusinessEntityID = dh.BusinessEntityID
 join HumanResources.Department as d
  on dh.DepartmentID = d.DepartmentID
where year(e.hiredate) between 2010 and 2012 

Now we have what we need, what’s left is to extract those data in the XML format…so the next script will do the trick

SELECT
    e.BusinessEntityID AS 'ID',
    CONCAT(p.FirstName, ' ', p.LastName) AS 'FullName',
    e.JobTitle AS 'JobTitle',
    e.BirthDate AS 'BirthDate',
    e.Gender AS 'Gender',
    e.HireDate AS 'HireDate',
    d.Name AS 'Field',
    d.GroupName AS 'Department'
FROM
    Person.Person AS p
JOIN
    HumanResources.Employee AS e ON p.BusinessEntityID = e.BusinessEntityID
JOIN
    HumanResources.EmployeeDepartmentHistory AS dh ON e.BusinessEntityID = dh.BusinessEntityID
JOIN
    HumanResources.Department AS d ON dh.DepartmentID = d.DepartmentID
WHERE
    YEAR(e.HireDate) BETWEEN 2010 AND 2012
FOR XML RAW, ROOT('Employers'), ELEMENTS

The result will look like the one below:

and if we open it, we will have (I used the raw mode):

In conclusion

XML is a versatile file format that can be useful in various scenarios such as data integration, building or consuming web services, report generation, analysis, writing configuration files, and exporting data.

Please remember that the decision to use XML format for a specific task depends on the specific requirements, integration needs, and capabilities of the systems involved.

Expand your knowledge, unlock new opportunities, and stay ahead in this fast-evolving data landscape. Visit my article collection and embark on an exciting journey of discovery today. Happy reading!