Edit

Example: Specify XSINIL with the ELEMENTS Directive

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance SQL database in Microsoft Fabric

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

Example

Note

In Azure SQL Database and SQL database in Microsoft Fabric, the USE statement isn't available. Instead, connect directly to intended database and run the rest of the code sample.

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>

Related content

  • Last updated on