Ok..I am trying to import a large XML file. The script below does an
insert.. but it inserts all of the same nodes. I can't figure out what I am
doing wrong?? The general structure of the XML file is:

<products-announced>

<product-announced>

<title-info>

<title-name>10</title-name>

<studio> </studio>

......

</title-info>

<product-info>

<product-name>"10" (DVD)</product-name>

.....

<upc>012569200227</upc>

.....

</product-info>

</product-announced>

REPEAT



Dim objXMLDOM

Dim objNodes

Dim objTitleNode


Dim objADORS

Dim objADOCnn


Const adOpenKeyset = 1

Const adLockOptimistic = 3


Set objXMLDOM = CreateObject("MSXML2.DOMDocument.4.0")

objXMLDOM.async = False

objXMLDOM.validateOnParse = False



objXMLDOM.load "D:\FTPDest\WHV\allproducts.xml"

If (objXMLDOM.parseError.errorCode <> 0) Then

Dim myErr

Set myErr = objXMLDOM.parseError

MsgBox("You have error " & myErr.reason)

Main = DTSTaskExecResult_Failure

else

Set objNodes =
objXMLDOM.selectNodes("//products-announced/product-announced")


Set objADOCnn = CreateObject("ADODB.Connection")

Set objADORS = CreateObject("ADODB.Recordset")


objADOCnn.Open
"PROVIDER=SQLOLEDB;SERVER=vmcsql;UID=sa;PWD=;DATAB ASE=ProcessDB;"

objADORS.Open "SELECT * FROM WHVProducts WHERE 1 = 2", objADOCnn,
adOpenKeyset, adLockOptimistic




For Each objTitleNode In objNodes


With objADORS

..AddNew

..fields("Title") =
objTitleNode.selectSingleNode("//title-info/title-name").nodeTypedValue

..fields("UPC") =
objTitleNode.selectSingleNode("//product-info/upc").nodeTypedValue

..Update

'Set objTitleNode = objNodes.nextNode


End With

Next

objADORS.Close

objADOCnn.Close


Main = DTSTaskExecResult_Success

End If

Remove the // from the select node-

objTitleNode.selectSingleNode("title-info/title-name").nodeTypedValue

Why not use a simple insert query, which could also be parameterised for
performance?

Or

Why not use the SQL XML Bulk Load object ?


--
Darren Green
http://www.sqldts.com

"Stacey Levine" <staceyl (AT) SMAPFILTERmusicforasong (DOT) com> wrote

This was the first thing that I nearly got working. Not opposed to using
other methods, I even wanted to use OPENXML.. but could not figure out how
to use it with a file instead of string..

"Darren Green" <darren.green (AT) reply-to-newsgroup-sqldts (DOT) com> wrote

In message <O#HK8A06DHA.2752 (AT) TK2MSFTNGP09 (DOT) phx.gbl>, Stacey Levine
<staceyl (AT) SMAPFILTERmusicforasong (DOT) com> writes
For anything SQL + XML related try this site-

sqlxml.org home
(http://www.sqlxml.org/default.aspx)


HOW TO: Use OPENXML with an XML file.
(http://www.sqlxml.org/faqs.aspx?faq=39)


--
Darren Green

Ok.. tried as you suggested.. it returns an error..

"Darren Green" <darren.green (AT) reply-to-newsgroup-sqldts (DOT) com> wrote

Here is the script I used, same as yours, but without the ADO stuff as I
have no repro details for that side of it-

Option Explicit

Function Main()
Dim objXMLDOM
Dim objNodes
Dim objTitleNode
Dim objADORS
Dim objADOCnn

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

objXMLDOM.load "C:\ProductsAnnounced.xml"
If (objXMLDOM.parseError.errorCode <> 0) Then
Dim myErr
Set myErr = objXMLDOM.parseError
MsgBox("You have error " & myErr.reason)
Main = DTSTaskExecResult_Failure
else

Set objNodes =
objXMLDOM.selectNodes("//products-announced/product-announced")

Dim sTitle
Dim sUPC

For Each objTitleNode In objNodes

sTitle =
objTitleNode.selectSingleNode("title-info/title-name").nodeTypedValue
sUPC = objTitleNode.selectSingleNode("product-info/upc").nodeTypedValue
MsgBox(sTitle & vbCrLf & sUPC)

Next

End If

Main = DTSTaskExecResult_Success
End Function

Here is the XML file I created based on your description-

<?xml version="1.0"?>
<products-announced>
<product-announced>
<title-info>
<title-name>10</title-name>
<studio/>
</title-info>
<product-info>
<product-name>"10" (DVD)</product-name>
<upc>012569200227</upc>
</product-info>
</product-announced>
<product-announced>
<title-info>
<title-name>11</title-name>
<studio/>
</title-info>
<product-info>
<product-name>"11" (DVD)</product-name>
<upc>112569200227</upc>
</product-info>
</product-announced>
</products-announced>


The two work together fine, and I get two message boxes with different
values.

What is wrong with this repro?
What is the error?


--
Darren Green
http://www.sqldts.com


"Stacey Levine" <staceyl (AT) SMAPFILTERmusicforasong (DOT) com> wrote

OK.. I took out the ADO stuff and it works. There must be some bad data
stopping it from being written to SQL.. thanks.. I can take it from here..
probably :-)

stacey
"Darren Green" <darren.green (AT) reply-to-newsgroup-sqldts (DOT) com> wrote