In SQL Server, the FOR XML
clause allows us to return the results of a query as an XML document. Simply by placing the FOR XML
clause at the end of the query will output the results in XML.
When we do this, we have the option of specifying RAW
, AUTO
, EXPLICIT
, or PATH
mode. These modes allow us to shape the resulting XML in different ways, and so the mode we choose will determine how the XML is generated.
Below are examples of using EXPLICIT
mode when generating XML from a SQL query.
The Original Query
First, here’s the original query without the FOR XML
clause:
SELECT
p.PetId,
p.PetName,
pt.PetType
FROM Pets p
INNER JOIN PetTypes pt ON p.PetTypeId = pt.PetTypeId
ORDER BY p.PetId, p.PetName;
Result:
PetId PetName PetType ----- ------- ------- 1 Fluffy Cat 2 Fetch Dog 3 Scratch Cat
That’s what the data looks like when it’s in a table. The following examples turn that into XML documents.
Basic FOR XML EXPLICIT
Example
The EXPLICIT
option of the FOR XML
clause is a lot more complex than the other options. The EXPLICIT
option requires that we are very specific with our query to ensure that the resulting XML document is shaped exactly how we want it.
With the other options, we can simply append FOR XML (option)
to the query, and the results will be output as XML. Sure, there are other things we can do to shape the XML, but it’s generally quite trivial to get a basic XML document from the query results. But using the EXPLICIT
option is a lot more involved. It involves modifying our query specifically for the required output.
Here’s an example of modifying the above query to use FOR XML EXPLICIT
:
SELECT
1 AS Tag,
NULL AS Parent,
p.PetId AS [Pet!1!PetId],
NULL AS [Details!2!PetName],
NULL AS [Details!2!PetType]
FROM Pets p
INNER JOIN PetTypes pt ON p.PetTypeId = pt.PetTypeId
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
p.PetId,
p.PetName,
pt.PetType
FROM Pets p
INNER JOIN PetTypes pt ON p.PetTypeId = pt.PetTypeId
ORDER BY [Pet!1!PetId], [Details!2!PetName]
FOR XML EXPLICIT;
As mentioned, it’s a lot more involved than just adding a line of code to the end of the query like with the other options.
When I run that with my command line tool, the XML is returned as one long string:
<Pet PetId="1"><Details PetName="Fluffy" PetType="Cat"/></Pet><Pet PetId="2"><Details PetName="Fetch" PetType="Dog"/></Pet><Pet PetId="3"><Details PetName="Scratch" PetType="Cat"/></Pet>
The same happens when I run it in my GUI (Azure Data Studio). The XML is returned in one column and one row. However, when running it in my GUI, clicking on the XML in the column opens a new tab with the XML formatted like this:
<Pet PetId="1"> <Details PetName="Fluffy" PetType="Cat" /> </Pet> <Pet PetId="2"> <Details PetName="Fetch" PetType="Dog" /> </Pet> <Pet PetId="3"> <Details PetName="Scratch" PetType="Cat" /> </Pet>
That format was explicitly specified by the way we formulated our query.
The Universal Table
If we remove the last line (i.e. the FOR XML EXPLICIT
) from our query, we get the universal table:
SELECT
1 AS Tag,
NULL AS Parent,
p.PetId AS [Pet!1!PetId],
NULL AS [Details!2!PetName],
NULL AS [Details!2!PetType]
FROM Pets p
INNER JOIN PetTypes pt ON p.PetTypeId = pt.PetTypeId
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
p.PetId,
p.PetName,
pt.PetType
FROM Pets p
INNER JOIN PetTypes pt ON p.PetTypeId = pt.PetTypeId
ORDER BY [Pet!1!PetId], [Details!2!PetName];
Result:
Tag Parent Pet!1!PetId Details!2!PetName Details!2!PetType --- ------ ----------- ----------------- ----------------- 1 null 1 null null 2 1 1 Fluffy Cat 1 null 2 null null 2 1 2 Fetch Dog 1 null 3 null null 2 1 3 Scratch Cat
The ORDER BY
clause is important when using the EXPLICIT
option. This sorts the results of our universal table so that each child ends up with the correct parent.
The naming convention of the columns determines the element names and the attribute names. For example, Pet!1!PetId
tells us that Pet
is the element name and PetId
is the attribute for that element.
Each Tag
value corresponds to one of the columns. Tags with a value of 1
correspond to the column that has !1!
in its name (in this case the Pet!1!PetId
column), while tags with a value of 2
correspond to the columns that have !2!
in their names (Details!2!PetName
and Details!2!PetType
).
We can see which columns are the top-level elements. The top-level elements are the ones with null
in the Parent
column. The other ones identify 1
as the Parent
. Each of these elements are added as a child to the previous <Pet>
element. That’s why the ORDER BY
clause is important. If we don’t order our results, we could end up with the wrong XML document.
Change Attributes to Elements
We can use the ELEMENT
directive to change an attribute into an element. When we use this directive, we need to apply it against the columns we want to be elements.
Example:
SELECT
1 AS Tag,
NULL AS Parent,
p.PetId AS [Pet!1!PetId],
NULL AS [Details!2!PetName!ELEMENT],
NULL AS [Details!2!PetType!ELEMENT]
FROM Pets p
INNER JOIN PetTypes pt ON p.PetTypeId = pt.PetTypeId
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
p.PetId,
p.PetName,
pt.PetType
FROM Pets p
INNER JOIN PetTypes pt ON p.PetTypeId = pt.PetTypeId
ORDER BY [Pet!1!PetId], [Details!2!PetName!ELEMENT]
FOR XML EXPLICIT;
Result:
<Pet PetId="1"> <Details> <PetName> Fluffy </PetName> <PetType> Cat </PetType> </Details> </Pet> <Pet PetId="2"> <Details> <PetName> Fetch </PetName> <PetType> Dog </PetType> </Details> </Pet> <Pet PetId="3"> <Details> <PetName> Scratch </PetName> <PetType> Cat </PetType> </Details> </Pet>
Here, I added !ELEMENT
to two columns, but left the PetId
column as an attribute.
Dealing with NULL Values
When using the ELEMENT
directive, if a column contains a NULL value, an element is not generated. If we want an element to be generated for NULL values, we can use the ELEMENTXSINIL
directive instead of ELEMENT
.
Suppose we run the following query:
SELECT
PetId,
PetName,
DOB
FROM Pets
ORDER BY PetId, PetName;
Result:
PetId PetName DOB ----- ------- ---------- 1 Fluffy 2020-11-20 2 Fetch null 3 Scratch 2018-10-01
We can see that the DOB
column for Fetch is null
.
Here’s what happens when we use FOR XML EXPLICIT
with the ELEMENT
directive:
SELECT
1 AS Tag,
NULL AS Parent,
PetId AS [Pet!1!PetId],
NULL AS [Details!2!PetName!ELEMENT],
NULL AS [Details!2!DOB!ELEMENT]
FROM Pets
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
PetId,
PetName,
DOB
FROM Pets
ORDER BY [Pet!1!PetId], [Details!2!PetName!ELEMENT]
FOR XML EXPLICIT;
Result:
<Pet PetId="1"> <Details> <PetName> Fluffy </PetName> <DOB> 2020-11-20 </DOB> </Details> </Pet> <Pet PetId="2"> <Details> <PetName> Fetch </PetName> </Details> </Pet> <Pet PetId="3"> <Details> <PetName> Scratch </PetName> <DOB> 2018-10-01 </DOB> </Details> </Pet>
We can see that there’s no DOB
element for Fetch. That’s how the ELEMENT
directive works.
If we want the element to be included even when its value is null
, we can use ELEMENTXSINIL
instead:
SELECT
1 AS Tag,
NULL AS Parent,
PetId AS [Pet!1!PetId],
NULL AS [Details!2!PetName!ELEMENT],
NULL AS [Details!2!DOB!ELEMENTXSINIL]
FROM Pets
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
PetId,
PetName,
DOB
FROM Pets
ORDER BY [Pet!1!PetId], [Details!2!PetName!ELEMENT]
FOR XML EXPLICIT;
Result:
<Pet xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" PetId="1"> <Details> <PetName> Fluffy </PetName> <DOB> 2020-11-20 </DOB> </Details> </Pet> <Pet xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" PetId="2"> <Details> <PetName> Fetch </PetName> <DOB xsi:nil="true" /> </Details> </Pet> <Pet xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" PetId="3"> <Details> <PetName> Scratch </PetName> <DOB> 2018-10-01 </DOB> </Details> </Pet>
This time we got an empty tag for Fetch’s date of birth.
More Information
See the Microsoft documentation for more examples and information on using EXPLICIT
mode with FOR XML
.