If you work in the IT industry, then you are likely to hear XML; but if your work is mainly related to SQL Server, then you may not use XML directly. XML is already a common data format in a web programming environment, and it is also one of the main underground technologies in .NET Framework. SQL Server supports XML in two ways: through the functionality of SQL Server itself, as well as additional functions called SQLXML. SQLXML extends SQL Server and provides XML compatibility. In this month's column, I will tell SQL Server's built-in support for XML and tell some features increasing by issuing SQLXML (see Figure 1). SQL Server supports XML means that we can update and read data more effectively; we no longer need to convert XML data into another format that can be understood, or convert XML data from the database into XML. Moreover, the methods available for the selection of developers are more, that is, they are more flexible in accessing the data. There are many publications to XML, so it is important to realize that it is just a simple technology. Essentially, it is a standard file format used to describe data. Since the initial version of the release of SQL Server 2000, the support of XML is already part of SQL Server. SQL Server does not support XML by providing a method to support XML, but provides an interface to the relational data so that you can read and write XML data in the table and other database objects. The XML features inherent in SQL Server include: Access SQL Server can be accessed via HTTP, template query, for xml clause, and OpenXML () function. Next, I will tell 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 a virtual directory. This virtual directory provides a link between HTTP protocols and a specific database. When setting up a virtual directory, we need to use the "Configure SQL XML Support In IIS" menu entry, you can find the item in the SQL Server menu entry in the Window's Start menu. Through this menu entry, you can specify the name, physical path, server name, database name, and registration information of the virtual directory. Once you have created a virtual directory, you can send the query to the database via a URL. If you set a virtual directory called Northwind and enter the query http: // localhost / northwind? SQL = SELECT * FROM SHIPPERS FOR XML AUTO, Elements & root = Shippers, it The XML data in the ShipPers example similar to the "XML 101" toolbar is returned. The HTTP query allows us to access websites or web applications compared to any other technique that uses ADO or any other technique. For a simple query statement, HTTP queries will be very good, but for a more complex query, this format will become difficult to understand and manage it. This method is not safe because the query source code is exposed to the user. Another alternative method is to call a template query on HTTP. A template query is an XML file containing SQL queries. The template is saved on the server as a file. So if you encapsulate ShipPers SELECT queries in a template called getShippers.xml, then the URL query is: http://localhost/northwind/templates/getshippers.xml. The template can also have parameters that will be useful when your template calls a stored procedure. In the URL query and template query, if you want to return an HTML page from the query, you can specify an XSLT style sheet to use it for XML. Template query is a more secure method for reading data, which can be cached to get better performance. You can also use the for XML clause to read the data into an XML format, which returns data from the SQL Server table, you can see them as XML data. You can use the for XML clause in a SELECT statement, and it has three modes to return XML: RAW, AUTO, and Explicit in different formats. The RAW mode returns each record in the result as a normal row element, which is included in a tab and uses the value of each column as an attribute. The AUTO mode returns each record as a row element, naming it according to the source table or view. If the query returns multiple columns from a table, the value of each column will be returned as the properties of the table element. But the most important thing is that if your SELECT statement executes the merge operation, the auto mode represents the subline, which is nested as an element under the father. Explicit mode has several parameters that you can fully define the returned XML style through these parameters. You can define a label for each element, and clearly determine how the data is nested. The FOR XML statement allows us to return a Rowset again, then convert it into XML in the client or intermediate layer.
The OpenXML function allows you to use XML data like a table, you can convert them into a ROWSET in memory. To use OpenXML, first call the sp_xml_preparedocument stored procedure, in fact, it analyzes XML into a data tree and passes the handle of that data to the OpenXML function. Then you can do that data: Inquiry, insert it into the table, and so on. The OpenXML function can have three parameters: the handle for the interior of the XML document, a rowpattern parameter, and a Flags parameter. The RowPattern parameter specifies which nodes in the original XML document should be returned. The Flags parameter specifies the property-centric mapping (result set column names in accordance with the property name) or the element-centric mapping (the result set column column meets the element name). After processing XML data, we can call SP_XML_REMOVEDOCUMENT to remove XML data from memory. Get more support via SQLXML
By publishing SQLXML (also known as Web), Microsoft also provides more XML support in SQL Server. There are already three SQLXML versions, and some of them include UpdateGram and XML Bulk Load. You can download the latest version SQLXML 3.0 (see resources) online. You can use UpdateGram to insert, update, or delete data in the table by XML-based templates. The template has a Before Block, which describes the current state before the update; there is an after block, which describes the change of the record. Below is an example of UpdateGram, which modifies a Phone field of a company in the Shippers table:
By default, UpdateGrams believes that the fields in Before Block and After Block refer to tables and columns. But UpdateGrams can also use a mapping schema. Mapping Schema associates an element in an XML document with an element in a table. If you reference the property name SID in the template, you will map the SID to the ShipperID column of Mapping Schema. You can send UpdateGrams to SQL Server by HTTP (same as template query) or through ADO. They can also be parametric, with input values. UpdateGrams provides a way to update SQL Server data directly from XML, so you don't have to get data from the XML document, and then use a record set or call a stored procedure. Updategrams only can easily insert, update, or delete data, so if you need to see if there is something that exists, or check out some business rules before updating, then you should use OpenXML. Although you can insert data with the OpenXML function and UpdateGrams, these two methods are not practical for loading a large number of XML data. You should use XML Bulk Load to insert a large amount of XML data into the SQL Server table. In fact, we use the SQLXMLBULKLOAD component to load data, you can call this component from a client application. After establishing a connection to the database, the Bulk Load component requires a path to access mapping schema to map XML properties and elements to the database object, and the path is also required to access an XML document or an XML stream. In the Bulk Load component, you can specify whether to perform the data table check constraint, whether to ignore the records added by the replication button, if the data is inserted, whether the data table should be locked, and so on. By default, a large amount of load is not a transactional, so if an error occurs, the data inserted before the error point will remain in the database. You can specify that all loaded data is in a separate transaction process, so the process is either successful or will roll back. If you use transaction processing, all data will be written to a temporary file before insertion. This means that you need enough disk space to save temporary files, and loading data may be quite slow. XML Bulk Load provides us with a good way to write a lot of data to SQL Server; otherwise, you must extract the data, then use additional methods to load it into your database. You can also configure SQL Server to cache XSLT style sheets, templates, and mapping schema to get better performance. Depending on the implementation, you can use the XML query through HTTP and XSLT to replace the standard ASP / ADO data accessed in the web application, so that the HTML output is greatly improved. The above is some advanced support for SQL Server to XML. No matter how much we have propaganda, XML's function is so much. Because XML is a standard for displaying data, it is also a data transmission technology for .NET, so integration capabilities of XML and SQL Server will be an important issue that companies need to solve. About the author: Dianne Siebold is in charge of business analytics software company in San Diego, is also a bit specializing in VB programmers and SQL Server development.
