FOR XML PATH: Choose Attributes and Nested Elements With Aliases

FOR XML PATH interprets column aliases as directions for the output’s attributes and element hierarchy. I use three aliases to shape two rows. The source columns stay ordinary relational values.

The required person element has an identifier attribute, a name element and a nested city element. Those locations differ even though each begins as a scalar column. Explicit aliases keep the intended structure visible.

Build a small document from fixed rows

The input contains two rows with identifiers one and two. Their names are Blue and Green, and their cities are Paris and Rome. The values are deliberately short enough to inspect completely.

The alias @Id places the identifier on the person element as an attribute. Name creates an ordinary child element. Address/City creates a City element beneath an Address element.

PATH names each row element person, and ROOT adds the people wrapper. TYPE returns an XML value for further inspection. The inner ORDER BY specifies the sequence of the two people.

WITH Input AS
(
    SELECT Id, Name, City
    FROM (VALUES
        (1, 'Blue', 'Paris'),
        (2, 'Green', 'Rome')
    ) AS v(Id, Name, City)
), Document AS
(
    SELECT
        (SELECT Id AS [@Id], Name AS [Name], City AS [Address/City]
         FROM Input ORDER BY Id
         FOR XML PATH('person'), ROOT('people'), TYPE) AS ResultXml
)
SELECT ResultXml,
       ResultXml.value('count(/people/person)', 'int') AS PersonCount,
       ResultXml.value('(/people/person/@Id)[1]', 'int') AS FirstId,
       ResultXml.value('(/people/person/Address/City/text())[1]', 'varchar(10)') AS FirstCity
FROM Document;

Read the constructed hierarchy

The expected document contains two person elements under people. Each has its own Id attribute, Name child and Address containing City. The query returns the complete XML document.

PersonCount should be two, FirstId one and FirstCity Paris. Those diagnostics check several structural decisions without replacing the full document. A different alias can keep the same text while putting it in the wrong place.

For example, City without the Address/ prefix would create a direct child of person. Id without the @ prefix would create an element instead of an attribute. Those are interface changes rather than cosmetic formatting changes.

The order is supplied explicitly inside the serialization query. It is not inferred from the VALUES constructor’s displayed order. Keep a deterministic ordering rule when document sequence matters to the receiving application.

Keep the mapping easy to review

The identifier attribute has to come before the element columns in the SELECT list, or SQL Server raises an error. That also lets the code read from outer metadata to child content. More complicated mappings deserve small examples before they become a large serializer.

The aliases describe relative paths beneath the row element. They are not the names of source table columns or physical folders. Separating source names from output names makes the interface contract easier to change deliberately.

This example does not use unnamed expressions for text concatenation. It constructs a structured document with named fields. Escaped scalar concatenation is a different use case and needs its own rules.

No NULL values are included here because the focus is field placement. Omission or nil behavior would add another serialization decision. Test that separately if the interface permits missing fields.

Check structure rather than appearance alone

A readable XML display can still hide a misplaced attribute or element. The scalar path checks make selected locations explicit. A recipient should validate every required part of a larger contract.

The example uses no namespaces and no schema collection. Those requirements can add qualified names and validation rules. Do not assume the simple names used here cover a namespaced interface.

The query creates no permanent objects and changes no data. It establishes no performance or XML indexing claim. Its purpose is to show how a short SELECT list can express a precise output hierarchy.

Start with two rows like these and the alias rules will feel natural.

An alias is not a label, it is the output path of its value.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.