articles >> performance tuning >> Use OPENXML to Minimize SQL Server Database ...

Use OPENXML to Minimize SQL Server Database Calls

By : Jay Buchta Mar 25, 2001

Page 2 / 2


At this point, you need to create the XML string. This can be created by using the DOM or by directly creating the XML string in a string variable. The example above uses the DOM to create a root element node<insert>, and a child element node<answers>. Three attributes are created in <answers> --> evalid, qnum, and answer. After the <insert> node is created and appended, the code spins through the array and adds an <answers> node with its attributes for each ordinal question.

The XML representation of the node and all its descendants is retrieved from the .xml property of the XML document you just created.

 

Listing 3 - XML Created

<insert>
<Answers EvalID="84" QNum="1" Answer="Good"/>
<Answers EvalID="84" QNum="2" Answer="Fair"/>
<Answers EvalID="84" QNum="3" Answer="Very Good"/>
<Answers EvalID="84" QNum="4" Answer="Excellent"/>
<Answers EvalID="84" QNum="5" Answer="Good"/>
<Answers EvalID="84" QNum="6" Answer="Good"/>
<Answers EvalID="84" QNum="7" Answer="Fair"/>
<Answers EvalID="84" QNum="8" Answer="Very Good"/>
<Answers EvalID="84" QNum="9" Answer="Excellent"/>
<Answers EvalID="84" QNum="10" Answer="Good"/>
</insert>

This string is passed to a database component function to handle the inserts into the database. This component also resides in the middle tier compiled as a VB ActiveX.dll. Here is the code for the database function:

 

Listing 4 – VB COM Database Function (ADO)

Public Function SaveXMLResponse(ByVal strXML As String) As Boolean
Dim prmXML As ADODB.Parameter

On Error GoTo SaveXMLResponse_Err

Set com = New ADODB.Command
Set prmXML = New ADODB.Parameter
If cn Is Nothing Then
StartConnection
End If
With com
.ActiveConnection = cn
.CommandText = "SaveXMLresponse"
.CommandType = adCmdStoredProc
Set prmXML = .CreateParameter("@evaldata", adVarChar, adParamInput, 4000, strXML)
.Parameters.Append prmXML
.Execute,,adExecuteNoRecords
End With

SaveXMLResponse = True

Cleanup:
Set prmXML = Nothing
Set com = Nothing
Set cn = Nothing

Exit Function

SaveXMLResponse_Err:
SaveXMLResponse = False
GoTo Cleanup
End Function

This is pretty straightforward as all you are doing is instantiating a Command object with one parameter, our XML string, and executing the stored procedure call.

The stored procedure called looks like this:

 

Listing 5 – SQL Server 2000 Stored Procedure

CREATE PROC saveXMLresponse
@evaldata varchar(4000) 
AS 
DECLARE @hDoc int 
exec sp_xml_preparedocument @hDoc OUTPUT,@evaldata 

INSERT INTO Answers
SELECT *
FROM OPENXML (@hDoc,'/insert/Answers')
WITH Answers


EXEC sp_xml_removedocument @hDoc
GO

You can see that the only parameter passed to the procedure is the XML passed as a varchar. Depending on the size of the XML string you are working with, the XML string input parameter can be (n)char or (n)text in addition to (n)varchar. [Note: Use the (n) when working with Unicode data]. The @hDoc variable is required by the sp_xml_preparedocument as an output parameter. Sp_xml_preparedocument is a SQL Server system stored procedure that creates an internal representation of the XML document passed to it, and returns this document handle in @hDoc.

The OPENXML function accepts three arguments, the first two of which are required. The first argument is the document handle that you created by calling sp_xml_preparedocument. This tells OPENXML which XML document you are working with. The second argument is an XPATH (XML Path Language) pattern used to identify the nodes in the XML document. XPATH is a W3 (World Wide Web Consortium) navigation standard. They define XPATH as a language for addressing parts of an XML document.

Each node identified by the XPATH pattern corresponds to a single row in the rowset generated by OPENXML. In our example, there are 10 <answers> nodes each representing a row in the rowset. The third argument is optional and specifies how the mapping should occur between the rowset created by OPENXML and the XML document. The default is attribute-centric, which means XML attributes of a given name are stored in a column in the rowset with the same name. In the example, the XML document attributes EvalID, Qnum, and Answer are stored in the rowset representation with the same names. Therefore, the rowset looks something like:


EvalID Qnum Answer
84     1    Good
84     2    Fair
84     3    Very Good
84     4    Excellent
84     5    Good
84     6    Good
84     7    Fair
84     8    Very Good
84     9    Excellent
84     10   Good


The WITH clause allows you to specify a Schema declaration (to specify additional mapping between a column in the rowset and a value in the XML document) or the table name if the table already exists with the desired schema.

The example does a simple insert into the Answers table, and since the XML document was created specifically to insert multiple rows into the Answers table, it is sufficient to specify the table name Answers in our WITH clause.

You can specify a schema declaration to accomplish the same mapping in the example as follows:

INSERT INTO Answers
SELECT *
FROM OPENXML (@hDoc,'/insert/Answers')
WITH (EvalID int,
Qnum int,
Answer varchar(20))

The last statement in Listing 5, EXEC sp_xml_removedocument @hDoc, is called to remove the XML document from it’s storage location in the internal cache of SQL Server.

In summary, the new OPENXML function in SQL Server 2000 can be useful for processing multiple table inserts within a single database call. The ability to map an XML document to a rowset representation of a specified portion of the XML document within a stored procedure can maximize the efficiency with which repetitive type inserts are accomplished.



Published with the express written permission of the author. Copyright 2000 Jay Buchta