XML Support in Transact-SQL
- XML Support in Transact-SQL
- Transact-SQL XML Extensions
- FOR XML AUTO
- Using Subqueries with FOR XML AUTO
- FOR XML RAW
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
The Internet has changed our lives once and for all; most times for better, but sometimes for worse. But the bottom line is that we would never want to go back to living without email, on-line shopping, and the ability to do research right from our desktop.
Microsoft SQL Server has supported Web-related functionality since version 6.5, which allowed you to create somewhat crude and non-interactive HTML pages. These HTML pages display contents of your tables or queries. In the later releases, the Web functionality has been enhanced with Web tasks and wizards that automatically create Web pages according to the settings you specify. More importantly, SQL Server 2000 is one of the first relational database engines that offers native XML support.
Perhaps we should step back for a minute and discuss what XML is and why it is the next "big thing" in the IT industry. The eXtensible Markup Language (XML) is related to Hyper Text Markup Language (HTML), along with other markup languages, which is the heart and soul of the Internet. Therefore, the information contained in XML can be easily translated into HTML and presented in the Web format. More importantly, XML enables programmers to exchange the data in a string format, which is supported by all platforms, all browsers, and all operating systems. Last but definitely not least, XML offers exceptional performance advantage over other ways of exchanging data, again due to the fact that data is passed around as a string, rather than being split into variables with different data types.
You can interact with SQL Server 2000 with XML in several different ways:
Extensions to Transact-SQL
Integration of SQL Server and Internet Information Server (IIS)
Using XML Updategrams
In this article, I'd like to talk about some of Transact-SQL XML extensions. For those who don't have a strong XML background, I'll provide a very brief overview of main XML components. Keep in mind, though, that I won't go into XML details—there are some fine articles and books that you can reference if you'd like to explore XML features further.
Basic Overview of XML
XML documents mainly consist of tags, attributes and elements. Tags can be nested within other tags; in such a case, the outer tags are often referred to as parent tags and the inner tags are the children. The elements are the pieces of information that have their own tags; the attributes are used to describe the elements—they do not have their own tags. Consider the following XML snippet:
<root> <employees EmployeeID="1" LastName="Davolio" FirstName="Nancy" Title="Sales Representative" TitleOfCourtesy="Ms." /> </root>
In this example, <root> is the parent of <employees> tag. EmployeeID, LastName, FirstName, Title, and TitleOfCourtesy are all attributes of the employees element. An alternative way to present the same data is the following:
<root>
<employees>
<EmployeeID>1</EmployeeID>
<LastName>Davolio</LastName>
<FirstName>Nancy</FirstName>
<Title>Sales Representative</Title>
<TitleOfCourtesy>Ms.</TitleOfCourtesy>
</employees>
</root>
The difference is that the attributes of the first example are presented as elements. In the second example, EmployeeID, LastName, FirstName, Title, and TitleOfCourtesy elements are all children of the employees element.
NOTE
Unlike many other programming and markup languages, XML is case-sensitive. Therefore, EmployeeID is not considered the same as employeeID, employeeid, or Employeeid.
If you understand what attributes, elements, parent tags, and child tags are, you're ready to learn how Transact-SQL extensions work.