sql server data warehouse: XML-load via DTS..? -- sql server data warehouse

XML-load via DTS..?

Kent Johnson
10/31/2004 6:55:52 PM

sql server data warehouse:

Hi all,
I have tried to load the below data into a SQL-table using a DTS-active
script package.
The data:
================================
<?xml version="1.0" encoding="iso-8859-1" ?>
<BusinessPartners_Update>
<Body>
<BusinessPartner>
<Customer>
<BusinessPartnerNumber>001</BusinessPartnerNumber>
<BusinessPartnerName>Name1</BusinessPartnerName>
</Customer>
<OrganisationNumber>5565412345</OrganisationNumber>
<AccountGroup>Z010</AccountGroup>
<LegalStatus>1</LegalStatus>
<Address>
<Street>StreetName</Street>
<AddressPostalCode>123 45</AddressPostalCode>
<City>CityName</City>
<CountryKey>US</CountryKey>
</Address>
</BusinessPartner>
</Body>
</BusinessPartners_Update>
===================================
DTS-package:
I just want to insert 1 record with two columns into TblCustomer.

'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()

Dim objXMLDOM
Dim objNodes
Dim objBookNode

Dim objADORS
Dim objADOCnn

Const adOpenKeyset = 1
Const adLockOptimistic = 3

Set objXMLDOM = CreateObject("MSXML2.DOMDocument.4.0")
objXMLDOM.async = False
objXMLDOM.validateOnParse = False

'No error handling done
objXMLDOM.load "F:\File1.xml"

Set objNodes =
objXMLDOM.selectNodes("/BusinessPartners_Update/Body/BusinessPartner/Address
")

Set objADOCnn = CreateObject("ADODB.Connection")
Set objADORS = CreateObject("ADODB.Recordset")

objADOCnn.Open
"PROVIDER=SQLOLEDB;SERVER=.;UID=sa;PWD=pwd;DATABASE=NorthWind;"
objADORS.Open "SELECT * FROM TblCustomer WHERE 1 = 2", objADOCnn,
adOpenKeyset, adLockOptimistic

For Each objBookNode In objNodes
With objADORS
.AddNew
.fields("Street") = objBookNode.selectSingleNode("Street").nodeTypedValue
..fields("City") = objBookNode.selectSingleNode("City").nodeTypedValue
.Update
End With
Next
objADORS.Close
objADOCnn.Close

Main = DTSTaskExecResult_Success

End Function
=====================================

When I try to run the above code I'll get:
Error description: Object required: 'ObjBookNode.SelectSingleNode(..)'

/Kent J.

Re: XML-load via DTS..?

aaron kempf
11/23/2004 6:12:07 PM

you can either push that XML into one column- like lazy people do-- or you
can break each field into it's own column.

i would reccomend the lazy way for now, since SQL 2005 is going to make this
a lot easier... even if you use SQL Server Express 2005, it should be really
nice to have this data in a single field. in a db, and i think that you
could deal with it a lot easier there.

[quoted text, click to view]