In this article, we will explore how to create XML data type documents from relational tables using various T-SQL methods in SQL Server. XML is a widely used standard for extracting, storing, and manipulating data, and being able to convert relational data into XML format can be useful for tasks such as data migration or integration with other systems.

FOR XML Provision

The FOR XML provision in SQL Server allows us to generate XML documents in different formats based on the query result. By adding the FOR XML keyword to a SELECT statement, we can control the structure and content of the resulting XML document. There are four modes available:

• RAW: Outputs an element named “row” for each row in the result set, with column values as attributes.
• AUTO: Converts each row in the result set into XML elements, with element names based on column aliases or names.
• EXPLICIT: Provides more control over the XML structure by allowing customized XML labels and values.
• PATH: Enables the use of XPath expressions to assign hierarchy to the XML elements.

Examples

Let’s take a look at some examples to better understand how each mode works:

AUTO Mode

In AUTO mode, each row in the result set is converted into XML elements, with element names based on column aliases or names. Here’s an example:

SELECT user_id, fname, lname, email_
FROM user_details
FOR XML AUTO;

The above query will produce a single XML document with elements for each row in the result set.

EXPLICIT Mode

EXPLICIT mode provides more control over the XML structure by allowing customized XML labels and values. Here’s an example:

SELECT 1 as tag, NULL as parent, user_id as [user!1!ID], NULL AS [fname!2!fname], NULL AS [lname!3!lname], NULL AS [email!4!email]
FROM user_details
UNION ALL
SELECT 3 as tag, 1 as parent, user_id as [user!1!ID], fname AS [fname!2!fname], lname AS [lname!3!lname], email_ AS [email!4!email]
FROM user_details
UNION ALL
SELECT 2 as tag, 1 as parent, user_id as [user!1!ID], fname AS [fname!2!fname], lname AS [lname!3!lname], email_ AS [email!4!email]
FROM user_details
ORDER BY 3, 1
FOR XML EXPLICIT;

In the above example, we define custom XML labels and values for each row in the result set.

RAW Mode

RAW mode is commonly used to convert each row in the result set into an XML component. Here are a few examples:

SELECT user_id, fname, lname, email_
FROM user_details
FOR XML RAW;

The above query will output an element named “row” for each row in the result set, with column values as attributes.

SELECT user_id, fname, lname, email_
FROM user_details
FOR XML RAW('user'), ROOT;

In this example, we define the root element as “user” and each row is enclosed within this element.

SELECT user_id, fname, lname, email_
FROM user_details
FOR XML RAW('user'), ROOT('UserDetails');

In this example, we define the root element as “UserDetails” and each row is enclosed within this element.

PATH Mode

PATH mode allows us to compose more straightforward SELECT statements and supply XPath expressions to assign hierarchy to the XML elements. Here’s an example:

SELECT user_id, fname, lname, email_
FROM user_details
WHERE email_ = 'mihail0@adventure-works.com'
FOR XML PATH;

In the above example, we use the WHERE clause to filter the result set and generate an XML document with the specified email address.

Conclusion

In this article, we have explored different ways to create XML data type documents from relational tables in SQL Server. By utilizing the FOR XML provision, we can control the structure and content of the resulting XML document. Whether it’s for data migration or integration purposes, being able to convert relational data into XML format can be a valuable skill for SQL Server developers.