Manage Learn to apply best practices and optimize your operations.

Bringing XML and SQL Server closer

In this XML Developer tip, Ed Tittel discusses the potential benefits of SQLXML.

Developers who work with multiple kinds of data in any kind of volume typically find they must use databases to access relational data and XML to access document data. For example, report generators, or enterprise resource planning applications, work mostly with relational databases, yet repackaging that data for interaction with suppliers or customers typically requires working with one or more XML applications.

Developers in the Microsoft camp deal mostly with SQL Server, the.NET Framework, Visual Studio and various related programming languages. And although SQL Server 2000 incorporated basic XML functionality, it's often necessary to boost client-side capabilities with tools like the free SQLXML.

In a nutshell, SQLXML supports the ability to create an XML view of relational data, and to interact with it as if it were an XML file. Thus, SQLXML permits the following kinds of operations against SQL Server 2000 databases:
  • Use XPath for queries.
  • Use Transact-SQL for queries, but return XML results.
  • Update relational data the same way you'd update XML.
  • Convert XML data into relational data, then load it into an existing SQL Server 2000 database.
  • Use URLs from a Web browser or application to query SQL Server.
  • Use OLEDB/ADO to access XML functions in SQL Server.
  • Use .NET Framework classes to access XML functions in SQL Server.

Although these are useful and effective, such contortions will no longer be required in the forthcoming release of SQL Server 2005.

SQL Server 2005 will support a broad range of native XML support, without requiring extra add-ins or transforms. The new release includes instructions like OpenXML, a command that takes XML documents and converts them into tabular format for database storage (called shredding, in SQL Server lingo), and FOR XML, a tool that permits relational data to be published as XML on-demand.

The new version of SQL Server will also apply native database indexes, constraints and SQL queries to XML data, and will allow tabular views of XML document data without requiring an intermediate conversion tool or step. This combined capability adds a lot of flexibility and power to SQL server since relational formats are great for representing data that is regular in structure, and comes many instances. But XML's representational power lets it capture the nuances of all kinds of information that may neither be terribly regular, nor allow extremely large numbers of instances to exist. This includes hierarchical data (a notorious pain to represent and navigate in purely relational terms), unstructured data (especially most kinds of text documents) and extremely complex document or record structures.

It will be interesting to see how this great marriage of capabilities is exploited in applications based on SQL Server 2005. It will also be interesting to see if XML support helps make databases a more natural place to store documents and other more unstructured forms of data. But one thing's for sure: There's a lot of potential for this powerful pairing.

Ed Tittel is a full-time writer and trainer whose interests include XML and development topics, along with IT Certification and information security topics. E-mail Ed at with comments, questions, or suggested topics or tools for review.

Dig Deeper on Topics Archive

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.