NEWS
SQLXML 3.0 SP2 Beta 1 now available!
SQL Server 2000 XML and .NET Integration (aka SQLXML)
SQLXML Home
SQLXML Tutorials
SQLXML KB Articles
SQLXML Links
SQLXML News
perfectxml.com Recommends
Professional SQL Server 2000 XML
SQL Server 2000 XML Distilled
|
SQL Server 2000 XML & .NET Integration Features
Microsoft® SQL Server™ 2000 introduced support for XML and related standards. This included:
-
FOR XML
The Transact SQL SELECT statement was extended in SQL Server 2000 to tell the database engine to return data as an XML stream instead of as a rowset. The FOR XML clause is used with the SELECT statement to convert a rowset into XML format. In the extended statement, a SELECT...FOR XML query is executed in one of the following three modes:
- RAW mode: In RAW mode, each record in the resultant rowset is converted to an element named row, which contains an attribute for each of the columns that is retrieved. RAW mode does not support retrieving binary data.
- AUTO mode:In AUTO mode, you have some control over the resultant XML format. You can also use AUTO mode when you are using complex JOIN queries or queries that return binary data columns. GROUP BY queries and aggregate functions are not supported in this mode
- EXPLICIT mode:With EXPLICIT mode, you have the greatest control over the resultant XML format; but using EXPLICIT mode requires a more complex query syntax. But what if you wanted to do the reverse of this, that is, what if you wanted to convert an XML document into rowset that could, for example, be used to insert or update relational data. Enter OPENXML.
- OPENXML
Microsoft SQL Server 2000 introduced a new Transact SQL function named OPENXML, a function that could be used to build a relational rowset from an XML stream. The rowset that is generated then can be used in other SQL statements. The most common application of OPENXML is inserting data from an XML document into tables in a database.
- HTTP Database Access
Another feature introduced by SQL Server 2000 is the ability to access relational data over HTTP directly by writing the queries at the end of the URLs. SQL Server 2000 provides a tool that can be used to create specialized HTTP virtual directories, which link to a SQL Server database. On this virtual root, we now can run the queries through an HTTP URL address directly. However, in the interest of better security, it is recommended that you not use URL queries to access data over HTTP, but instead use XML template files. XML template files are physical files that are stored under the virtual root directory; they contain the SQL queries. SQL Server 2000 also permits using XPath queries in conjunction with an XDR Schema to retrieve data over HTTP.
To continue extending and enhancing the support for XML in SQL Server 2000, and also to intoduce the .NET and Web services (SOAP) support in SQL Server 2000, Microsoft decided to make periodic Web Releases of SQLXML (XML for SQL Server 2000). SQLXML 3.0 was released recently (Feb 2002).
-
SQLXML 1.0
Microsoft SQLXML 1.0 was released in February of 2001. This release introduced two new features, updategrams and the XML Bulk Load tool.
- Updategrams: Updategrams are an alternative to OPENXML function and can be used to modify relational data through XML over HTTP. They are similar to XML template files, but they have a different syntax, one that describes the current state of the data and how the data should look after the updategram has been executed.
- XML Bulk Load:SQLXML 1.0 shipped a COM object that can be used to very quickly and efficiently import XML data into a database.
-
SQLXML 2.0
Microsoft SQLXML 2.0 was released in October of 2001, and it introduced the following three new features:
- SQLXML Managed Classes: SQLXML 2.0 introduced Microsoft .NET Framework support, which allows developers who are creating applications on the .NET platform to use SQLXML features from within their applications. The Microsoft.Data.SqlXml namespace contains three classes: SqlXmlAdapter, SqlXmlCommand, and SqlXmlParameter. These classes facilitate the execution of SQL (FOR XML) queries and XPath queries and calling XML templates and template files, updategrams, and DiffGrams.
- Annotated XSD Schemas support: The XSD support in SQLXML 2.0 allows developers to use XSD annotations for mapping an XSD schema to database tables and columns and creating XML views of the relational data. These XML views then can be used to run XPath queries to return records in XML format.
- Client-side XML formatting: SQL Server 2000 introduced the FOR XML clause, which converts the rowset into an XML document on the server side (on the computer on which SQL Server is running). SQLXML 2.0 provides the flexibility to convert the rowset to an XML document on the client side. In other words, if you call the SELECT…FOR XML query and client-side XML formatting is enabled, only the SELECT statement is passed to the instance of SQL Server, which returns a rowset; the rowset is then converted to an XML document by SQLXML on the client side (on the computer on which SQLXML is installed).
-
SQLXML 3.0
Microsoft SQLXML 3.0 was released recently (Feb 2002). It contains enhancements to existing features, such as DiffGrams and client-side XML formatting, and introduces one new feature:
- Web services (SOAP) support: This feature permits enabling the existing stored procedures, user-defined functions (UDFs), or XML template files and updategrams, as Web services, within seconds. These services then can be accessed from any platform or any language by just using SOAP.
Microsoft announced the SQL Server 2000 Web Services Toolkit at VSLive 2002 event. Read more about this toolkit here.
In summary, the XML and .NET support in SQL Server 2000 truely helps rapidly build extensible and scalable enterprise database applications.
|
