Example: Specifying the ELEMENT Directive

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

following:

XML

The query remains the same, except you add the

directive in the column names.

Therefore, instead of attributes, the

and

element children are added to the

element. Because the

column doesn’t specify the

directive,

is added as the attribute of the

element.

This is the partial result.

XML

ELEMENT
<FName>
<LName>
<Name>
Employee!1!EmpID
ELEMENT
EmpID
<Employee>
<Employee
EmpID
=.
>
<Name>
<FName>.
</FName>
<LName>.
</LName>
</Name>
</Employee>
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;