Transact SQL – Returning data as XML

Background

SQL Server has a nice support for exposing results of SQL Select Statements as XML.

Let us take a first look.

 

SQL

Scenario

Let us start off with a simple SQL Select statement.

We will get a couple of columns from the sys.databases view.

Code

Basic SQL

SQL


SELECT  
		  [name]
			= rtrim(tblSD.[name])
					
		, [owner] 
				= SUSER_SNAME(tblSD.owner_sid)
					
		, [collation] 
			= tblSD.[collation_name]
		
from    sys.databases tblSD

order by
	rtrim(tblSD.[name]) asc

Output

SQL – Returns Lone XML Node

SQL


; with cteDatbase
as
(
	SELECT 
	          [name]
		    = rtrim(tblSD.[name])
					
		, [owner] 
		   = SUSER_SNAME(tblSD.owner_sid)
					
		, [collation] 
		   = tblSD.[collation_name]
		
	from    sys.databases tblSD

)
, cteDatabaseXML([node])
as
(
	select * 

	from   cteDatbase cteDB

	order by 
		
		cteDB.[name]

	for xml 
		  PATH('database')
		, root ('list')

)
select 
	cteDBXML.[node]

from   cteDatabaseXML cteDBXML


Output – XML


<?xml version="1.0" encoding="UTF-8"?>
<list>
   <database>
      <name>AdminDB</name>
      <owner>LAB\dadeniji</owner>
      <collation>SQL_Latin1_General_CP1_CI_AS</collation>
   </database>
   <database>
      <name>DBLab</name>
      <owner>LAB\dadeniji</owner>
      <collation>SQL_Latin1_General_CP1_CI_AS</collation>
   </database>
   <database>
      <name>HRDB</name>
      <owner>sa</owner>
      <collation>SQL_Latin1_General_CP1_CI_AS</collation>
   </database>
   <database>
      <name>DWConfiguration</name>
      <owner>LAB\dadeniji</owner>
      <collation>Latin1_General_100_CI_AS_KS_WS</collation>
   </database>
   <database>
      <name>DWDiagnostics</name>
      <owner>LAB\dadeniji</owner>
   </database>
   <database>
      <name>DWQueue</name>
      <owner>LAB\dadeniji</owner>
   </database>
   <database>
      <name>master</name>
      <owner>sa</owner>
      <collation>SQL_Latin1_General_CP1_CI_AS</collation>
   </database>
   <database>
      <name>model</name>
      <owner>sa</owner>
      <collation>SQL_Latin1_General_CP1_CI_AS</collation>
   </database>
   <database>
      <name>msdb</name>
      <owner>sa</owner>
      <collation>SQL_Latin1_General_CP1_CI_AS</collation>
   </database>
   <database>
      <name>tempdb</name>
      <owner>sa</owner>
      <collation>SQL_Latin1_General_CP1_CI_AS</collation>
   </database>
   <database>
      <name>WideWorldImportersFull</name>
      <owner>sa</owner>
      <collation>Latin1_General_100_CI_AS</collation>
   </database>
   <database>
      <name>WideWorldImportersStandard</name>
      <owner>LAB\dadeniji</owner>
      <collation>Latin1_General_100_CI_AS</collation>
   </database>
</list>

Explanation

  1. Hopefully to make things easier to follow we have created a couple of Common Table Expressions
  2. Common Table Expression ( CTE/1 ):-
    • Plain Query using simple SQL
  3. Common Table Expression ( CTE/2 ):-
    • Converts to xml using
      • Syntax
        • for xml
          PATH(‘database’)
          , root (‘list’)
    • Meaning
      • For xml requests XML
      • PATH(‘database’)
        • Element Name for each record
      • root
        • Our root node should be named list
    • cte name
      • cteDatabaseXML([node])
      • Means table name is cteDatabaseXML
      • and, column name is node
  4. Return our XML node

Summary

There are a few good things about XML.

  1. We can lighten our data load and not have to worry about formatting within our backend
  2. We can have some independence with query support as it does not matter to us the original columns etc
  3. We will have the front-end developers worry about that

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s