FOR XML PATH clause in SQL Server
FOR XML PATH clause in SQL Server and
clause In each row, each column value is embedded into and clauses clauses with each row. Previously, we did not specify the ELEMENTS directive, so it returns the default value. Suppose we want to define root element as Customers. Each row data should be embedded between and . In the following query, we added the parameters as following. FOR XML PATH(‘CustomerData’) for each row data ROOT(‘Customers’) for the root tag 123456789101112 SELECT [CustomerID], [CustomerName], [CustomerCategoryName], [PrimaryContact], [AlternateContact], [PhoneNumber], [FaxNumber], [BuyingGroupName], [WebsiteURL], [DeliveryMethod]FROM [WideWorldImporters].[Website].[Customers]WHERE CustomerID < 3 FOR XML PATH('CustomerData'), ROOT('Customers'); meta data tag. To do so, we can add an alias in the Select statement and use an alias with the @ parameter. In the following query, We use @CustomerID in the select statement to show the CustomerID as well in the row tag. 123456789101112 SELECT [CustomerID] as "@CustomerID", [CustomerName], [CustomerCategoryName], [PrimaryContact], [AlternateContact], [PhoneNumber], [FaxNumber], [BuyingGroupName], [WebsiteURL], [DeliveryMethod]FROM [WideWorldImporters].[Website].[Customers]WHERE CustomerID < 3 FOR XML PATH('Customer'), ROOT('Customers');
Example 5 Column Alias on columns other than the individual row tag Suppose we want to add an alias to columns other than the alias for the individual row tag. In the following query, we defined alias on the middle name and the last name column. 1234 SELECT TOP 5 [FirstName] AS '@FirstName', [MiddleName] as 'Person/MiddleName', [LastName] as 'Person/LastName'FROM [AdventureWorks2017].[Person].[Person] FOR XML PATH('Person'), ROOT('Employee'), ELEMENTS XSINIL; ; In the following screenshot, we get both the MiddleName and LastName column are enclosed in the tag along with the alias we defined for individual columns. Let’s add a new column in the SELECT statement. We need to be careful in the order of the select statement. The correct order is to use the new column after the alias columns. 12345 SELECT TOP 5 [FirstName] AS '@FirstName', [MiddleName] AS 'Person/MiddleName', [LastName] AS 'Person/LastName', EmailPromotionFROM [AdventureWorks2017].[Person].[Person] FOR XML PATH('Person'), ROOT('Employee'), ELEMENTS XSINIL; If we use this new column in between the alias columns, you still get the output, but it might complicate the XML as shown in the following screenshots. 12345 SELECT TOP 5 [FirstName] AS '@FirstName', [MiddleName] AS 'Person/MiddleName', EmailPromotion, [LastName] AS 'Person/LastName'FROM [AdventureWorks2017].[Person].[Person] FOR XML PATH('Person'), ROOT('Employee'), ELEMENTS XSINIL; In this screenshot, we get different meta tag for each column with the alias. It might make it complicated to interpret the XML, especially for large data sets.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at [email protected]
View all posts by Rajendra Gupta Latest posts by Rajendra Gupta (see all) Copy data from AWS RDS SQL Server to Azure SQL Database - October 21, 2022 Rename on-premises SQL Server database and Azure SQL database - October 18, 2022 SQL Commands to check current Date and Time (Timestamp) in SQL Server - October 7, 2022
SQLShack
SQL Server training EspañolFOR XML PATH clause in SQL Server
July 30, 2019 by Rajendra Gupta As SQL professionals, we often have to deal with XML data in our databases. This article will help you walk through several examples of using ‘FOR XML PATH’ clause in SQL Server. We get the requirement to display the data from the relational SQL table in various formats. Sometimes developers want to retrieve data in the XML format from the SQL tables holding relational data in regular data types. SQL Server supports XML data using the FOR XML clause. We can easily convert existing data into the XML format using this. We have the following modes available in the FOR XML clause. We can use the FOR XML clause to join or concatenate multiple columns into a single row output as well. We use the FOR XML PATH SQL Statement to concatenate multiple column data into a single row RAW Auto EXPLICIT PATHExample 1 Basic use of the FOR XML PATH clause
Let’s use the WideWorldImporters sample database for this part of the article. Execute the following query, and it retrieves the data in a grid format. 123456789101112 SELECT [CustomerID], [CustomerName], [CustomerCategoryName], [PrimaryContact], [AlternateContact], [PhoneNumber], [FaxNumber], [BuyingGroupName], [WebsiteURL], [DeliveryMethod]FROM [WideWorldImporters].[Website].[Customers]WHERE CustomerID < 3; Click on the Result to Text in the SSMS toolbar and rerun the query. It gives the same result in a text format. In the following screenshot, we do not see the complete results because you have to scroll the bar in SSMS to see other columns. Let’s use the FOR XML PATH clause in previous query and get results in a grid format. To use Grid format, you can click on Results to Grid in the SSMS menu bar as shown below. 123456789101112 SELECT [CustomerID], [CustomerName], [CustomerCategoryName], [PrimaryContact], [AlternateContact], [PhoneNumber], [FaxNumber], [BuyingGroupName], [WebsiteURL], [DeliveryMethod]FROM [WideWorldImporters].[Website].[Customers]WHERE CustomerID < 3 FOR XML PATH; We get the XML output as a hyperlink. Click on the hyperlink and you get the results in a new window. You can note the following in this screenshot. Each row is embedded intoExample 2 Use of an Elements directives with FOR XML PATH
We can use ELEMENTS directives to replaceExample 3 Column Alias in the XML output
Let’s say we want to use CustomerID attribute instead of theExample 4 NULL values and the XML output
Let’s further customized the output using the FOR XML CLAUSE. In the further output, we will use the following data set. You can get this data using the following query in the AdventureWorks database. 12345 SELECT TOP 5 [PersonType], [FirstName], [MiddleName], [LastName] FROM [AdventureWorks2017].[Person].[Person]; Let’s view this data in the XML format with the FOR XML PATH clause and alias in the FirstName column. 1234 SELECT TOP 5 [FirstName] AS '@FirstName', [MiddleName], [LastName]FROM [AdventureWorks2017].[Person].[Person] FOR XML PATH('Person'), ROOT('Employee'); In this output, You can notice that Kim does not have a middle name therefore, you do not get this column in the XML output. We can use the ELEMENTS XSINIL parameter to display NULL values as well in the XML output. 1234 SELECT TOP 5 [FirstName] AS '@FirstName', [MiddleName], [LastName]FROM [AdventureWorks2017].[Person].[Person] FOR XML PATH('Person'), ROOT('Employee'), ELEMENTS XSINIL; It adds the following things in the output. xsi:nil=”true” for the NULL values It also add XML definition in the root elementExample 6 Use of the Wildcard character
We can use the wildcard character * with the FOR XML PATH as well. Once we specify a wildcard with a specific column, in the output, we get that column without the column name. In the following query, we specified the wildcard character for all columns. In the output, we can see it does not return the column name in the XML. 1234 SELECT TOP 5 [FirstName] "*", [MiddleName] "*", [LastName] "*"FROM [AdventureWorks2017].[Person].[Person] FOR XML PATH('Person'), ROOT('Employee'), ELEMENTS XSINIL; Let’s remove the wildcard character for the FirstName column. 1234 SELECT TOP 5 [FirstName], [MiddleName] "*", [LastName] "*"FROM [AdventureWorks2017].[Person].[Person] FOR XML PATH('Person'), ROOT('Employee'), ELEMENTS XSINIL; In the output, you get the column name tag for the Firstname because it does not contain the wildcard character. For other columns, it does not give the column name tag. Similarly, in the below query, we use wildcard characters only for the Lastname column. 1234 SELECT TOP 5 [FirstName], [MiddleName], [LastName] "*"FROM [AdventureWorks2017].[Person].[Person] FOR XML PATH('Person'), ROOT('Employee'), ELEMENTS XSINIL; You can notice the difference in the output. It does not show column name tags only for the LastName column because it contains the wildcard character.Example 7 Use of a XMLNAMESPACE for the XML output
We can use XMLNAMESPACE with the FOR XML PATH to declare a namespace and use it in the Select statements. In the following example, we specified XMLNAMESPACE. We call it in the select query. 123 WITH XMLNAMESPACES('SQLShack' as URL) SELECT 'https://www.sqlshack.com/' as 'URL' FOR XML PATH You get the following output with XMLNAMESPACE query.Example 8 Create a comma-separated string using the FOR XML PATH clause
We can use FOR XML PATH to prepare a comma-separated string from the existing data. Let’s create an Authors table and insert a few records into it. 12345678 declare @Authors Table(ID int,AuthorName varchar(20))Insert @Authors(ID,AuthorName)Values(1,'Rajendra'),(1,'Raj'),(2,'Sonu'),(2,'Raju'),(3,'Akshita'),(3,'Akshu'),(4,'Kashish'),(4,'Kusum')select * from @Authors In the data, we can see we have an ID column and the AuthorName column. If we just select the records, it gives the output in the following format. Assume, we need to combine the data based on the ID column and display list of authors in a comma-separated view. We can use FOR XML PAH and get the output using the following query. 12345678910 SELECT DISTINCT ID, ( SELECT SUBSTRING( ( SELECT ',' + AuthorName FROM @Authors WHERE ID = t.ID FOR XML PATH('')), 2, 200000)) AS AuthorNameFROM @Authors t; Alternatively, we can use the STUFF function in SQL Server along with the FOR XML PATH to retrieve the same result. We use the STUFF function to replace a part of a string with substring at a specified position. The STUFF function makes it easy to write the above query and get the same result set. 123456789 SELECT DISTINCT ID, STUFF(( SELECT ',' + AuthorName FROM @Authors A1 WHERE A1.ID = A2.ID FOR XML PATH('')), 1, 1, '') AS AuthorsFROM @Authors A2;Conclusion
In this article, we demonstrated FOR XML PATH clause and its usage with different examples. We further learned to customize the XML output. If you have any comments or questions, feel free to leave them in the comments below. Author Recent Posts Rajendra GuptaHi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at [email protected]
View all posts by Rajendra Gupta Latest posts by Rajendra Gupta (see all) Copy data from AWS RDS SQL Server to Azure SQL Database - October 21, 2022 Rename on-premises SQL Server database and Azure SQL database - October 18, 2022 SQL Commands to check current Date and Time (Timestamp) in SQL Server - October 7, 2022