ActiveX help - SQL Server Forums

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)

ActiveX help

Author

Topic

yaaadman
Starting Member

4 Posts

Posted - 2007-12-04 : 13:45:05

I am trying to parse an xml file and grab a few fields and insert the data into a table. I am using DTS for this. I ceate a ActiveX Script Task for this and I believe that I am having a problem with XML Namespaces since objNodes.length is returning 0. Please see code below. Any help would be appreciated. Thanks,

XML FILE: http://www.newyorkfed.org/xml/schemas/FX_Utility.xsd

'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
CONST strCurFileName = "C:\NoonRates.xml" ' "\\chimssql01\DWFiles"
CONST adOpenKeyset = 1
CONST adLockOptimistic = 3

Function Main()

' MSXMLDOM objects
Dim objXMLDOM
Dim objNodes
Dim objNodeItem

' ADO objects
Dim objADORS
Dim objADOCnn

'Create and initialize (Open) ADO Connection
Set objADOCnn = CreateObject("ADODB.Connection")
objADOCnn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=GRCStage;Data Source=CHIMSSQLDEV;"

'Create MSXML 4.0 DOM Object and initialize it
Set objXMLDOM = CreateObject("MSXML2.DOMDocument.4.0")
objXMLDOM.async = False
objXMLDOM.validateOnParse = False
objXMLDOM.setProperty "SelectionLanguage", "XPath"
objXMLDOM.setProperty "SelectionNamespaces", "xmlns:utility='http://www.SDMX.org/resources/SDMXML/schemas/v1_0/utility' xmlns:frbny='http://www.newyorkfed.org/xml/schemas/FX/utility'"

'Load the XML file
objXMLDOM.load strCurFileName

Set objNodes = objXMLDOM.selectNodes("frbny:DataSet/frbny:Series")

MsgBox objNodes.length

'Create and Open the recordset
Set objADORS = CreateObject("ADODB.Recordset")
objADORS.Open "SELECT * FROM ExchangeRates WHERE 1 = 2", objADOCnn, adOpenKeyset, adLockOptimistic

'Add records
For Each objNodeItem In objNodes
With objADORS
.AddNew

.fields("Time_Format") = objNodeItem.selectSingleNode("TIME_FORMAT").nodeTypedValue
.fields("UNIT") = objNodeItem.selectSingleNode("UNIT").nodeTypedValue

.Update
End With
Next

objADORS.Close

objADOCnn.Close

Set objADORS = Nothing
Set objADOCnn = Nothing
Set objXMLDOM = Nothing
Set objFSO = Nothing

Main = DTSTaskExecResult_Success
End Function

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources