Let SQL Server data support XML

If you work in it, you probably have heard of XML, but if your job is primarily about SQL Server, you may not be using XML directly. XML is already a popular data format in the Web programming environment, and it is one of the main underlying technologies in the. NET framework. SQL Server supports XML in two ways: through the capabilities of SQL Server itself, and by publishing additional functionality called SQLXML. SQLXML extends SQL Server and provides XML compatibility. In this month's column, I'll cover SQL Server's built-in support for XML and describe some of the additional features that have been added by publishing SQLXML (see Figure 1). SQL Server support for XML means that we can update and read data more effectively; We no longer need to convert XML data into another format that the database can understand, or convert XML data from a database into XML. And there are more options available for developers to choose from, which means they are more flexible in accessing data.

There's been a lot of hype about XML, so it's important to realize that it's just a simple technique. In essence, it is a standard file format used to describe data. (For more information about the basics of XML, see the toolbar XML 101.) Since the initial release of SQL Server 2000, support for XML is already part of SQL Server. Instead of providing a way to save XML files to support XML, SQL Server provides an interface to relational data that allows you to read and write XML data in tables and other database objects. The XML features inherent in SQL Server include: You can access SQL Server through HTTP, template queries, FOR XML clauses, and OPENXML () functions. Next, I'll tell you how these functions work and how they can benefit your business.

Figure 1. Provide XML support

To access a SQL Server database via HTTP, you must first set up a virtual directory. This virtual directory provides a link between the HTTP protocol and a specific database. When setting up a virtual directory, we need to use the "Configure SQL XML Support in IIS" menu entry, which you can find in the SQL Server menu entry in the window's Start menu. With this menu entry, you can specify the name of the virtual directory, the physical path, the server name, the database name, and the registration information. Once you create a virtual directory, you can send the query to the database via a URL. If you set up a virtual directory called Northwind and entered a query in the browser/northwind?sql=select+*+from+shippers+for+xml+auto,elements+&root= Shippers, it returns XML data similar to the shippers example in the XML 101 toolbar. HTTP queries make it easier for us to access data for a Web site or Web application than to use ADO or any other technology. HTTP queries are good for a simple query, but for a more complex query, this format becomes difficult to understand and difficult to manage. This method is also unsafe because the query source code is exposed to the user. Another option is to use a template query on the HTTP upgrade. A template query is an XML file that contains SQL queries. The template is saved as a file on the server. Therefore, if you encapsulate a shippers select query in a template called Getshippers.xml, the form of a URL query would be:/northwind/templates/getshippers.xml. Templates can also have parameters, which are useful when your template invokes a stored procedure. In URL queries and template queries, if you want to return an HTML page from a query, you can specify an XSLT stylesheet to use in XML. A template query is a more secure way to read data, and it can be cached for better performance.