example-specifying-the-element-directive.md

title	Example: Specifying the ELEMENT Directive | Microsoft Docs
ms.custom
ms.date	03/01/2017
ms.prod	sql-server-2016
ms.reviewer
ms.suite
ms.technology	dbe-xml
ms.tgt_pltfrm
ms.topic	article
helpviewer_keywords	ELEMENT directive
ms.assetid	80dd5d1f-fa90-4f97-a186-8fa3f460a7f3
caps.latest.revision	10
author	BYHAM
ms.author	rickbyh
manager	jhubbard

Example: Specifying the ELEMENT Directive

This retrieves employee information and generates element-centric XML as shown in the following:

<Employee EmpID=...>  
  <Name>  
    <FName>...</FName>  
    <LName>...</LName>  
  </Name>  
</Employee>  

The query remains the same, except you add the ELEMENT directive in the column names. Therefore, instead of attributes, the <FName> and <LName> element children are added to the <Name> element. Because the Employee!1!EmpID column does not specify the ELEMENT directive, EmpID is added as the attribute of the <Employee> element.

SELECT 1    as Tag,  
       NULL as Parent,  
       E.BusinessEntityID as [Employee!1!EmpID],  
       NULL       as [Name!2!FName!ELEMENT],  
       NULL       as [Name!2!LName!ELEMENT]  
FROM   HumanResources.Employee AS E  
INNER JOIN Person.Person AS P  
ON  E.BusinessEntityID = P.BusinessEntityID  
UNION ALL  
SELECT 2 as Tag,  
       1 as Parent,  
       E.BusinessEntityID,  
       FirstName,   
       LastName   
FROM   HumanResources.Employee AS E  
INNER JOIN Person.Person AS P  
ON  E.BusinessEntityID = P.BusinessEntityID  
ORDER BY [Employee!1!EmpID],[Name!2!FName!ELEMENT]  
FOR XML EXPLICIT;  

This is the partial result.

<Employee EmpID="1">

<Name>

<FName>Ken</FName>

<LName>Sánchez</LName>

</Name>

</Employee>

<Employee EmpID="2">

<Name>

<FName>Terri</FName>

<LName>Duffy</LName>

</Name>

</Employee>

...

See Also

Use EXPLICIT Mode with FOR XML