Jignesh Raiyani

Generate XML Forms or XML Data Type Documents in SQL Server

January 27, 2020 by

In this article, we’ll show how to create an XML data type document from a relational table utilizing different T-SQL ways. For example, for data migration, information from the SQL Server database can be exported and imported using XML in another framework. XML is a standard way of extracting, storing and manipulating data. One aspect of working with the XML data type is creating XML from relational information, which is done using the FOR XML provision in SQL Server:

Table to XML document

An XML data type can be utilized in an alternate manner in SQL Server. This article will explain some fundamental uses of XML. FOR XML keyword is a provision that can be added with the SELECT query statement to prepare the XML document in the required forms in a query result. The outcome is a Unicode string containing components and qualities controlled by the different modes determined in the provision with FOR XML.

The four modes are:

  • RAW
  • AUTO
  • EXPLICIT
  • PATH

The table below shows sample data for further clarification of the various methodologies:

sample table

AUTO mode

AUTO mode is one of the approaches to converts over to the XML elements from the rows of the table. Element names of the XML document corresponding to the column alias or column name of the SELECT query.

For example, the query result has been produced with a single XML document for the above sample data. The query result had 5 rows in a result set, which is changed over to a solitary cell result-set. This methodology is utilized in a single-valued function to return multiple rows in a single return variable.

Table to XML data type

Here, AUTO produces header names utilizing table names.

EXPLICIT mode

For more control over the styling of the subsequent XML, SQL developers can use EXPLICIT mode. With EXPLICIT mode, a query result will be changed over to a single XML data type document with customized XML labels and values. The EXPLICIT mode will make a different label set for each row in a record. Even, the EXPLICIT mode allows designing the XML format as the user wants to set the position of the elements in the XML data type format in SQL Query.

Below is an example of using EXPLICIT mode:

SQL query:

EXPLICIT mode result:

EXPLICIT mode

RAW mode

The most common and often-used mode by developers for making XML within FOR XML is the RAW mode. Basically, RAW mode outputs an element named “row” from the result set for each row in the SELECT articulation, and the cell value of the row will be bounded as an attribute of the element. In a RAW mode, every column will be treated as one element in the XML, and columns of those lines will be attributed to the same element. RAW mode is used to change each row in the result set into an XML component.

For ROW mode, various querying models with different outputs explained in the examples below:

Example 1

Using RAW mode, the header of each element is defined as a row element in the XML result and the columns of each row are being attributed inside the row element in the XML data type result.

Example 2

If the user wants to include all elements in a single tag, then the ROOT option with the RAW mode can be used and each element’s header name can be defined by the user by adding a name with the RAW() in the XML data type result.

Example 3

In the XML RAW ROOT, the header can be designed with a user-defined name with the ROOT(). As it can be seen in the example, the <root> element is replaced by the <UserDetails>

Example 4

Using the ELEMENT directive with the RAW mode, the user can make row-element structure-based XML. Each cell of the row is being extracted with XML elements inside the parent element of the row as <row>. Basically, we used the ELEMENTS keyword in the above query to display the columns as elements in the output XML.

PATH mode

The best mode for creating XML with more control over the format is the PATH mode. Using the PATH, the user can compose more straightforward SELECT and supply XPATH articulations for the column to assign a chain of command. Use FOR XML PATH with subqueries to get the proper XML data type document with the element-attribute chain:

Conclusion

Using a server query result to achieve a different XML format is straightforward. The query result will be extracted in XML by including a FOR XML catchphrase at the end of the SELECT statement. As discussed, we have a number of alternatives to structure or arrange the outcome with columns to attributes or XML nodes with the XML data type.

Jignesh Raiyani
14,284 Views