Sql Server

FOR XML PATH clause in SQL Server

by Deepak Talwar
September 15, 2022
0 Comments
7 minute read
269 Views
0 Likes

This article gives an overview of FOR XML PATH clause to get data in XML format with various examples

FOR XML clause can use to convert existing data to XML format. It can also be used to join or concatenate multiple columns into a single row. FOR XML Clause has below 4 Modes, that decide the shape of the XML – result. RAW AUTO EXPLICIT PATH

RAW MODE With FOR XML
The RAW mode generates a single XML element for each row in the result set returned by
the query. each <row> element maps to a row that is returned by the SELECT statement,
and each column, by default, is treated as an attribute of that element.

SELECT * FROM Member FOR XML AUTO

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 = 'Jhon'
FOR XML RAW;

Result- 
<row EmployeeID="4" FirstName="Jhon" LastName="Marrie" />
<row EmployeeID="168" FirstName="Jhon" MiddleName="M" LastName="Carter" />

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 = 'Jhon'
FOR XML RAW ('Employee');

Now the element associated with each row returned by the query will be named <Employee>,
rather than the default <row>:

Result-
<Employee EmployeeID="4" FirstName="Jhon" LastName="Marrie" />
<Employee EmployeeID="168" FirstName="Jhon" MiddleName="M" LastName="Carter" />

Can specify a root element be created to wrap all other elements

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 = 'Jhon'
FOR XML RAW ('Employee'), ROOT;

To create a root element,
add the ROOT keyword to your FOR XML clause:

Result-
<root>
<Employee EmployeeID="4" FirstName="Jhon" LastName="Marrie" />
<Employee EmployeeID="168" FirstName="Jhon" MiddleName="M" LastName="Carter" />
</root>

Can specify name of the root element:

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 = 'Jhon'
FOR XML RAW ('Employee'), ROOT ('Employees');

Result-
<Employees>
<Employee EmployeeID="4" FirstName="Jhon" LastName="Marrie" />
<Employee EmployeeID="168" FirstName="Jhon" MiddleName="M" LastName="Carter" />
</Employees>

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 = 'Jhon'
FOR XML RAW ('Employee'), ROOT ('Employees'), ELEMENTS;

can specify that the column values be added as child elements to the row element by including the ELEMENTS option in the FOR XML clause:

Result-
<Employees>
<Employee>
    <EmployeeID>22</EmployeeID>
    <FirstName>Jhon</FirstName>
    <LastName>Marrie</LastName>
</Employee>
<Employee>
    <EmployeeID>678</EmployeeID>
    <FirstName>Jhon</FirstName>
    <MiddleName>M</MiddleName>
    <LastName>Carter</LastName>
</Employee>
</Employees>

In all above situations, column having null values does not create any corresponding elements.no elements
are created for a column whose value is null. However, you can override this behavior by adding the XSINIL
keyword to the ELEMENTS option:

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 = 'Jhon'
FOR XML RAW ('Employee'), ROOT ('Employees'), ELEMENTS XSINIL; 

Result-
<Employees xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Employee>
    <EmployeeID>22</EmployeeID>
    <FirstName>Jhon</FirstName>
    <MiddleName xsi:nil="true" />
    <LastName>Marrie</LastName>
</Employee>
<Employee>
    <EmployeeID>678</EmployeeID>
    <FirstName>Jhon</FirstName>
    <MiddleName>M</MiddleName>
    <LastName>Carter</LastName>
</Employee>
</Employees>

Can add an inline W3C XML Schema (XSD) in the XML data.

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 = 'Jhon'
FOR XML RAW ('Employee'), ROOT ('Employees'), ELEMENTS XSINIL, XMLSCHEMA; 

<Employees xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<xsd:schema targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet1" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" elementFormDefault="qualified">
    <xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
    <xsd:element id="Employee"">
      <xsd:complexType>
        <xsd:sequence>
          <xsd:element id="Employee"ID" type="sqltypes:int" nillable="1" />
          <xsd:element id="FirstName"" nillable="1">
            <xsd:simpleType sqltypes:sqlTypeAlias="[AdventureWorks].[dbo].[Name]">
              <xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
                <xsd:maxLength value="50" />
              </xsd:restriction>
            </xsd:simpleType>
          </xsd:element>
          <xsd:element id="MiddleName"" nillable="1">
            <xsd:simpleType sqltypes:sqlTypeAlias="[AdventureWorks].[dbo].[Name]">
              <xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
                <xsd:maxLength value="50" />
              </xsd:restriction>
            </xsd:simpleType>
          </xsd:element>
          <xsd:element id="LastName"" nillable="1">
            <xsd:simpleType sqltypes:sqlTypeAlias="[AdventureWorks].[dbo].[Name]">
              <xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
                <xsd:maxLength value="50" />
              </xsd:restriction>
            </xsd:simpleType>
          </xsd:element>
        </xsd:sequence>
      </xsd:complexType>
    </xsd:element>
</xsd:schema>
<Employee xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1">
    <EmployeeID>22</EmployeeID>
    <FirstName>Jhon</FirstName>
    <MiddleName xsi:nil="true" />
    <LastName>Marrie</LastName>
</Employee>
<Employee xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1">
    <EmployeeID>678</EmployeeID>
    <FirstName>Jhon</FirstName>
    <MiddleName>M</MiddleName>
    <LastName>Carter</LastName>
</Employee>
</Employees>

Can also specify the name of the target namespace.

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 = 'Jhon'
FOR XML RAW ('Employee'), ROOT ('Employees'), ELEMENTS XSINIL, XMLSCHEMA ('urn:schema_example.com'); 

AUTO MODE With FOR XML
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 AUTO mode generates the XML by using heuristics based on how the SELECT statement is definedSELECT Employee.EmployeeID, ContactInfo.FirstName,

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

Result-
<Employees>
<Employee EmployeeID="22">
<ContactInfo FirstName="Jhon" LastName="Marrie" />
</Employee>
<Employee EmployeeID="678">
<ContactInfo FirstName="Jhon" MiddleName="M" LastName="Carter" />
</Employee>
</Employees>

Can Add Root and Elements

FOR XML AUTO, ROOT ('Employees'), ELEMENTS; 

Result-
<Employees>
<Employee>
    <EmployeeID>22</EmployeeID>
    <ContactInfo>
      <FirstName>Jhon</FirstName>
      <LastName>Marrie</LastName>
    </ContactInfo>
</Employee>
<Employee>
    <EmployeeID>678</EmployeeID>
    <ContactInfo>
      <FirstName>Jhon</FirstName>
      <MiddleName>M</MiddleName>
      <LastName>Carter</LastName>
    </ContactInfo>
</Employee>
</Employees>

EXPLICIT MODE With FOR XML
The EXPLICIT mode provides very specific control over your XML, but this mode is much more complex to use than the RAW or AUTO modes. 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.

PATH MODE With FOR XML
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.

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 = 'Jhon'
FOR XML PATH;

Result-
<row>
<EmployeeID>22</EmployeeID>
<FirstName>Rob</FirstName>
<LastName>Marrie</LastName>
</row>
<row>
<EmployeeID>678</EmployeeID>
<FirstName>Jhon</FirstName>
<MiddleName>M</MiddleName>
<LastName>Carter</LastName>
</row>

Can use More Options similar to RAW Options

Deepak Talwar

Technical Architect and Full Stack Developer with 18+ years of Professional Experience in Microsoft Technologies & PHP Platform. Hands on experience with C#, VB, ASP.NET, ASP.NET MVC, ASP.NET Core, ASP.NET Web API, Linq, ADO.NET, Entity Framework, Entity Framework Core, Sql Server, MYSQL, NoSql, Javascript, Angular, jQuery, AWS, Azure, React, Angular, Laravel, Codeingiter, Serenity, VBA, Cloud Computing, Microservices, Design Patterns, Software Architecture, Web Design and Development.

GETDATE vs GETUTCDATE vs SYSDATETIME vs CURRENT_TIMESTAMP in SQL Server

September 15, 2022

Explain, How to Pass Props, parameter and Functions to Child Components

September 26, 2022

Your Name *

E-mail *

Website

Comments *

FOR XML PATH clause in SQL Server

Deepak Talwar

GETDATE vs GETUTCDATE vs SYSDATETIME vs CURRENT_TIMESTAMP in SQL Server

The Most Powerful

Stay Connected

Popular

What is SQL Server

SQL | List Stored Procedure

How Cursors Works in Sql Server

FOR XML PATH clause in SQL Server

Sql Server | Find String | CHARINDEX, LIKE

Hot Categories

Tags

React Custom Hooks

React useMemo Hook

About Us

Instagram

Contact Info

FOR XML PATH clause in SQL Server

Popular Tags:

Share This Post:

Related Post

Stay Connected

Popular

Hot Categories

Tags

About Us

Instagram

Contact Info