Search code examples
sqlsql-serverfor-xml-path

What is the meaning of FOR XML PATH(' ')?

Whats is the meaning of 

FOR XML PATH('')?

What does it represent. As there is no root node to it.

Solution

  • There seems to be a bit of confusion about "Root" and "Element". In XML, the root is a single node at the top of the tree.

    If we specify FOR XML PATH with nothing following, then we get a set of XML elements called "Row". This is a default name.

    USE AdventureWorks2016CTP3;
GO

SELECT BusinessEntityID, FirstName, LastName
    FROM Person.Person
    WHERE BusinessEntityID BETWEEN 14500 AND 14502
    FOR XML PATH;

    enter image description here

    We can specify a different name for the enclosing XML element like this.

    SELECT BusinessEntityID, FirstName, LastName
    FROM Person.Person
    WHERE BusinessEntityID BETWEEN 14500 AND 14502
    FOR XML PATH('Person');

    enter image description here

    If we want the XML elements to be enclosed in a top level root element, we can specify it like this.

    SELECT BusinessEntityID, FirstName, LastName
    FROM Person.Person
    WHERE BusinessEntityID BETWEEN 14500 AND 14502
    FOR XML PATH('Person'),
    ROOT('People');

    enter image description here

    Specifying an empty string for the element name, causes it to leave out the enclosing XML elements. It seems a very peculiar thing to do. I can't imagine why anyone would want to do that.

    SELECT BusinessEntityID, FirstName, LastName
    FROM Person.Person
    WHERE BusinessEntityID BETWEEN 14500 AND 14502
    FOR XML PATH('');

    enter image description here

    However Books Online does say this is the correct behaviour. See Basic Syntax of the FOR XML Clause > https://msdn.microsoft.com/en-gb/library/ms190922.aspx > Arguments > PATH.

    If you provide an empty string (FOR XML PATH ('')), no wrapper element is generated.