SQLXML is an addition to SQL Server that adds a number of XML features to the database. For example, in SQLXML 3.0, which shipped with SQL Server 2000, provided features like SqlXmlAdapter, SqlXmlCommand, SqlXmlParameter, and so forth.
Bobb Beauchemin, who spoke at many of the SQLMag Roadshows and keeps a fantastic blog at http://www.SQLskills.com/blogs/bobb, describes it this way:
It was one the "original" ways to use XML in SQL Server. This was originally a shim over the XML parts the SQLOLEDB provider (SQLXMLX.dll). I think in 4.0 it was rewritten in native code. So its there for backward compatibility mostly.It's also the only way to use the original "XPath with annotated schemas to query SQL Server" (popular in SQL Server 2000) and an easy way to use "get SQL as XML, and apply a stylesheet" (the stylesheet is a property on SqlXmlCommand I beleive \[sic\]). Also, if you've ever tried to access the "FOR XML" output in any other API before SQL Server 2005, you'd appreciate the "Root" property.
A related feature, SqlXml bulk load through client API, is still sometimes the fastest way to load a database with XML, even with the "BULK" provider in SQL Server 2005.
Michael Rys, the Microsoft program manager responsible for SQLXML, added to Bob's list with these bullets for SQLXML 4.0:
1. Provide support for annotated schemas (XPath, updategrams and bulkload), client-side FOR XML and SQLXML templates against SQL Server 2005 through native and managed providers (ie. ExecuteXMLReader).
2. Role up the web releases into something that ships with SQL Server.
3. Remove ISAPI (don't know why though).
If you ask me personally, I find the SQLXML Bulkload object and the SQLXML templates the most useful which have no simple equivalence.
So there are a few reasons to use SQLXML in general and SQLXML 4.0 in particular. Let me know what you think.