TDWI | Training & Research | Business Intelligence, Analytics, Big Data, Data Warehousing

XML and Web Services Go Native in SQL Server 2005

XML and Web services make SQL Server a more interoperable player for BI; both are seen as mixed blessings by IT pros, too.

• By Stephen Swoyer
• June 1, 2005

When Microsoft Corp. first incorporated business intelligence (BI) capabilities into its SQL Server 7.0 database seven years ago, it kicked off a firestorm of sorts, with some purists arguing that such features had no place in a relational database management system.

Since then, the debate has flared up on several occasions. When Oracle Corp., for example, followed Microsoft’s lead (and then some) by dropping BI features and native XML support into its flagship data store, the database giant’s move elicited howls of protest from relational purists.

With Microsoft’s SQL Server 2005 on deck, look for the same debate to be rekindled. Not only does SQL Server 2005 boast considerably broadened BI features, but also—like Oracle—native XML and Web services support. Both features should make SQL Server 2005 a more transparent and interoperable partner for a host of different application requirements, particularly for BI. Both, as well, are seen as mixed blessings of a sort by some IT pros.

Microsoft, for its part, says that SQL Server 2005’s support for native XML and Web services connectivity are customer-driven enhancements.

“We integrated in a native XML data type — [so] you can put XML in and get XML out natively,” says Tom Rizzo, director of SQL Server product management with Microsoft. He notes that SQL Server 2005 features native support for the still-incomplete XML Query language (XQuery) standard, adding, “A lot of people use XML as the lingua franca of interoperability — at the data level.”

This isn’t to say all adopters will celebrate SQL Server 2005’s native support for XML as a vast improvement over SQL Server 2000, for which Microsoft provided an add-on XML implementation, enabled by means of “shredding” (or sorting) XML tag components into relational table columns.

Take Adam Machanic, a Microsoft SQL Server MVP and database software engineer with a telecommunications and broadband services provider based in the Northeast who expresses doubts about native XML support. In light of the pros and cons of such a move, says Machanic, SQL Server 2000’s non-native XML implementation might be something of an ideal compromise.

“XML support in databases is at once a hot topic and a hotly debated topic. XML provides the ability to more quickly write application interop[erability] logic— but the penalty is data integrity. It's more difficult with XML than with relational structures to express constraints and data rules,” he says. “SQL Server 2000's support, in my opinion, is quite good. The ability to easily ‘shred’ XML into relational structures using the OPENXML function, and the ability to re-constitute the XML using the FOR XML clause are powerful features.”

More to the point, Machanic asks, why use a descriptive language such as XML when SQL—the structured query language—was designed from the ground up for querying?

“SQL Server 2005 builds on these [SQL Server 2000 XML] features greatly, but I still don't know how I feel about storing XML natively. If the data is something to be queried, I question why relational structures aren't appropriate.”

Connectivity is Key

On the Web services front, Microsoft’s Rizzo says that dropping a native HTTP Web services listener into SQL Server is a no-brainer. Not only do Web services make it easier to expose stored procedures and other database logic, he argues, but Web services connectivity also makes SQL Server a more interoperable player in the highly heterogeneous environments of today.

“We’ve made it so that SQL Server can natively expose Web services, so you can create a stored procedure and say, ‘SQL [Server], expose this as a Web service,’ [and] the SQL Server automatically understands how to create a WSDL so you can call it from Linux [or] Unix,” he explains.

This has enormous BI ramifications. For starters, native Web services support makes it much easier for programmers to incorporate (more or less transparently, at that) SQL Server BI services into custom-built or third-party applications. Need to add text-mining capabilities to a new customer-facing application? Then SQL Server 2005 is your database. Secondly, it’s conceivable that some cutting-edge developers will exploit this connectivity to cobble together virtual applications—that is, apps that consist of little more than OLAP, ETL, reporting, or other SQL Server BI services working in orchestration with one another.

“I can see that this will be useful in some solutions, particularly among converts to service-oriented architecture (SOA) who want to treat the database (and, indeed, everything else) as a ‘black box’ service,” says SQL Server professional Graeme Malcolm, a principal technologist with technical training and content development specialist Content Master. “To an extent, you can do this in SQL Server 2000 with SQLXML 3.0, but it requires IIS—so the native use of HTTP.sys just makes it easier to set up and manage.”

There’s an enormous upside to native Web services connectivity, but some SQL Server pros see a downside, too. “Web services straight from the database seems like an opening for trouble to me,” says Machanic. “I'll have to very carefully analyze security and performance implications before deciding how best—or whether—to use this feature.”

Moot Point

Content Master’s Malcolm suggests that much of the debate over both native XML and Web services connectivity misses the point. There are definite benefits associated with both approaches, he says, and for this reason alone, their presence in the relational data store of today is a fait accompli.

“I guess one of the most important shifts in application design since the SQL Server 2000 timeframe is the increased importance of XML as a way of structuring data,” he says, adding that when SQL Server was released, “the predominant thinking — seems to have been that you might want to transmit data as XML, but you'll always want to store it in regular tables and columns.”

Things have changed a lot since then, says Malcolm. “I think that as XML has become a core technology in many application designs, the ‘two world’" approach is no longer relevant for a lot of people. In many cases, it makes sense to actually store some kinds of data as XML—particularly when the solution needs to be more flexible about how ‘structured’ the data entities are. ”As a result, Malcolm, for one, is excited about the changes that SQL Server 2005 brings to the table on both accounts. “[T]he exciting things for me are the improvements in XML support in SQL Server 2005—particularly the native XML data type with associated querying methods,” he observes, noting that Microsoft’s decision to drop XQuery from its .NET Framework 2.0 is nevertheless something of a disappointment. “I understand the rationale behind the decision, but I think it's a really useful technology for people who need to extract data from XML, and it seems odd to have a subset of XQuery in SQLServer 2005 but not in the framework.”