MSXML SAX2 Help

kwilliams · December 15th, 2005, 06:57 PM

With the help of a great article titled "Exporting SQL Data as XML - ADO and DOM" at http://www.perfectxml.com/articles/X...rtSQLXML.asp#2, I've created an ActiveX Script that basically pulls data from a SQL Server 2000 DB table, and exports it out to an XML file. Here it is:

Code:

'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************
Function Main()
    Dim objADORS
    Dim objXMLDoc

    Dim nodeRoot
    Dim nodeTemp
    Dim nodeRelease

    'Create ADO and MSXML DOMDocument Objects
    Set objADORS = CreateObject("ADODB.Recordset")
    Set objXMLDoc = CreateObject("MSXML2.DOMDocument.3.0")

    'Run the stored procedure and load the Recordset
    objADORS.Open "SELECT * FROM tblTABLENAME WHERE (start_date < GETDATE()) AND (end_date > GETDATE())", _
        "PROVIDER=SQLOLEDB.1;SERVER=SERVERNAME;UID=UID;PWD=PWD;DATABASE=DBNAME;" 

    'Prepare the XML Document
    objXMLDoc.loadXML "<root />"
    Set nodeRoot = objXMLDoc.documentElement

    'For each record in the Recordset
    While Not objADORS.EOF

        Set nodeRelease = objXMLDoc.createElement("release")
        nodeRoot.appendChild nodeRelease

        Set nodeTemp = objXMLDoc.createElement("release_id")
        nodeTemp.nodeTypedValue = Trim(objADORS.Fields("release_id").Value)
        nodeRelease.appendChild nodeTemp

        objADORS.moveNext
    Wend

    objADORS.Close
    Set objADORS = Nothing

    'Save the created XML document
    objXMLDoc.Save "\\SERVERNAME\dbdata.xml"

    Main = DTSTaskExecResult_Success
End Function

It worked great until I ran the job through a scheduled DTS package. I quickly found out why it's not that efficient to use DOM over SAX for this process. The article suggested using SAX (more specifically MSXML SAX2) instead of DOM.

So now I'd like to see what it would take to create an ActiveX Script using MSXML SAX2 for this process. This is what I have so far:

Code:

'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************
Function Main()
    Dim ObjADORS
    Dim objWriter 
    Dim saxContentHandler
    Dim objADOField
    Dim strFldName
    Dim objAttributes 
    Dim objRSFields

    Set ObjADORS  = Server.CreateObject("ADODB.Recordset")
    Set objWriter = Server.CreateObject("MSXML2.MXXMLWriter.4.0")
    Set objAttributes = Server.CreateObject("Msxml2.SAXAttributes.4.0")

    'Run the stored procedure and load the Recordset
    objADORS.Open "SELECT * FROM tblTABLENAME WHERE (start_date < GETDATE()) AND (end_date > GETDATE())", _
        "PROVIDER=SQLOLEDB.1;SERVER=SERVERNAME;UID=UID;PWD=PWD;DATABASE=DBNAME;" 

    'Prepare MXXMLWriter object
    Set saxContentHandler = objWriter
    objWriter.indent = True
    objWriter.standalone  = True

    'Send the writer output to the ASP response stream
    objWriter.output = Response

    'Generate SAX events
    saxContentHandler.startDocument
    saxContentHandler.startElement "", "", "root", objAttributes

    Set objRSFields = ObjADORS.Fields

    'For each record
    While Not ObjADORS.EOF
        'Create ShipperRecord element
        saxContentHandler.startElement "", "", "release", objAttributes

        'For each field
        For Each objADOField In objRSFields
            'Create the element with the same name as the field name
            release_id =  objADOField.Name

            saxContentHandler.startElement "", "", release_id, objAttributes
            saxContentHandler.characters CStr(objADOField.Value)
            saxContentHandler.endElement "", "", release_id
        Next
        ObjADORS.MoveNext

        saxContentHandler.endElement "", "", "release"
    Wend

    saxContentHandler.endElement "", "", "root"
    saxContentHandler.endDocument

    ObjADORS.Close 
    Set ObjADORS = Nothing

    'Save the created XML document
    objXMLDoc.Save "\\SERVERNAME\dbdata.xml"

    Main = DTSTaskExecResult_Success
End Function

...but I'm receiving this error message:
Error Code: 0
Error Source=Microsoft VBScript runtime error
Error Description: Object required: 'Server'
Error on Line 13

If anyone could give me some advice on the best way to set this up using MSXML SAX2, or could let me know what I'm doing wrong with my latest attempt, that would be great. Thanks.

KWilliams