SQL FOR XML RAW

Use of Raw mode with FOR XML in SQL Server is to transform each and every row in the result set into XML element. In this article we will you list of examples that will explain the use of SQL FOR XML RAW mode. For this example, We are using the New Employee table present in the SQL Test, and below screenshot will show you the tables inside that database.

SQL FOR XML RAW Example 1

In this example we will show you the basic way of using FOR XML RAW mode. The simplest way of using Raw mode is to append FOR XML RAW after your Select Statement.

SQL CODE

-- SQL Server FOR XML RAW Example
USE [SQLTEST]
GO
SELECT  [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [NewEmployee]
  FOR XML RAW;

OUTPUT

As you can see from the above screenshot, query has generated the XML file. Please click on the hyperlink to see the XML file.

From the above screenshot you can see that, each row in New Employee table is mapped with <row> element, and columns became the attributes.

SQL FOR XML RAW Example 2

From the above screenshot, you can see all the columns are added as the attributes for the <row> element. In this example we will show you, How to add the columns values as the child elements (rather than attributes). In order to achieve the same, we have to use the ELEMENTS keyword along with the FOR XML RAW.

SQL CODE

-- SQL Server FOR XML RAW Example
USE [SQLTEST]
GO
SELECT  [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [NewEmployee]
  FOR XML RAW, ELEMENTS;

OUTPUT

Now you can see that the SQL For XML Raw has added the column values as the child elements

SQL FOR XML RAW Example 3

If you observe the above screenshot, every row is separated by the default <row> element. In this example, we will show you, How you override the default row with custom element.

SQL CODE

-- SQL Server FOR XML RAW Example
USE [SQLTEST]
GO
SELECT  [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [NewEmployee]
  FOR XML RAW('EmployeeDetails'), ELEMENTS;

OUTPUT

As you can see from the below screenshot, <row> element is replaced with the <EmployeeDetails>

SQL FOR XML RAW Example 4

The Sql Server FOR XML RAW allows you to create a new root element that will wrap all the other elements inside it. In order to achieve the same, we have to use the ROOT keyword along with the FOR XML RAW.

SQL CODE

-- SQL Server FOR XML RAW Example
USE [SQLTEST]
GO
SELECT  [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [NewEmployee]
  FOR XML RAW('Employee'), ROOT, ELEMENTS;

OUTPUT

and the XML file is:

SQL FOR XML RAW Example 5

If you observe the above screenshot, there is a <root> element as the parent level. In this example we will change this default element name.

SQL CODE

-- SQL Server FOR XML RAW Example
USE [SQLTEST]
GO
SELECT  [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [NewEmployee]
  FOR XML RAW('Employee'), 
          ROOT('EmployeeDetails'), ELEMENTS;

OUTPUT

Now you can see that the <root> element is replaced by the <EmployeeDetails>

SQL FOR XML RAW Example 6

If you observe all the above examples, the generated XML file is ignoring the elements with NULL values. This is the default behavior of the Sql Server For XML Raw but you can change this by adding XSINIL keyword.

SQL CODE

-- SQL Server FOR XML RAW Example
USE [SQLTEST]
GO
SELECT  [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [NewEmployee]
  FOR XML RAW('Employee'), 
          ROOT('EmployeeDetails'), ELEMENTS XSINIL;

OUTPUT

Now you can see that the XML file is showing the elements with nulls

SQL FOR XML RAW Example 7

By using the XMLSCHEMA keyword you can generate the XML file along with the schema. In this SQL FOR XML RAW example, we will show the same.

SQL CODE

-- SQL Server FOR XML RAW Example
USE [SQLTEST]
GO
SELECT  [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [NewEmployee]
  FOR XML RAW('Employee'), 
          ROOT('EmployeeDetails'), ELEMENTS XSINIL, XMLSCHEMA;

OUTPUT

You can see that the For XML Raw is returning the XML file along with the schema.

Let me change the Default target namespace to custom one.

SQL CODE

-- SQL Server FOR XML RAW Example
SELECT  [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [NewEmployee]
  FOR XML RAW('Employee'), 
          ROOT('EmployeeDetails'), ELEMENTS XSINIL, XMLSCHEMA('urn:tutorialgateway.org');

OUTPUT

You can see the namespace that we used in the above query

Thank You for Visiting Our Blog