- Introduction
- Returning Data as XML from the SQL Server Database
- Getting XML Data into the SQL Server Database
- Conclusion
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Returning Data as XML from the SQL Server Database
We can use SQL Server to return results from a dataset as XML. Because XML is a markup language like HTML, it would be tedious to have to convert the data manually, especially with a very large recordset. Instead, to return data as XML from a query, we'll use the Transact SQL (T-SQL) FOR XML clause, which can be used at the end of a SELECT statement.
If you're at all familiar with XML, you're probably aware of the hierarchical formatting arrangements of XML elements and attributes. Simply stated, to have the most control over your data in an XML document for retrieval purposes, you use nesting. When using the FOR XML clause, the MODE argument determines how the XML data is to be formatted in the result set, by using one of three values: RAW, AUTO, or EXPLICIT.
- RAW returns each row returned by the query as an XML element. RAW is the least-nested format; it's good for XML documents that don't need extensive data manipulation.
- AUTO nests elements inside other elements, making it easier to perform more advanced queries on the XML document. Suppose we're doing a query on three tables, getting the author's name and ID from the Authors table, and the titles tied to this author from the Titleauthor and Titles tables. AUTO would nest all the titles returned in the Titles table as elements within a main Author element for each author returned by the Authors table.
- Using EXPLICIT, you can place data in certain sections of an XML document, hide columns, force attributes to become elements, and more.
To compare the results, I used the Query Optimizer with the Pubs database and the example just described. Listing 1 shows the query and Listing 2 shows the XML result set returned using the RAW value.
Listing 1 Query for RAW Format
Select Authors.au_id,Authors.au_lname,Authors.au_fname,Titles.title From Authors,Titleauthor,Titles WHERE Titleauthor.au_id = Authors.au_id And Titles.title_id = Titleauthor.title_id ORDER BY Authors.au_lname FOR XML RAW
Listing 2 XML Result Set in RAW Format
<row au_id="409-56-7008" au_lname="Bennet" au_fname="Abraham" title="The Busy Executive's Database Guide"/> <row au_id="648-92-1872" au_lname="Blotchet-Halls" au_fname="Reginald" title="Fifty Years in Buckingham Palace Kitchens"/> <row au_id="238-95-7766" au_lname="Carson" au_fname="Cheryl" title="But Is It User Friendly"/> <row au_id="722-51-5454" au_lname="DeFrance" au_fname="Michel" title="The Gourmet Microwave"/> <row au_id="712-45-1867" au_lname="del Castillo" au_fname="Innes" title="Silicon Valley Gastronomic Treats"/> <row au_id="427-17-2319" au_lname="Dull" au_fname="Ann" title="Secrets of Silicon Valley"/> <row au_id="267-41-2394" au_lname="Ellis" au_fname="Michael" title="Cooking with Computers: Surreptitious Balance Sheets"/> <row au_id="267-41-2394" au_lname="Ellis" au_fname="Michael" title="Sushi for Anyone"/> <row au_id="213-46-8915" au_lname="Green" au_fname="Marjorie" title="The Busy Executive's Database Guide"/> <row au_id="213-46-8915" au_lname="Green" au_fname="Marjorie" title="You Can Combat Computer Stress"/>
Notice that each row is an element in RAW format. After running the same query using the AUTO value as shown in Listing 3, we get the results in Listing 4.
Listing 3 Query for AUTO Format
Select Authors.au_id,Authors.au_lname,Authors.au_fname,Titles.title From Authors,Titleauthor,Titles WHERE Titleauthor.au_id = Authors.au_id And Titles.title_id = Titleauthor.title_id ORDER BY Authors.au_lname FOR XML AUTO
Listing 4 XML Result Set in AUTO Format
<Authors au_id="409-56-7008" au_lname="Bennet" au_fname="Abraham"> <Titles title="The Busy Executive's Database Guide"/></Authors> <Authors au_id="648-92-1872" au_lname="Blotchet-Halls" au_fname="Reginald"> <Titles title="Fifty Years in Buckingham Palace Kitchens"/></Authors> <Authors au_id="238-95-7766" au_lname="Carson" au_fname="Cheryl"> <Titles title="But Is It User Friendly"/></Authors> <Authors au_id="722-51-5454" au_lname="DeFrance" au_fname="Michel"> <Titles title="The Gourmet Microwave"/></Authors> <Authors au_id="712-45-1867" au_lname="del Castillo" au_fname="Innes"> <Titles title="Silicon Valley Gastronomic Treats"/></Authors> <Authors au_id="427-17-2319" au_lname="Dull" au_fname="Ann"> <Titles title="Secrets of Silicon Valley"/></Authors> <Authors au_id="267-41-2394" au_lname="Ellis" au_fname="Michael"> <Titles title="Cooking with Computers: Surreptitious Balance Sheets"/> <Titles title="Sushi for Anyone"/></Authors> <Authors au_id="213-46-8915" au_lname="Green" au_fname="Marjorie"> <Titles title="The Busy Executive's Database Guide"/> <Titles title="You Can Combat Computer Stress"/></Authors>
You can see the difference right away. The titles for each author are now nested as their own elements inside an Authors element. The Authors element is the parent element; the elements inside it are the child elements. If you want more control over your XML formatting than RAW or AUTO provide, use the EXPLICIT value.
So far, we've looked at how to get XML data from SQL Server by using T-SQL. Now let's consider how to get XML data into SQL Server.