What is FOR XML in SQL Server and how to use it?

What is for xml?

If we use SELECT query it returns as a rowset but there it is optional to retrieve formal results of SQL query as XML by specifying FOR XML. FOR XML is used in top level queries. In a FOR XML clause, you specify one of these modes:

• RAW
• AUTO
• EXPLICIT
• PATH

You can include a FOR XML clause only in SELECT statements, if those statements define the outer, or top-level, query. However, you can also include the clause in INSERT, UPDATE, and DELETE statements that are part of a sub-query.

How to use for xml?

[ FOR { BROWSE | <XML> } ]  <XML> ::=  XML       {         { RAW [ ('ElementName') ] | AUTO }           [              <CommonDirectives>              [ , { XMLDATA | XMLSCHEMA [ ('TargetNameSpaceURI') ]} ]              [ , ELEMENTS [ XSINIL | ABSENT ]           ]        | EXPLICIT           [              <CommonDirectives>              [ , XMLDATA ]           ]        | PATH [ ('ElementName') ]           [              <CommonDirectives>              [ , ELEMENTS [ XSINIL | ABSENT ] ]          ]       }      <CommonDirectives> ::=      [ , BINARY BASE64 ]     [ , TYPE ]     [ , ROOT [ ('RootName') ] ]

RAW Mode

The RAW mode generates a single XML element for each row in the result set returned by the

SELECT e.EmployeeID, c.FirstName, c.MiddleName, c.LastName FROM HumanResources.Employee e INNER JOIN Person.Contact c    ON c.ContactID = e.ContactID WHERE c.FirstName = 'XYZ' FOR XML RAW;

Output:

<row EmployeeID="123" FirstName="XYZ" LastName="XYZ" /> <row EmployeeID="125" FirstName="XYZ" MiddleName="AA" LastName="ABC" />

Now in output we can see there “row” we can rename it by adding ('Employee') after RAW in the query

The AUTO Mode

The AUTO mode in a FOR XML clause is slightly different from the RAW mode in the way that it generates the XML result set. The AUTOmode generates the XML by using heuristics based on how the SELECT statement is defined. The best way to understand how this works is to look at an example. The following SELECT statement, as in the previous examples

SELECT Employee.EmployeeID, ContactInfo.FirstName, ContactInfo.MiddleName, ContactInfo.LastName FROM HumanResources.Employee AS Employee INNER JOIN Person.Contact AS ContactInfo ON ContactInfo.ContactID = Employee.ContactID WHERE ContactInfo.FirstName = 'XYZ' FOR XML AUTO, ROOT ('Employees');

Output:

</Employees>     <Employees>         <Employee EmployeeID="123">         <ContactInfo FirstName="XYZ" LastName="XYZ" />     </Employee>     <Employee EmployeeID="125">         <ContactInfo FirstName="XYZ" MiddleName="AA" LastName="ABC" />     </Employee> </Employees>

The EXPLICIT Mode

The EXPLICIT mode provides very specific control over your XML, but this mode is much more complex to use than the RAW or AUTOmodes. To use this mode, you must build your SELECT statements in such as way as to define the XML hierarchy and structure. In addition, you must create a SELECT statement for each level of that hierarchy and use UNION ALL clauses to join those statements.

SELECT 1 AS Tag, NULL AS Parent, e.EmployeeID AS [Employee!1!EmployeeID], NULL AS [ContactInfo!2!FirstName!ELEMENT], NULL AS [ContactInfo!2!MiddleName!ELEMENT], NULL AS [ContactInfo!2!LastName!ELEMENT] FROM HumanResources.Employee e INNER JOIN Person.Contact c ON c.ContactID = e.ContactID WHERE c.FirstName = 'XYZ' UNION ALL SELECT 2 AS Tag, 1 AS Parent, e.EmployeeID, c.FirstName, c.MiddleName, c.LastName FROM HumanResources.Employee e INNER JOIN Person.Contact c ON e.ContactID = c.ContactID WHERE c.FirstName = 'XYZ' ORDER BY [Employee!1!EmployeeID], [ContactInfo!2!FirstName!ELEMENT] FOR XML EXPLICIT;

Output:

<Employee EmployeeID="123">     <ContactInfo>         <FirstName>XYZ</FirstName>         <LastName>XYZ</LastName>     </ContactInfo> </Employee> <Employee EmployeeID="125">     <ContactInfo>         <FirstName>XYZ</FirstName>         <MiddleName>AA</MiddleName>         <LastName>ABC</LastName>     </ContactInfo> </Employee>

The PATH Mode

When you specify the PATH mode in the FOR XML clause, column names (or their aliases) are treated as XPath expressions that determine how the data values will be mapped to the XML result set. By default, XML elements are defined based on column names. You can modify the default behavior by using the at (@) symbol to define attributes or the forward slash (/) to define the hierarchy. Let’s take a look at a few examples to demonstrate how all this works.

SELECT e.EmployeeID, c.FirstName, c.MiddleName, c.LastName FROM HumanResources.Employee AS e INNER JOIN Person.Contact AS c ON c.ContactID = e.ContactID WHERE c.FirstName = 'XYZ' FOR XML PATH ('Employee'), ROOT ('Employees');

<Employees>     <Employee>         <EmployeeID>123</EmployeeID>         <FirstName>XYZ</FirstName>         <LastName>XYZ</LastName>     </Employee>     <Employee>         <EmployeeID>125</EmployeeID>         <FirstName>XYZ</FirstName>         <MiddleName>AA</MiddleName>         <LastName>ABC</LastName>      </Employee> </Employees>