Naveen's Weblog

Bridge to future

MSSQL Output in XML using For XML

Posted by codingsense on December 31, 2008



As we know nowadays XML is extensively used for any transfer between media so we should know how to get output of any user data in XML form. In MSSQL 2005 life has been made easier with For XML Keyword. In this section we will see how to get different types of XML output in MSSQL.

Open Management studio, and as i explain each keyword, execute the queries and check the output for better understanding.

First let us create 2 tables which will be refferred in the below section

Employee Table :

CREATE TABLE [dbo].[Employee](
	[ID] [int] NOT NULL Primary Key,
	[FirstName] [varchar](50) NOT NULL,
	[LastName] [varchar](50) NOT NULL,
	[DOB] [datetime] NULL)
GO

EmployeeSalary Table:

CREATE TABLE [dbo].[EmpSalary](
	[ID] int NULL,	
	[SalariedMonth] varchar(4) NULL
)

Insert Data in the tables:

Insert into Employee select 1,'First1','Last1','08/04/1983'
Insert into EmployeeSalary select 1,'Apr'
Insert into EmployeeSalary select 1,'May'
Insert into EmployeeSalary select 1,'Jun'

Insert into Employee select 2,'First2','Last2','08/04/1984'
Insert into EmployeeSalary select 2,'Apr'
Insert into EmployeeSalary select 2,'Jun'

Insert into Employee select 3,'First3','Last3',null
Insert into EmployeeSalary select 3,'May'
Insert into EmployeeSalary select 3,'Jun'

Raw: – Gives as the element name for each row

select FirstName,LastName from Employee for xml raw

Auto: – Gives Table Name as the element name for each Row

select FirstName,LastName from Employee for xml AUTO

Elements:
Without Elements keyword a row with all the columns will be converted in one XML node element.
With Elements Keyword a row will be converted into Parent Node and each column will be converted into a child node.

select FirstName,LastName from Employee for xml AUTO , elements
XSINIL: While selecting columns if the column value is null then it will not be displayed in the XML. If we want to display the column even it has Null Values, then we have to use XSINIL. By using this, if the column has values then the respective value will be displayed or xsi:nil="true" will be displayed for the NULL values.
select FirstName,LastName, DOB from Employee for xml path, ELEMENTS XSINIL

'@':
@ keyword has to be used in 'As Clause' of the column if we want the Column to be considered as an Tag attribute and display in the parent tag.

select ID as '@ID',FirstName,LastName from Employee for xml path, ELEMENTS 

Root:
Root creates a root node with the name specified in the parameter
For eg:
root('Client') create .... on both ends of the XML generated

select * from Employee for xml path, ELEMENTS XSINIL, root('Client')

Joining 2 Tables:
Now if you want both the tables to be joined and the output has to be shown in a single XML file then there are many methods i will show you the two output which i have come accross.
Consider that for each employee we need to show the months in which he has earned the salary, then within the Employee node we will insert one more salary paid node and display the months.

1) Here a salary Tag will be displayed under each Employee tag and will contain the list of months in separate child nodes in which the employee has been paid the salary.

select Employee.ID as '@ID', FirstName,LastName,DOB , 
	(select SalariedMonth as 'Month' from employeesalary where 
	employee.id = employeesalary.id for xml path(''),Type) as 'Salary' 
from Employee
for xml path('Employee'),Elements XSINIL,root('Employees')

2) Here we use data() method to merge all the child entries into one row in the salary tag. This will give only one element named 'salary' under employee tag.

select Employee.ID as '@ID', FirstName,LastName,DOB , 
	(select SalariedMonth as 'data()' from employeesalary where 
	employee.id = employeesalary.id for xml path(''),Type) as 'Salary' 
from Employee
for xml path('Employee'),Elements XSINIL,root('Employees')

As you see in above example there is no much difference in both the methods except the 'AS clause' in subquery, but the output is pretty different.

Data():-
If for each parent record if there exists multiple children then we can use data() to display the parent and all children together in a single tag.
The 2nd example in previous section can be used to know about the Data() method.

There are lot many things to know, with the above things you can just begin smoothly.

More Information:
http://technet.microsoft.com/en-us/library/ms345137(SQL.90).aspx#forxml2k5_topic8

Happy Learning 🙂

Advertisements

Sorry, the comment form is closed at this time.

 
%d bloggers like this: