Example: Specify XSINIL with the ELEMENTS Directive
Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance
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>