Skip to content

MS SQL

XML Data Processing in MS SQL Server

XML is a special data type introduced in SQL Server 2005. Its purpose is to store data in a specific format so that the data can be queried / analyzed easily whenever required.

Optional Info: XML has now become a standard way of data sharing & data transport across various heterogeneous platforms.

XML data processing in SQL Server or any RDBMS for that matter is almost a requirement for any modern day application. For representing relation data in SQL Server in XML format we can use FOR XML clause in one of the following 4 ways:

  1. AUTO
  2. RAW
  3. EXPLICIT
  4. PATH

AUTO – FOR XML AUTO option is used for general purpose nesting of relational data and it has little control and flexibility on the generated output as shown below.

Basic usage of FOR XML AUTO

Basic usage of FOR XML AUTO

Again, this output can be made element centric by introducing ELEMENT clause as shown below:

Making FOR XML AUTO output element centric

Making FOR XML AUTO output element-centric

RAW – FOR XML RAW produces a much straight forward output by enclosing all the constituent fields as attribute under a single tag called <Row>.  But the default <Row> tag can be replaced with any user defined value as shown below:

Output generated by FOR XML RAW

Output generated by FOR XML RAW

EXPLICIT: FOR XML EXPLICIT option is the most restrictive with two fixed metadata fields called TAG and PARENT. This option helps to generate a hierarchical structure based on a primary key foreign key relationship between two entities as shown below.

FOR XML EXPLICIT showing hierarchical data

FOR XML EXPLICIT showing hierarchical data

PATH: The basic usage of FOR XML PATH gives us nodes segregated inside <row> tags. But if we use FOR XML PATH(‘’) then the outer <row> tags are removed. Also we can add one root element with this clause. All these three cases have been shown below.

Example of FOR XML PATH with Root tag

Example of FOR XML PATH with Root tag

FOR XML PATH without using Root tag in below image:

FOR XML PATH without using Root tag

FOR XML PATH without using Root tag

Use of basic FOR XML PATH option in below image:

Use of basic FOR XML PATH option

Use of basic FOR XML PATH option

 Code Download for Practice

The second part of this article where I’ll talk about Parsing of single column multivalued data from XML and Parsing from multiple columns of data will be live next week. You can download the required source code for both the articles from this Dropbox URL.

Be Sociable, Share!
    The following two tabs change content below.

    Basant Singh

    Basant is a talented software developer who is very engaged in web & cloud platforms on a daily basis. He is a contributor to codingthis.com reporting on various news and troubleshooting on various platforms. Basant is very strong in SQL and a great asset to the CodingThis community.

    Latest posts by Basant Singh (see all)