This chapter is from the book
This chapter is from the book
This chapter is from the book
Despite MSXML's power and ease of use, SQL Server doesn't leverage MSXML in all of its XML features. It doesn't use it to implement server-side FOR XML queries, for example, even though it's trivial to construct a DOM document programmatically and return it as text. MSXML has facilities that make this quite easy. For example, Listing 18.2 presents a Visual Basic app that executes a query via ADO and constructs a DOM document on-the-fly based on the results it returns.
Listing 18.2
Private Sub Command1_Click()
Dim xmlDoc As New DOMDocument30
Dim oRootNode As IXMLDOMNode
Set oRootNode = xmlDoc.createElement("Root")
Set xmlDoc.documentElement = oRootNode
Dim oAttr As IXMLDOMAttribute
Dim oNode As IXMLDOMNode
Dim oConn As New ADODB.Connection
Dim oComm As New ADODB.Command
Dim oRs As New ADODB.Recordset
oConn.Open (Text3.Text)
oComm.ActiveConnection = oConn
oComm.CommandText = Text1.Text
Set oRs = oComm.Execute
Dim oField As ADODB.Field
While Not oRs.EOF
Set oNode = xmlDoc.createElement("Row")
For Each oField In oRs.Fields
Set oAttr = xmlDoc.createAttribute(oField.Name)
oAttr.Value = oField.Value
oNode.Attributes.setNamedItem oAttr
Next
oRootNode.appendChild oNode
oRs.MoveNext
Wend
oConn.Close
Text2.Text = xmlDoc.xml
Set xmlDoc = Nothing
Set oRs = Nothing
Set oComm = Nothing
Set oConn = Nothing
End Sub
As you can see, translating a result set to XML doesn't require much code. The ADO Recordset object even supports being streamed directly to an XML document (via its Save method), so if you don't need complete control over the conversion process, you might be able to get away with even less code than in my example.
As I've said, SQL Server doesn't use MSXML or build a DOM document in order to return a result set as XML. Why is that? And how do we know that it doesn't use MSXML to process server-side FOR XML queries? I'll answer both questions in just a moment.
The answer to the first question should be pretty obvious. Building a DOM from a result set before returning it as text would require SQL Server to persist the entire result set in memory. Given that the memory footprint of the DOM version of an XML document is roughly three to five times as large as the document itself, this doesn't paint a pretty resource usage picture. If they had to first be persisted entirely in memory before being returned to the client, even moderately large FOR XML result sets could use huge amounts of virtual memory (or run into the MSXML memory ceiling and therefore be too large to generate).
To answer the second question, let's again have a look at SQL Server under a debugger.
Exercise 18.2 Determining Whether Server-Side FOR XML Uses MSXML
-
Restart your SQL Server, preferably from a console since we will be attaching to it with WinDbg. This should be a test or development system, and, ideally, you should be its only user.
-
Start Query Analyzer and connect to your SQL Server.
-
Attach to SQL Server using WinDbg. (Press F6 and select sqlservr.exe from the list of running tasks; if you have multiple instances, be sure to select the right one.) Once the WinDbg command prompt appears, type g and press Enter so that SQL Server can continue to run.
-
Back in Query Analyzer, run a FOR XML query of some type:
SELECT * FROM ( SELECT 'Summer Dream' as Song UNION SELECT 'Summer Snow' UNION SELECT 'Crazy For You' ) s FOR XML AUTO
This query unions some SELECT statements together, then queries the union as a derived table using a FOR XML clause.
-
After you run the query, switch back to WinDbg. You will likely see some ModLoad messages in the WinDbg command window. WinDbg displays a ModLoad message whenever a module is loaded into the process being debugged. If MSXMLn.DLL were being used to service your FOR XML query, you'd see a ModLoad message for it. As you've noticed, there isn't one. MSXML isn't used to service FOR XML queries.
-
If you've done much debugging, you may be speculating that perhaps the MSXML DLL is already loaded; hence, we wouldn't see a ModLoad message for it when we ran our FOR XML query. That's easy enough to check. Hit Ctrl+Break in the debugger, then type lm in the command window and hit Enter. The lm command lists the modules currently loaded into the process space. Do you see MSXMLn.DLL in the list? Unless you've been interacting with SQL Server's other XML features since you recycled your server, it should not be there. Type g in the command window and press Enter so that SQL Server can continue to run.
-
As a final test, let's force MSXMLn.DLL to load by parsing an XML document. Reload the query from Exercise 18.1 above in Query Analyzer and run it. You should see a ModLoad message for MSXML's DLL in the WinDbg command window.
-
Hit Ctrl+Break again to stop WinDbg, then type q and hit Enter to stop debugging. You will need to restart your SQL Server.
So, based on all this, we can conclude that SQL Server generates its own XML when it processes a server-side FOR XML query. There is no memory-efficient mechanism in MSXML to assist with this, so it is not used.