MSXML SAX2 Help - SQL Server to XML

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

MSXML SAX2 Help - SQL Server to XML

Author

Topic

kwilliams

194 Posts

Posted - 2005-12-15 : 17:58:51

With the help of a great article titled "Exporting SQL Data as XML - ADO and DOM" at http://www.perfectxml.com/articles/XML/ExportSQLXML.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:

'**********************************************************************
'  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:

'**********************************************************************
'  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
-------------------
It's the end of the world as we know it...and I feel fine

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources