SQL FOR XML RAW

Use of Raw mode with FOR XML in SQL Server is to transform every row in the result set into an XML element. For this SQL FOR XML RAW mode example, we are using the New Employee table present in the SQL Test. The below screenshot show you the SQL Server tables inside that database.

SQL FOR XML RAW Example 1

In this example, we 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 on our database.

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

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

Notice that each row in the New Employee table mapped with <row> element, and columns became the attributes.

SQL FOR XML RAW Example 2

In this FOR XML RAW example, let us see how to add the columns values as the child elements (rather than attributes). To accomplish this, we have to use the ELEMENTS keyword along with the FOR XML RAW.

-- SQL Server FOR XML RAW Example

SELECT  [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [NewEmployee]
  FOR XML RAW, ELEMENTS;

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

FOR XML RAW Example 3

If you see the earlier For XML RAW image, every row separated by the default <row> element. Let’s see how you override the default row with the custom element.

-- SQL Server FOR XML RAW Example

SELECT  [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [NewEmployee]
  FOR XML RAW('EmployeeDetails'), ELEMENTS;

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

FOR XML RAW Example 4

The SQL Server FOR XML RAW lets you create a new root element that will wrap all the other elements inside it. To do the same, use the ROOT keyword along with the FOR XML RAW.

-- SQL Server FOR XML RAW Example

SELECT  [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [NewEmployee]
  FOR XML RAW('Employee'), ROOT, ELEMENTS;

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 Server FOR XML RAW Example

SELECT  [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [NewEmployee]
  FOR XML RAW('Employee'), 
          ROOT('EmployeeDetails'), ELEMENTS;

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

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 Server FOR XML RAW Example

SELECT  [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [NewEmployee]
  FOR XML RAW('Employee'), 
          ROOT('EmployeeDetails'), ELEMENTS XSINIL;

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

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 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;

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 a custom one.

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

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