Developer .NET UPDATE--Integrating XML Data--January 7, 2005

This Issue Sponsored By
This email newsletter comes to you free and is supported by the following advertisers, who offer products and services that might interest you. Please take a moment to visit these advertisers' Web sites and show your support for Developer .NET UPDATE.

Microsoft ASP.NET & Visual Studio Connections
http://www.devconnections.com

Bring in the New Year with SQL Server Magazine
https://secure.pentontech.com/nt/sql/index.cfm?promocode=tgep2151ds

1. Developer .NET Perspectives

2. Announcements

3. Resource

4. Events Central

5. New and Improved

Sponsor: Microsoft ASP.NET & Visual Studio Connections
March 20-23, 2005, Hyatt Grand Cypress Resort in Orlando. Don't miss this exciting event offering cutting-edge sessions by industry gurus. All attendees receive beta copies of SQL Server 2005 and Visual Studio 2005, and attend sessions at SQL Server Magazine Connections free!
http://www.devconnections.com

1. Developer .NET Perspectives

by Bill Sheldon, [email protected]

SQL Server 2005 Provides True Integration of XML Data
In my last article, I started discussing Microsoft SQL Server 2005's native XML data type. This new data type brings with it some of the most important core changes to how developers will interact with SQL Server. As I noted in my previous article, XML structures are excellent for managing hierarchical data relationships. Being able to incorporate XML data into a relational database provides tremendous flexibility.

However, just being able to drop XML data into the database wouldn't be sufficient if you couldn't integrate this data with other data in the database. For example, SQL Server 2000 provides limited support for XML as part of its query and insert capabilities, but this support stops outside the database. The XML data is either treated as a blob in the database or transformed into a set of relational structures. Fortunately, SQL Server 2005 lets you integrate XML data with other data in the database through several new features. More important, the introduction of a native type means that SQL Server 2005 truly supports XML.

Before leaving SQL Server 2000 behind in this discussion, I want to quickly look at the future for those of you who have embedded XML in your current data structures under SQL Server 2000. To store this data, you have undoubtedly used one of the current built-in types, such as Varchar. This workaround lets you save your XML data, but the individual fields within that XML structure are nothing more than blobs as far as the database is concerned. As part of your transition to SQL Server 2005, you can alter your table and designate these columns as being of the XML type. As long as you have well-formed XML structures, you'll be able to leverage all the features that I'm about to discuss.

At the individual column level, SQL Server 2005 supports the XML data type by exposing several methods that have been added to the T-SQL language. These new methods let you search, retrieve, and update data that's embedded within a larger XML structure. Each method is combined with a XML field to limit or update data associated with an XML column. The five new methods are query(), value(), exist(), nodes(), and modify().

The query() method lets you search through a larger XML structure to find a set of data based on an XML Query (XQuery) definition. The XQuery language is a World Wide Web Consortium (W3C) standard for searching or defining a set of XML nodes that meet a set of criteria. Most of the other methods also leverage XQuery conditions. For more information about XQuery, go to http://www.w3.org/tr/xquery.

Exist() is an optimized method that lets you screen XML data the same way you screen data with a relational WHERE clause. However, instead of retrieving a value from your XML data, then checking to see whether this value matches a condition, you pass the condition into the XML processor, then retrieve only those records that match the condition.

The value() method returns a specific value from within your XML structure. The limitation is that this value must be a single instance, such as a string or number. It can't be a subset (i.e., node) of your XML structure.

The nodes() method returns a subset of your XML structure in the form of a node. This result can then be used by other methods, such as exist() and value() to pull out repeating values that might be embedded within a single XML column. The key is that by using the XQuery syntax, which is native to XML, you can embed your queries against the data in a single relational column.

The last method I'd like to discuss is modify(). This method lets you insert and update values and nodes that are contained within an XML column. The modify() method accepts both INSERT and UPDATE statements not only for scalar values but also for entire subtrees. Thus, you can add specific child elements to a collection of items. By leveraging XQuery statements within each XML column command, you can manipulate the individual components contained within your custom XML structure.

SQL Server 2005 supports both untyped and typed XML columns. In an untyped XML column, there aren't any definitions for the structures contained in that column. Consequently, SQL Server 2005 needs more time to search for items located within it. Besides slow performance, you're limited to how much integration you can achieve with the data in an untyped XML column. I'll discuss this limitation in more depth in a future article.

When can you leave an XML column untyped? You should do so only under two circumstances. First, you can leave an XML column untyped when you're developing a table and query performance isn't an issue. Second, you can leave an XML column untyped when you have a complex XML structure and the speed of insertion is the most important consideration.

Typing an XML column involves associating an XML Schema Definition (XSD) with the XML column. SQL Server then uses the XSD to identify what specific XML elements should be part of your XML data. SQL Server 2005 goes beyond just ensuring that you have well-formed XML. It actually validates insertions against the schema. In addition, if you have typed XML columns, you can leverage SQL Server 2005's features for XML data. I'll review these features in my next article.

Sponsor: Bring in the New Year with SQL Server Magazine
For a limited time, subscribe to SQL Server Magazine and get 30% off the newsstand price! As a special BONUS, you will also receive the latest Top SQL Server Tips Guide FREE – includes over 60 helpful tips! In addition, you'll get free subscriber-only access to every article ever published in the magazine – online! That's over 5 years of exclusive articles, code listings, insider notes and savvy instructions on how to get the most out of SQL Server. Order now:
https://secure.pentontech.com/nt/sql/index.cfm?promocode=tgep2151ds

2. Announcements

(brought to you by SQL Server Magazine)

Insider Resources for SQLMag.com Users
Get access to the Web's most popular online SQL Server users' group: tens of thousands of articles, scripts, product reviews, FAQs, and help for administrators and developers. Get the help you need, when you need it.
http://www.sswug.org/url/2976

Get Access to Every SQL Server Magazine Article
Celebrate the New Year with the newest version of the SQL Server Magazine Master CD! You'll receive portable access to every SQL Server Magazine article ever written since 1999--online and in hand! As an added bonus, you'll also receive exclusive scripting and security content--a $30 value for free. Subscribe today:
https://secure.pentontech.com/nt/sql/index.cfm?promocode=cbep2851cd

3. Resource

Featured Thread: Problematic Backup Produces Error 3041
Forum member "dba wanna be" wrote an applet in Visual Basic .NET to update certain columns in a database's table. The update runs fine. However, the applet calls for a backup after the update. The backup processes for a short time but then returns the error message "3041: BACKUP failed to complete the command BACKUP DATABASE CEDNet TO DISK = 'D:Program FilesMicrosoft SQL ServerMSSQLBackupProductionReady.BAK' WITH INIT." The error message is identical in the Event Viewer and SQL error logs. The forum member is wondering what might be causing the error. Offer your advice and see what other people have said on SQL Server Magazine's Development forum at
http://www.windowsitpro.com/sqlserver/forums/rd.cfm?cid=1670&tid=128835

4. Events Central

(See the complete Windows IT Pro Network guide to Web and live events http://www.winnetmag.com/events)

SQL Server Summit in Dallas, TX - January 18, 2005
This year's summit features a jam-packed developer track with topics like: Programming SQL Server, the new SQL Server Integration Services, formerly DTS (delivered by Donald Farmer), Developing Data Apps for devices (SQLCE), Building Advanced SQL Server Reporting Services, and Developer's Perspective: Oracle to SQL. Call 1-877-MSEVENT or register online (reference event code: 1032256747) at:
http://www.microsoft.com/events

5. New and Improved

(by Karen Bemowski, [email protected])

Easily Manage .NET Development Projects
Logic Software announced Easy Projects .NET 2.9.1, a Windows Web-based project-management tool and project-tracking system for software development projects. With just a few clicks, you can examine your project's outstanding and completed tasks, resource allocations, open and fixed bugs, customer-request status, and employee and contractor timesheets. New features in this version include a customizable permission system that lets team members take different roles in different projects; reports about open bugs, delayed tasks, and upcoming tasks that are due; installer and application support for SQL Server and Windows authentication; and editable SMTP properties. Easy Projects .NET runs under Windows Server 2003/XP/2000 and requires Windows .NET Framework 1.1, Microsoft IIS 5.0, and Microsoft Access or Microsoft SQL Server 2000 or later. Prices start at $97. You can download a free, fully functional trial version or try the online demo. For more information, contact Logic Software at 416 352 1802 or [email protected].
http://www.logicsoftware.net

Contact Us

Developer .NET UPDATE is brought to you by SQL Server Magazine, the only magazine devoted to helping developers and DBAs master new and emerging SQL Server technologies and issues. Subscribe today.
http://www.sqlmag.com/rd.cfm?code=00ep214xeb

View the SQL Server Magazine Privacy Policy
http://www.winnetmag.com/aboutus/index.cfm?action=privacy

SQL Server Magazine is a division of Penton Media, Inc. 221 East 29th Street, Loveland, CO 80538, Attention: Customer Service Department

Copyright 2005, Penton Media, Inc. All Rights Reserved.