Saturday, June 6, 2015

SQL - FOR XML Commands

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 -
<row EmpId="E0007" EmployeeName="Bill Gray" Deptid="D003" EmployeeRatePerDay="190.00" />
<row EmpId="E0005" EmployeeName="Chao Milk" Deptid="D002" EmployeeRatePerDay="225.00" />
<row EmpId="E0006" EmployeeName="Chris Gyal" Deptid="D002" EmployeeRatePerDay="210.00" />
<row EmpId="E0003" EmployeeName="Lucy Gray" Deptid="D001" EmployeeRatePerDay="250.00" />
<row EmpId="E0008" EmployeeName="Red Bill" Deptid="D003" EmployeeRatePerDay="210.00" />
<row EmpId="E0001" EmployeeName="Ryan Arjun" Deptid="D001" EmployeeRatePerDay="240.00" />
<row EmpId="E0009" EmployeeName="Tom Ramsay" Deptid="D003" EmployeeRatePerDay="200.00" />
<row EmpId="E0002" EmployeeName="Tony Towery" Deptid="D001" EmployeeRatePerDay="230.00" />
<row EmpId="E0004" EmployeeName="Will Smith" Deptid="D002" EmployeeRatePerDay="245.00" />

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



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.

<EmployeeMaster EmpId="E0001" EmployeeName="Ryan Arjun" Deptid="D001" EmployeeRatePerDay="240.00" />

<EmployeeMaster EmpId="E0002" EmployeeName="Tony Towery" Deptid="D001" EmployeeRatePerDay="230.00" />

<EmployeeMaster EmpId="E0003" EmployeeName="Lucy Gray" Deptid="D001" EmployeeRatePerDay="250.00" />

<EmployeeMaster EmpId="E0004" EmployeeName="Will Smith" Deptid="D002" EmployeeRatePerDay="245.00" />

<EmployeeMaster EmpId="E0005" EmployeeName="Chao Milk" Deptid="D002" EmployeeRatePerDay="225.00" />

<EmployeeMaster EmpId="E0006" EmployeeName="Chris Gyal" Deptid="D002" EmployeeRatePerDay="210.00" />

<EmployeeMaster EmpId="E0007" EmployeeName="Bill Gray" Deptid="D003" EmployeeRatePerDay="190.00" />

<EmployeeMaster EmpId="E0008" EmployeeName="Red Bill" Deptid="D003" EmployeeRatePerDay="210.00" />

<EmployeeMaster EmpId="E0009" EmployeeName="Tom Ramsay" Deptid="D003" EmployeeRatePerDay="200.00" />



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


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.



<EMP DeptId="D001">

  <EMP2 EmployeeId="E0001" EployeeName="Ryan Arjun" EmployeeRatePerDay="240.00" />

  <EMP2 EmployeeId="E0002" EployeeName="Tony Towery" EmployeeRatePerDay="230.00" />

  <EMP2 EmployeeId="E0003" EployeeName="Lucy Gray" EmployeeRatePerDay="250.00" />

</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')





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.

<Employee EmployeeID="E0001">

  <Employee>

    <Name>Ryan Arjun</Name>

    <Dept>D001</Dept>

    <RatePerDay>240.00</RatePerDay>

  </Employee>

</Employee>

<Employee EmployeeID="E0002">

  <Employee>

    <Name>Tony Towery</Name>

    <Dept>D001</Dept>

    <RatePerDay>230.00</RatePerDay>

  </Employee>

</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')

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.

<Employee EmployeeID="E0001">

  <Employee EmployeeName="Ryan Arjun" EmployeeDept="D001" EmployeeRatePerDay="240.00" />

</Employee>

<Employee EmployeeID="E0002">

  <Employee EmployeeName="Tony Towery" EmployeeDept="D001" EmployeeRatePerDay="230.00" />

</Employee>

<Employee EmployeeID="E0003">

  <Employee EmployeeName="Lucy Gray" EmployeeDept="D001" EmployeeRatePerDay="250.00" />

</Employee>

<Employee EmployeeID="E0004">

  <Employee EmployeeName="Will Smith" EmployeeDept="D002" EmployeeRatePerDay="245.00" />

</Employee>

<Employee EmployeeID="E0005">

  <Employee EmployeeName="Chao Milk" EmployeeDept="D002" EmployeeRatePerDay="225.00" />

</Employee>

<Employee EmployeeID="E0006">

  <Employee EmployeeName="Chris Gyal" EmployeeDept="D002" EmployeeRatePerDay="210.00" />

</Employee>

<Employee EmployeeID="E0007">

  <Employee EmployeeName="Bill Gray" EmployeeDept="D003" EmployeeRatePerDay="190.00" />

</Employee>

<Employee EmployeeID="E0008">

  <Employee EmployeeName="Red Bill" EmployeeDept="D003" EmployeeRatePerDay="210.00" />

</Employee>

<Employee EmployeeID="E0009">

  <Employee EmployeeName="Tom Ramsay" EmployeeDept="D003" EmployeeRatePerDay="200.00" />

</Employee>


No comments:

Post a Comment

Popular Posts