"This time, I would take a quick but a thoughtprovoking aspects of what happens with XML knits with a database inMicrosoft World."
Extensible Stylesheet Language Shape ups (XSLT)When companies exchange data, their schemas will more than likely beinconsistent. One company might use CustomerID as an element name whileanother might simply use ID. Extensible Stylesheet Language Shape up(XSLT) represents an excellent mechanism for shaping up XML into avariety of structures, including other forms of XML. Using the .NETplatform, it's fairly straightforward to shape up an XML document intoa structure that can then be loaded into a DataSet object that containsthe database schema. Using one of the .NET-managed providerDataAdapters, the XML can then be updated into the appropriate databasetables.
Plus:
Suppose we're comfortable with XSLT, thismight be the quickest way to achieve the goal of moving XML into adatabase, especially given the strengths of the DataSet class. Onceloaded with the correct XML data, we can easily add the DataSet'scontents to a database using a DataAdapter. If the source XML documentchanges, we can just edit the XSLT stylesheet to reflect the changeswithout having to recompile code.
Minus:
XSLT loads XML documents (and thecorresponding XSLT stylesheets) into memory during the shape upprocess. Though we can minimize some of the memory impact by using theappropriate classes (the XPathDocument class instead of the XmlDocumentclass, for instance), we might run into scalability problems when largeXML documents are involved.
DataSet/DataAdapter Mapping
The DataSet is an extremely powerful objectthat is part of the ADO.NET classes found in the .NET platform. Unlikeclassic ADO Recordsets, DataSets hold multiple tables and theirassociated rows and columns. We can even create relationships betweenthe tables. DataSets do not interact directly with the database.Instead, they go through DataAdapters.
The DataSet object can be used withDataAdapter, DataTableMapping, and DataColumnMapping classes to map thesource XML data to the proper database tables and fields. Once themapping is complete, we can load the XML into a DataSet and update thedatabase using the DataAdapter.
Plus:
We get a lot of the behind-the-scenes plumbingdone for we with this technique, which cuts development time and makesthe application more maintainable. Visual Studio .NET provides a GUIinterface to make generating the mapping code easier.
Minus:
When working with complex hierarchicalrelationships in XML documents, the mapping process can become quitecomplex unless we're using a visual tool such as Visual Studio .NET.Changes to the source XML document will more than likely result in codeneeding to be recompiled and distributed to accommodate the changes.
The DOM
The Document Object Model (DOM) can be achoice to move the XML into a database. By Using the XmlDocumentclasses along with XPath, specific nodes/node values can be found inthe XML document and SQL statements can be dynamically instantiated tobe executed against the database.
Plus:
The DOM is easy to access and manipulate oncewe understand the different DOM objects and their associated propertiesand methods. Finding any node within an XML document is easy using theXPath language along with the XmlDocument's SelectSingleNode() andSelectNodes() methods.
Minus:
This is not a scalable solution because everyXML document should be loaded into memory. For small applications thissolution might seem viable. However, what happens if the "small" XMLdocuments stay small but the number of documents to be parsed increasesover time? Time to buy more RAM! If we absolutely have to use the DOMfor some reason and need to use XPath, I'd recommend looking at theXPathNavigator class rather than the XmlDocument class because ofdifferences in how the DOM structure is loaded into memory.
The XmlTextReader Class
The XmlTextReader class is a forward-onlystream having XML tokens that can be parsed quickly and efficiently.It's a highly scalable solution capable of working with large XMLdocuments without tying up huge amounts of server memory (as with theDOM).
Plus:
The XmlTextReader is a stream, so it uses aminimum amount of memory while parsing the XML document. Different XML"tokens" found in the stream can be pulled as necessary to access dataor check node types. The ability to "pull" nodes increases this class'sability to scale well.
Minus:
Though simple to use, the XmlTextReader mighttake more time to get comfortable with compared to other classes suchas those described in the DOM section. The XmlTextReader represents aforward-only stream, so random access to nodes in the XML document isnot an option as with the DOM or XSLT. Also, if the XML documentchanges, we might have to recompile the code depending on how theapplication is designed.
Microsoft SQLServer 2000
The Microsoft SQLServer 2000 development teamprovides "Web releases" of different XML-related tools used with thisvery XML-enabled database. The latest Web release has specific classeswe can use with .NET applics to move XML into database tables. Evenwithout these new classes, however, Microsoft SQLServer 2000 comes withseveral XML features that can aid this process including technologiessuch as DataGrams and OPENXML.
Plus:
Microsoft SQLServer 2000 is arguably the mostXML-enabled database available today. We can retrieve data as XMLthrough SQL queries, XML templates, and stored procedures or move XMLinto a database using OPENXML "document shredding" techniques,DataGrams, and other technologies. With the release of the latestMicrosoft SQLServer 2000 Web release tools, the options for integratingXML into database tables increase even more because new .NET classesare available to aid in this process.
Minus:
Well, we have to have Microsoft SQLServer 2000to use it. To use the new Microsoft SQLServer 2000 .NET classes, we'llhave to install the Web release, which may or may not be an option forus.
Happy .NETing !
