By: Jeremy Kadlec | Comments (3) | Related: > XML
Problem
With XML being used in so many application and data exchange capacities, extracting XML from SQL Server should not be a challenge. Yet, some organizations are building complex applications to do so and overlooking some of the native SQL Server features. If the data tier has a viable option to extract XML in a native format, should that option be considered?
Solution
Yes - Should be the resounding answer. The trusted SELECT statement (SQL Server 2000 and SQL Server 2005) includes a FOR XML option (SQL Server 2000 and SQL Server 2005) with the ability to extract XML in a few different formats. Each of these options offers a different means to format the XML.
Why would I want to use the FOR XML options?
One of the strongest reasons to use one of the FOR XML options is that they are a simple extension to the trusted SELECT statement. Regardless of the application, some sort of SELECT statement needs to be built, so adding the XML parameters is a no brainier. Another consideration for choosing the FOR XML statement is that the SELECT statement can be called from a stored procedure, DTS\SSIS package or a script. Finally, the FOR XML clause is supported in both SQL Server 2000 (with the exception of the XML PATH option) and 2005.
What are the differences between the FOR XML options?
| ID | Option | Description |
| 1 | FOR XML AUTO | Simple nested tree of XML with each column being represented as a single element |
| 2 | FOR XML RAW | Each row in the result set is transformed into generic <row> element tag |
| 3 | FOR XML EXPLICIT | A predefined XML format is created for the result set |
| 4 | FOR XML PATH | Much of the same functionality as the EXPLICIT mode, but the elements and attributes can be built with XPATH like syntax
This option is not available in SQL Server 2000. |
FOR XML AUTO
| SQL Server 2000 | SQL Server 2005 |
| SELECT TOP 1 a.au_lname AS 'AuthorLastName', a.au_fname AS 'AuthorFirstName', t.title AS 'Title', t.pubdate AS 'PublicationDate' FROM dbo.Authors a INNER JOIN dbo.TitleAuthor ta ON a.au_id = ta.au_id INNER JOIN dbo.Titles t ON ta.title_id = t.title_id WHERE a.state = 'CA' FOR XML AUTO |
SELECT TOP 1 c.CourseName, c.CourseDesc, s.SectionTitle FROM Course c INNER JOIN Section s ON c.CourseID = s.CourseID FOR XML AUTO |
| <a AuthorLastName="Green" AuthorFirstName="Marjorie"><t Title="The Busy Executive's Database Guide" PublicationDate="1991-06-12T00:00:00"/></a> | <c CourseName="DBMS-101" CourseDesc="Database fundamentals"><s SectionTitle="MWF-8:00 AM"/></c> |
FOR XML RAW
| SQL Server 2000 | SQL Server 2005 |
| SELECT TOP 1 a.au_lname AS 'AuthorLastName', a.au_fname AS 'AuthorFirstName', t.title AS 'Title', t.pubdate AS 'PublicationDate' FROM dbo.Authors a INNER JOIN dbo.TitleAuthor ta ON a.au_id = ta.au_id INNER JOIN dbo.Titles t ON ta.title_id = t.title_id WHERE a.state = 'CA' FOR XML RAW |
SELECT TOP 1 c.CourseName, c.CourseDesc, s.SectionTitle FROM Course c INNER JOIN Section s ON c.CourseID = s.CourseID FOR XML RAW |
| <row AuthorLastName="Green" AuthorFirstName="Marjorie" Title="The Busy Executive's Database Guide" PublicationDate="1991-06-12T00:00:00"/> | <row CourseName="DBMS-101" CourseDesc="Database fundamentals" SectionTitle="MWF-8:00 AM"/> |
FOR XML EXPLICIT
| SQL Server 2000 | SQL Server 2005 |
| SELECT 1 AS Tag, NULL AS Parent, t.title AS [Title!1!TitleName], NULL AS [LastName!2!AuthorLastName] FROM dbo.Authors a INNER JOIN dbo.TitleAuthor ta ON a.au_id = ta.au_id INNER JOIN dbo.Titles t ON ta.title_id = t.title_id WHERE a.state = 'CA' UNION ALL SELECT 2 AS Tag, 1 AS Parent, t.title, a.au_lname FROM dbo.Authors a INNER JOIN dbo.TitleAuthor ta ON a.au_id = ta.au_id INNER JOIN dbo.Titles t ON ta.title_id = t.title_id WHERE a.state = 'CA' ORDER BY [Title!1!TitleName], [LastName!2!AuthorLastName] FOR XML EXPLICIT |
SELECT 1 AS Tag, NULL AS Parent, c.CourseName AS [Course!1!CourseName], NULL AS [Section!2!SectionTitle] FROM Course c INNER JOIN Section s ON c.CourseID = s.CourseID WHERE c.CourseID = 1 UNION ALL SELECT 2 AS Tag, 1 AS Parent, c.CourseName, s.SectionTitle FROM Course c INNER JOIN Section s ON c.CourseID = s.CourseID WHERE c.CourseID = 1 FOR XML EXPLICIT |
| <Title TitleName="But Is It User Friendly?"> <LastName AuthorLastName="Carson"/></Title> ... | <Course CourseName="DBMS-101"> <Section SectionTitle="MWF-8:00 AM" /> </Course> |
FOR XML PATH
| SQL Server 2000 | SQL Server 2005 |
| Not available | SELECT c.CourseName, c.CourseDesc, s.SectionTitle FROM Course c INNER JOIN Section s ON c.CourseID = s.CourseID FOR XML PATH |
| Not available | <row> <CourseName>DBMS-101</CourseName> <CourseDesc>Database fundamentals</CourseDesc> <SectionTitle>MWF-8:00 AM</SectionTitle> </row> |
Next Steps
- As you are faced with requirements to extract XML from your current systems, consider the SELECT FOR XML options as a viable means to complete the task.
- If you have not had time to explore the many capabilities of XML, start with these simple examples and begin to make them more complex to meet your needs.
- Stay tuned for more of SQL Server's native XML capabilities...
About the author
Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
