Skip to content

example-specifying-xsinil-with-the-elements-directive.md

Latest commit

 

History

History
77 lines (64 loc) · 1.82 KB

example-specifying-xsinil-with-the-elements-directive.md

File metadata and controls

77 lines (64 loc) · 1.82 KB
title Specify XSINIL with the ELEMENTS Directive
description View an example that specifies XSINIL with the ELEMENTS directive to generate element-centric XML from the query result.
author MikeRayMSFT
ms.author mikeray
ms.reviewer randolphwest
ms.date 05/05/2022
ms.service sql
ms.subservice xml
ms.topic how-to
ms.custom
ignite-2025
helpviewer_keywords
RAW mode, specifying XSINIL example

Example: Specify XSINIL with the ELEMENTS Directive

[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance FabricSQLDB]

The following query specifies the ELEMENTS directive to generate element-centric XML from the query result.

Example

USE AdventureWorks2022;
GO
SELECT ProductID, Name, Color
FROM Production.Product
FOR XML RAW, ELEMENTS;
GO

This is the partial result.

<row>
  <ProductID>1</ProductID>
  <Name>Adjustable Race</Name>
</row>
...
<row>
  <ProductID>317</ProductID>
  <Name>LL Crankarm</Name>
  <Color>Black</Color>
</row>

Because the Color column has null values for some products, the resulting XML won't generate the corresponding <Color> element. By adding the XSINIL directive with ELEMENTS, you can generate the <Color> element even for NULL color values in the result set.

USE AdventureWorks2022;
GO
SELECT ProductID, Name, Color
FROM Production.Product
FOR XML RAW, ELEMENTS XSINIL;

This is the partial result:

<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <ProductID>1</ProductID>
  <Name>Adjustable Race</Name>
  <Color xsi:nil="true" />
</row>
...
<row>
  <ProductID>317</ProductID>
  <Name>LL Crankarm</Name>
  <Color>Black</Color>
</row>

See also