chap16_0789724499
- Chapter 16 - Working With XML Data in SQL Server 2000
- XML Support in SQL Server 2000
- Retrieving SQL Server Data in XML Format
- Using XML Data With Transact-SQL
- HTTP Access to SQL Server 2000
- Modifying SQL Server 2000 Data With Updategrams
- What's Next?
Chapter 16 - Working With XML Data in SQL Server 2000
SQL Server 2000 can use Internet protocols and standards to communicate with other applications and systems. The Extensible Markup Language, or XML, is the de facto standard format for interapplication communications on the Internet.
This chapter teaches you how SQL Server uses Web technologies and XML to provide platform-independent communication. This is only an introduction to this large and popular subject; for a more complete discussion of how XML works in general you will have to find a good book on XML, such as Benoît Marchal's XML by Example. This chapter teaches you the following:
Basic XML concepts
How to retrieve SQL Server 2000 data in XML format
How to use XML documents in Transact-SQL
How to access SQL Server through HTTP
How to update, insert, and delete SQL Server data using XML Updategrams
Short Introduction to XML
Every computer application uses its own data types, and stores that data in its own internal format. Interapplication communication is a complex operation that requires an agreement on communication formats. If applications are running on different hardware and operating system platforms, the situation is even more complex.
The Extensible Markup Language (XML) alleviates this situation. XML provides a self-describing format for semi-structured data in plain text, which is compatible with most computer systems.
Listing 16.1 shows a typical XML document. You can create XML documents using any text editor, such as Notepad.
Listing 16.1—This Is a Typical XML Document
<?xml version='1.0' ?> <!--this is the way you write comments in a XML document --> <Products> <Product ProductID="1" ProductName="Chai" UnitPrice="18.0000"/> <Product ProductID="2" ProductName="Chang" UnitPrice="19.0000"/> <Product ProductID="3" ProductName="Aniseed Syrup" UnitPrice="10.0000"/> </Products>
You can open XML documents using Internet Explorer 5.5. If you save the document from Listing 16.1, using XML as file extension, and then open the document in Internet Explorer, you will see something similar to Figure 16.1.
){kind=link}
Figure 16.1 You can use Internet Explorer 5.5 as an XML viewer.
In the XML document in Listing 16.1, you can see different sections. This tag is a processor instruction that identifies this document as an XML document, and identifies which XML version it uses:
<?xml version='1.0' ?>
This is just a comment inside the document:
<!--this s the way you write comment in a XML document -->
This tag identifies the root element this document describes. Every well-formed XML document must have a root element. This document describes some products:
<Products>
Each one of the following lines describes one particular element, a product in this case, and specifies three attributes for every product: ProductID, ProductName, and UnitPrice. For every attribute, the document specifies the name of the attribute and the value of that attribute for this particular product. The / symbol at the end of each line represents the end of the individual element definition. In this case, it is a simplification of the formal syntax </Product>:
<Product ProductID="1" ProductName="Chai" UnitPrice="18.0000"/> <Product ProductID="2" ProductName="Chang" UnitPrice="19.0000"/> <Product ProductID="3" ProductName="Aniseed Syrup" UnitPrice="10.0000"/>
These three lines could have been written like this:
<Product ProductID="1" ProductName="Chai" UnitPrice="18.0000"> </Product> <Product ProductID="2" ProductName="Chang" UnitPrice="19.0000"> </Product> <Product ProductID="3" ProductName="Aniseed Syrup" UnitPrice="10.0000"> </Product>
At the end of the XML document, you must have a closing tag that defines the end of the root element definition:
</Products>
CAUTION
Note that XML is case-sensitive. Using a different case for the closing and starting tags of an element will give you an XML syntax error. For example, Listing 16.2 gives you the error message shown in the enclosed output. The only difference between Listing 16.1 and Listing 16.2 is that in Listing 16.2, the case of the closing tag </products> is different from the starting tag; the closing tag should be </Products> instead.
Listing 16.2—This Document Fails Because It Uses a Different Case for Its Closing Tag
<?xml version='1.0' ?> <!--this s the way you write comment in a XML document --> <Products> <Product ProductID="1" ProductName="Chai" UnitPrice="18.0000"/> <Product ProductID="2" ProductName="Chang" UnitPrice="19.0000"/> <Product ProductID="3" ProductName="Aniseed Syrup" UnitPrice="10.0000"/> </products>
The XML page cannot be displayed Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later. ----------------------------------------------------------------------------- End tag 'products' does not match the start tag 'Products'. Line 8, Position 3 </products> --^
In some cases, you might prefer to define the attributes as independent elements. This could be useful to describe the hierarchy of the data. Listing 16.3 shows the same XML document as in Listing 16.1, but in this case, every attribute has been defined as an independent element. Figure 16.2 shows this document in Internet Explorer. Note the difference between Figures 16.1 and 16.2.
){kind=link}
Listing 16.3—This Is a Typical XML Document with Attributes Defined As Elements
<?xml version='1.0' ?> <Products> <Product> <ProductID>1</ProductID> <ProductName>Chai</ProductName> <UnitPrice>18.0000</UnitPrice> </Product> <Product> <ProductID>2</ProductID> <ProductName>Chang</ProductName> <UnitPrice>19.0000</UnitPrice> </Product> <Product> <ProductID>3</ProductID> <ProductName>Aniseed Syrup</ProductName> <UnitPrice>10.0000</UnitPrice> </Product> </Products>
Figure 16.2 An element-centric XML document.
Listing 16.4 shows an example of a hierarchical XML structure. Figure 16.3 shows how this document is displayed in Internet Explorer. Figure 16.4 shows the tree representation of this XML document where every element is represented as a node in a tree structure.
){kind=link}
){kind=link}
Listing 16.4—An XML Document Can Represent a Data Hierarchy
<?xml version='1.0' ?>
<Customers>
<Customer CompanyName="Victuailles en stock">
<Order Date="1996-07-08">
<Product Name="Gustafaposs Knackebrod" Price="16.8000" Quantity="6"/>
<Product Name="Ravioli Angelo" Price="15.6000" Quantity="15"/>
<Product Name="Louisiana Fiery Hot Pepper Sauce" Price="16.8000"
Quantity="20"/>
</Order>
<Order Date="1996-10-21">
<Product Name="Filo Mix" Price="5.6000" Quantity="8"/>
<Product Name="Scottish Longbreads" Price="10.0000" Quantity="10"/>
</Order>
<Order Date="1997-02-19">
<Product Name="Ikura" Price="24.8000" Quantity="20"/>
<Product Name="Tourtiere" Price="5.9000" Quantity="6"/>
</Order>
<Order Date="1997-02-27">
<Product Name="Uncle Bobaposs Organic Dried Pears" Price="24.0000"
Quantity="16"/>
<Product Name="Spegesild" Price="9.6000" Quantity="20"/>
<Product Name="Mozzarella di Giovanni" Price="27.8000" Quantity="40"/>
</Order>
<Order Date="1997-03-18">
<Product Name="Ikura" Price="24.8000" Quantity="20"/>
</Order>
<Order Date="1997-05-23">
<Product Name="Uncle Bobaposs Organic Dried Pears" Price="30.0000"
Quantity="10"/>
<Product Name="Steeleye Stout" Price="18.0000" Quantity="30"/>
<Product Name="Tarte au sucre" Price="49.3000" Quantity="40"/>
</Order>
<Order Date="1997-12-31">
<Product Name="Chang" Price="19.0000" Quantity="20"/>
<Product Name="Louisiana Fiery Hot Pepper Sauce" Price="21.0500"
Quantity="2"/>
<Product Name="Longlife Tofu" Price="10.0000" Quantity="15"/>
</Order>
</Customer>
<Customer CompanyName="Vins et alcools Chevalier">
<Order Date="1996-07-04">
<Product Name="Queso Cabrales" Price="14.0000" Quantity="12"/>
<Product Name="Singaporean Hokkien Fried Mee" Price="9.8000"
Quantity="10"/>
<Product Name="Mozzarella di Giovanni" Price="34.8000" Quantity="5"/>
</Order>
<Order Date="1996-08-06">
<Product Name="Flotemysost" Price="17.2000" Quantity="20"/>
<Product Name="Mozzarella di Giovanni" Price="27.8000" Quantity="7"/>
</Order>
<Order Date="1996-09-02">
<Product Name="Gnocchi di nonna Alice" Price="30.4000" Quantity="4"/>
</Order>
<Order Date="1997-11-11">
<Product Name="Konbu" Price="6.0000" Quantity="4"/>
<Product Name="Jack's New England Clam Chowder" Price="9.6500"
Quantity="12"/>
</Order>
<Order Date="1997-11-12">
<Product Name="Inlagd Sill" Price="19.0000" Quantity="6"/>
<Product Name="Filo Mix" Price="7.0000" Quantity="18"/>
</Order>
</Customer>
</Customers>
Figure 16.3 Hierarchical XML document displayed in Internet Explorer.
Figure 16.4 Graphical representation of a hierarchical XML document.
As you can see in Figures 16.3 and 16.4, this XML document contains different hierarchical levels:
A root level called Customers, which defines the purpose of the XML document data.
One Customer element for every customer. This element contains the Name attribute, displaying the company name in this case.
One Order element for every order placed for this particular customer. This element shows, as an attribute, the order's date.
One Product element for every order for every product contained on that particular order. For every product the document shows, as attributes, the name of the product, its unit price, and the quantity ordered.
Looking at the previous examples, you might wonder why you would use this XML format, instead of the standard output format from SQL Server, as returned from the various database libraries. The main reasons are compatibility and versatility.
Every information system accepts and understands text files, and so every system accepts XML files. However, not every system can interpret result sets, as returned from the pure Tabular Data Stream (TDS) SQL Server format. And not every system has database libraries available to connect to SQL Server and interpret TDS packets.
If you look at the examples shown so far, there is nothing in those XML documents about the format to use to display them. These documents contain pure data and its definition but no format instructions. This is an important aspect of XML documents: They are highly versatile in terms of the way you can choose to represent them.
To define the format to use when representing XML documents, you can use style sheets, as defined by the Extensible Stylesheet Language (XSL).
Listing 16.5 shows an example of how you can change the display format of an XML document by providing the appropriate XSL file. Listing 16.5 defines a style sheet designed to apply formatting to the document from Listing 16.1. Listing 16.6 shows the document from Listing 16.1, including the reference to the XSL file. Figure 16.5 shows the formatted display as shown in Internet Explorer.
){kind=link}
Listing 16.5—An XSL File
<xsl:stylesheet xmlns:xsl='http://www.w3.org/TR/WD-xsl'>
<xsl:template match='/'>
<HTML>
<Title>Product's List</Title>
<Body>
<Table border='0'>
<TR><TD>Product</TD><TD>Name</TD><TD>Price</TD></TR>
<xsl:for-each select='Products/Product'>
<TR>
<TD><xsl:value-of select='@ProductID'/></TD>
<TD><xsl:value-of select='@ProductName'/></TD>
<TD><xsl:value-of select='@UnitPrice'/></TD>
</TR>
</xsl:for-each>
</Table>
</Body>
</HTML>
</xsl:template>
</xsl:stylesheet>
Listing 16.6—Add a Reference to the XSL File in the XML Document
<?xml version='1.0'?> <!--this is the way you add a reference to the Style Sheet --> <?xml-stylesheet type='text/xsl' href='xmlListing5.xsl'?> <!--this is the way you write comments in a XML document --> <Products> <Product ProductID="1" ProductName="Chai" UnitPrice="18.0000"> </Product> <Product ProductID="2" ProductName="Chang" UnitPrice="19.0000"> </Product> <Product ProductID="3" ProductName="Aniseed Syrup" UnitPrice="10.0000"> </Product> </Products>
Figure 16.5 An XML document as it appears in Internet Explorer after you apply a simple XSL file to it.
The main benefit of this technique is to be able to divide the development between the data itself and the format to apply to the data. The Web design team can focus on how to provide a great look to the Web pages, whereas the data programming team can focus on data accuracy and consistency, without worrying about how to represent the data.
Listing 16.7 shows a different XSL file, which changes the format of the previous XML document. Figure 16.6 shows how the XML document from Listing 16.6 will look in Internet Explorer, after changing only the following line of code to point to the new XSL file:
<?xml-stylesheet type='text/xsl' href='xmlListing7.xsl'?>
In this way it is easy to change completely the look of a Web site, without affecting the programming logic or the data available in the XML files.
Listing 16.7—An XSL File with Formatting Directives
<xsl:stylesheet xmlns:xsl='http://www.w3.org/TR/WD-xsl'>
<xsl:template match='/'>
<HTML>
<HEAD>
<Title>Products List</Title>
<STYLE>
.subject {
font-family: Garamond;
font-weight: bold;
font-size: 48;
}
.headings {
font-family: Garamond;
font-weight: bold;
font-size: 24;
}
.productname {
font-family: Garamond;
font-weight: bold;
font-size: normal;
}
</STYLE>
</HEAD>
<Body>
<Table>
<TR>
<TD CLASS="subject">Products List</TD>
</TR>
</Table>
<Table border='1'>
<TR>
<TD CLASS="headings">Product</TD>
<TD CLASS="headings">Name</TD>
<TD CLASS="headings">Price</TD>
</TR>
<xsl:for-each select='Products/Product'>
<TR>
<TD><xsl:value-of select='@ProductID'/></TD>
<TD CLASS="productname"><xsl:value-of select='@ProductName'/></TD>
<TD><xsl:value-of select='@UnitPrice'/></TD>
</TR>
</xsl:for-each>
</Table>
</Body>
</HTML>
</xsl:template>
</xsl:stylesheet>
Figure 16.6 An XML document displayed in Internet Explorer after applying an XSL file with more complex formatting.
){kind=link}
Studying the different possibilities that this powerful, and yet simple, language offers you is outside the scope of this book. You can learn more about XML from the book XML by Example (Benoît Marchal, QUE, ISBN 0-7897-2242-9).