Microsoft Business Intelligence (Data Tools)|SQL

As we are well aware that SQL Server supports an enhancement to the T-SQL syntax that enables normal relational queries to output their result set as XML, using any of these four approaches:
1) FOR XML RAW
2) FOR XML AUTO
3) FOR XML EXPLICIT
4) FOR XML PATH

The first three of these options were introduced with the very first XML support in SQL Server 2000. We’ll start with these options and then cover later XML enhancements added in SQL Server 2008, which includes the fourth option (FOR XML PATH).

Most common usable XML command is FOR XML PATH. Mostly, everyone has been worked with this command.

To understand the each FOR XML command, we will amuse that we have a Employee Master table as given below:

Employee Master
Emp Id	Employee Name	EmployeeDept	EmployeeRatePerDay
E0001	Ryan Arjun	Database	240
E0002	Tony Towery	Testing	230
E0003	Lucy Gray	IT	250
E0004	Will Smith	Database	245
E0005	Chao Milk	Testing	225
E0006	Chris Gyal	IT	210
E0007	Bill Gray	Database	190
E0008	Red Bill	Testing	210
E0009	Tom Ramsay	IT	200

FOR XML RAW Command
FOR XML RAW produces attribute-based XML. FOR XML RAW command essentially creates a flat representation of the data in which each row returned becomes an element and the returned columns become the attributes of each element. This is also does not interpret joins in any special way.

Example - Using FOR XML RAW to produce flat, attribute-based XML

SELECT [Emp Id] as EmpId

,[Employee Name] as EmployeeName

,[Dept Id] as Deptid

,[EmployeeRatePerDay]

FROM [dbo].[EmployeeMaster]

order by [Employee Name]

FOR XML RAW

After execution of the above query, we will get the query results as a hyperlink and after clicking on that hyperlink, we will see the output rendered as properly formatted XML in a color-coded window that supports expanding and collapsing nodes as shown below -

FOR XML AUTO- This is also produces attribute-based XML (By default Nature) but its output is hierarchical rather than flat—that is, it can create nested results based on the tables in the query’s join clause.

Example-

SELECT

[Emp Id] as EmpId

,[Employee Name] as EmployeeName

,[Dept Id] as Deptid

,[EmployeeRatePerDay]

FROM

EmployeeMaster

order by

EmpId

FOR XML AUTO

FOR XML EXPLICIT
This is the most powerful and flexible of the three original FOR XML options. FOR XML EXPLICIT is more complex than its predecessors. To use FOR XML EXPLICIT, we must have two SELECT statements with a UNION.

I'm trying to cover it now for completeness, but recommend using the simpler FOR XML PATH feature.

SELECT

1 as Tag, -- Tag this result set as level 1

Null as parent -- Level 1 has no parent

,[Dept Id] as [EMP!1!DeptId] -- level 1 value

,NULL as [EMP2!2!EmployeeId] -- level 2 value

,NULL as [EMP2!2!EployeeName] -- level 2 value

,Null as [EMP2!2!EmployeeRatePerDay]-- level 2 value

FROM EmployeeMaster EMP

where ([Dept Id]='D001' and [Emp Id]='E0001')

UNION ALL

SELECT

2 -- Tag this resultset as level 2

,1 -- Link to parent at level 1

,[Dept Id] as DeptId

,[Emp Id] as EmployeeId

,[Employee Name] as EmployeeName

,EmployeeRatePerDay

FROM EmployeeMaster

where ([Dept Id]='D001' )

order by [EMP!1!DeptId]

FOR XML EXPLICIT

</EMP>

Additional FOR XML Features

The TYPE Option- Using the TYPE option, FOR XML can output to an xml data type from a SELECT statement, which in turn allows you to nest the results of SELECT…FOR XML into another SELECT statement.

SELECT [Emp Id]

SELECT [Employee Name]

,[Dept Id]

,[EmployeeRatePerDay]

FROM [EmployeeMaster]

where [Emp Id]=emp.[Emp Id]

FOR XML AUTO, TYPE

) Details

FROM [dbo].[EmployeeMaster] emp

Using the TYPE option with FOR XML AUTO to cast a subquery result set as an xml data type as shown below-

Emp Id	Details
E0001	<EmployeeMaster Employee_x0020_Name="Ryan Arjun" Dept_x0020_Id="D001" EmployeeRatePerDay="240.00" />
E0002	<EmployeeMaster Employee_x0020_Name="Tony Towery" Dept_x0020_Id="D001" EmployeeRatePerDay="230.00" />
E0003	<EmployeeMaster Employee_x0020_Name="Lucy Gray" Dept_x0020_Id="D001" EmployeeRatePerDay="250.00" />
E0004	<EmployeeMaster Employee_x0020_Name="Will Smith" Dept_x0020_Id="D002" EmployeeRatePerDay="245.00" />
E0005	<EmployeeMaster Employee_x0020_Name="Chao Milk" Dept_x0020_Id="D002" EmployeeRatePerDay="225.00" />
E0006	<EmployeeMaster Employee_x0020_Name="Chris Gyal" Dept_x0020_Id="D002" EmployeeRatePerDay="210.00" />
E0007	<EmployeeMaster Employee_x0020_Name="Bill Gray" Dept_x0020_Id="D003" EmployeeRatePerDay="190.00" />
E0008	<EmployeeMaster Employee_x0020_Name="Red Bill" Dept_x0020_Id="D003" EmployeeRatePerDay="210.00" />
E0009	<EmployeeMaster Employee_x0020_Name="Tom Ramsay" Dept_x0020_Id="D003" EmployeeRatePerDay="200.00" />

FOR XML PATH - As we already knew, FOR XML PATH gives a fine control over the generated XML much like FOR XML EXPLICIT does, but is much simpler to use.

SELECT [Emp Id] as [@EmployeeID]

,[Employee Name] as [Employee/Name]

,[Dept Id] as [Employee/Dept]

,[EmployeeRatePerDay] as [Employee/RatePerDay]

FROM [dbo].[EmployeeMaster]

FOR XML PATH('Employee')

<Name>Ryan Arjun</Name>

</Employee>

<Name>Tony Towery</Name>

</Employee>

Notice that the EMP ID column is rendered as an attribute. This is because it was aliased as @EmployeeID, and the @-symbol in XPath means “attribute.” Now, using FOR XML PATH to shape XML output for a two-level hierarchy as shown below

SELECT [Emp Id] as [@EmployeeID],

(

Select

[Employee Name] as [@EmployeeName]

,[Dept Id] as [@EmployeeDept]

,[EmployeeRatePerDay] as [@EmployeeRatePerDay]

from [dbo].[EmployeeMaster]

where [Emp Id]= emp.[Emp Id]

FOR XML PATH('Employee'), TYPE)

FROM [dbo].[EmployeeMaster] emp

FOR XML PATH('Employee')

</Employee>

</Employee>

</Employee>

</Employee>

</Employee>

</Employee>

</Employee>

</Employee>

</Employee>

Microsoft Business Intelligence (Data Tools)

Saturday, June 6, 2015

SQL - FOR XML Commands

No comments:

Post a Comment

Popular Posts