SQLXML Data Provider Tips

In case you haven’t heard, the SQLXML .Net managed provider is built on top of OleDb, so unless you absolutely need to use SQL2K’s XML templates and server-side XPath queries I’d recommend staying away from the SQLXML .Net provider.  For some reason I never noticed this little fact until I was re-reading Dino Esposito’s Applied XML Programming for Microsoft .Net.  I double checked the newsgroups, and sure enough, the SQLXML managed classes are just wrappers for SQLXMLOLEDB.  I never liked the fact that a bunch of the XML enhancements to SQL Server 2K were not in the SQL Server DB engine, and where in IIS instead.  I know that Oracle does a similar thing, but I didn’t like it when it came out, and I still don’t like it now.  And there doesn’t seem to be any plans to update the SQLXML provider until Yukon.

One other thing of interest I found in Dino’s book.  If you use the System.Data.SqlClient.SqlCommand object’s ExecuteXmlReader (for reading SQL2K queries that use For XML), the connection to the DB remains open as long as the XmlReader is open.  If you want a disconnected version, use the DataSet object.

DonXML

[Listening to: The Hard Way - Bowling For Soup ] 

2 Comments

  • while i agree that the lack of full managed class for sqlxml is bad thing, there are some uses for the product other then navigating your data using xpath. transporting the data either by http or soap can aid some shops that need to get data from sql to a non-windows app.





    there was a presenter at tech-ed that had some beta sp2 bits on cd with him. so that maybe the only update coming until Yukon's native XML data type.


  • Bear in mind that the DataSet is really quite heavyweight; as an alternative, you could drag the data from the XmlReader into a cache of some kind (in memory, on disk, whatever takes your fancy), and provide an XmlReader on that instead. You get to keep your native Xml (particularly important if your schema requires features that dataset does not support), and you pay a smaller "in memory" penalty than a data set, even if you go for an in-memory cache as an XmlDocument.

Comments have been disabled for this content.