How to query SQL data into XML

Problem: How to convert SQL data to XML.

Solution:

FOR XML (SQL Server)

Generally used in the select statement to convert the rows of a column we need to specify the FOR xml clause at the end of the statement and the mode you would like to choose for representation.

Here are few available modes in FOR XML clause, you specify one of these modes. AS the name of the modes their functionality is also similar:

·         RAW - Gives crude form of output

·         AUTO – generates output based on heuristics

·         EXPLICIT – explicit control on the output but complicated out of all the modes.

·         PATH– returns values as parent child elements.

This FOR XML Clause can be used in sub- queries as well and INSERT, UPDATE and DELETE Statements.

Example:

In This article I am going to show you how we can use each of these modes using some base queries, let’s dive into the stuff.

This is how the table structure looks like in a normal query:

 SELECT col1,

                col2,

                col3

FROM [DB].[dbo].[TableA]

WHERE col2 = 4

OUTPUT:

1. XML RAW

SELECT col1,

                col2,

                col3

FROM [DB].[dbo].[TableA] XML_RAW

WHERE col2 = 4

FOR XML RAW 

OUTPUT:

2. XML AUTO 

SELECT TOP 1 col1,

                col2,

                col3

FROM [DB].[dbo].[TableA] XML_AUTO

WHERE col2 = 4

FOR XML  AUTO, TYPE, ELEMENTS 

OUTPUT:

3. XML EXPLCIT

SELECT 1 AS tag,

                NULL AS Parent,

                col1 AS [Columns!1!Col1],

                col2 AS [Columns!1!Col2],

                col3 AS [Columns!1!Col3]

FROM [DB].[dbo].[TableA] XML_EXPLICIT

WHERE col2 = 4

FOR XML EXPLICIT

OUTPUT:

 

4. XML PATH

SELECT col1,

                col2,

                col3

FROM [DB].[dbo].[TableA] XML_PATH

WHERE col2 = 4

FOR XML PATH

OUTPUT: