Returning XML Result Sets with SQL Server


By:   |   Updated: 2006-09-19   |   Comments (3)   |   Related: More > 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&apos;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&apos;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...


Last Updated: 2006-09-19


get scripts

next tip button



About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an MSSQLTips.com co-founder and Edgewood Solutions SQL Server Consultant.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Thursday, July 17, 2014 - 5:41:47 AM - Pavan Back To Top

Very usefull article.

The XML returns with double quote value. How can I get single quote?

 

Thanks,

Pavan


Saturday, October 20, 2012 - 6:34:39 AM - rony Back To Top

is there a limitation on the xml created if I do the followingI only ever get a max of 8000 characters .. I tried everything and nothig changes what i the point of thsi For xml id u can only dump to 8000 or 4000 nvarchar strings

 

declare @IoResult varchar(max)
set @IoResult = CAST((SELECT * from Employees FOR XML RAW('EM'), ELEMENTS ) AS VARCHAR(MAX))
print @IoResult


Tuesday, June 01, 2010 - 7:15:54 AM - TheBlessedOne Back To Top

Hi

I am querying a SSAS cube using stored procedure [Using OPEN Query through Linked Server] and get the result in tabular format.

I wanted to get the result of this query in XML foramt which can be consumed by another downstream application.

 

Can you please suggest if this is possible or not ? Or if there is any other better approach to implement this ?

 

Thanks !!



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools