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:
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.
Again, this output can be made element centric by introducing ELEMENT clause as shown below:
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:
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.
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.
FOR XML PATH without using Root tag in below image:
Use of basic FOR XML PATH option in below image:
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.