How to Insert XML data to Access .MDB Database?

[Pheddy]

Hello everyone!

I am having severe problems updating my Access 2007 .mdb database with the content from an XML file... I had it going, but there where problems with the Product groups, taking each record and updating the database caused problems since there are over 8000 products there are 99 Product groups - and somehow after x records the Product group arent acounted for.

The XML structure is like this:

<?xml version="1.0" encoding="ISO-8859-1"?>
<products xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="oemPriceXMLList.xsd">

<group name="">
</group>
<group name="Other Software">
<product id="50567">
<name>Adobe Premiere Elements UK V8</name>
<stock>Yes</stock>
<weight>0.5</weight>
<model>6543545546</model>
<shortDescription>Adobe Premiere Elements - ( v. 8 ) - English</shortDescription>
<manufacturerName>Adobe</manufacturerName>
<manufacturerURL>http://www.adobe.dk/</manufacturerURL>
<price>475</price>
</product>

The code looks like this:

Set objNodeList	= xmlDOC.documentElement.selectNodes("//products/group/product/name")
Set objNodeList10	= xmlDOC.documentElement.selectNodes("//products/group/@name")
Response.Write "<br>Total produkter: " & objNodeList.Length
Response.Write "<br>Total Kategorier: " & objNodeList10.Length
'Response.Write "<br>Starter Opdatering af Databasen.."

For I = 0 to objNodeList.Length - 1
varCountCategories = varCountCategories + 1

  'Response.Write objNodeList(i).text & "<br>" '<--- Produkt navn:

  Set objNodeList2	= xmlDOC.documentElement.selectNodes("//products/group/product/stock")
    varObj2 = objNodeList2(i).text
  Set objNodeList3	= xmlDOC.documentElement.selectNodes("//products/group/product/weight")
    varObj3 = objNodeList3(i).text
  Set objNodeList4	= xmlDOC.documentElement.selectNodes("//products/group/product/model")
    varObj4 = objNodeList4(i).text
  Set objNodeList5	= xmlDOC.documentElement.selectNodes("//products/group/product/shortDescription")
    varObj5 = objNodeList5(i).text
  Set objNodeList6	= xmlDOC.documentElement.selectNodes("//products/group/product/manufacturerName")
    varObj6 = objNodeList6(i).text
  Set objNodeList7	= xmlDOC.documentElement.selectNodes("//products/group/product/manufacturerURL")
    varObj7 = objNodeList7(i).text
  Set objNodeList8	= xmlDOC.documentElement.selectNodes("//products/group/product/price")
    varObj8 = objNodeList8(i).text
  Set objNodeList9	= xmlDOC.documentElement.selectNodes("//products/group/product/@id")
    varObj9 = objNodeList9(i).text 
  Set objNodeList10	= xmlDOC.documentElement.selectNodes("//products/group/product/@name")
    varObj10 = objNodeList10(i).text   
  
    'Produkt Gruppe........................................................................................
    strSQL = "SELECT * FROM products WHERE product_name = '" & varObj10 & "' AND product_genre = 0"
    Set rs = Conn.Execute(strSQL)

  if not rs.EOF then
    strUID = generateUID(12)
	'HVIS PRODUKTGRUPPEN FINDES, OPDATER ITEM TIL DENNE:
  strGenreID = rs("product_category") 'Find Genre ID AF PRODUKTGRUPPEN
  strProductTypeID = rs("product_type")


  'Produkt........................................................................................ Tjek Produkt
  strSQL2 = "SELECT * FROM products WHERE product_boughtLink = '" & objNodeList9(i).text & "'"
  Set rs2 = Conn.Execute(strSQL2)

  if rs2.EOF then
    'Produktet findes.. Tilføj produkt:
	'ADD TO DB.....................................................................................
	sql = "INSERT INTO products (product_name, product_boughtLink, product_nettoPrice, product_link, product_addedDate, product_addedTime, product_type, product_category, product_genre, product_picture, "
	sql = sql & "product_memo, product_SKU, product_UID, product_reseller, product_producerName, product_inventoryWeight, product_inventoryDelivery)"
	sql = sql & " VALUES "
	sql = sql & "('" & Replace(objNodeList(i).text,"'","''") & "', "			'NAME
	sql = sql & "'" & objNodeList9(i).text & "', "			'SKU
	sql = sql & "'" & objNodeList8(i).text & "', "			'PRICE IN
	sql = sql & "'" & objNodeList7(i).text & "', "			'PRODUCT LINK
	sql = sql & "'" & Date & "', "														'AUTO DATE
	sql = sql & "'" & Time & "', "														'AUTO TIME
	sql = sql & "'" & strGenreID & "',"													'TYPE (ALLWAYS 2 FOR HARDWARE)
	sql = sql & "'0',"													'CATEGORY ALLWAYS 0 FOR ITEMS! / (DIRECTORY ID)
	sql = sql & "'" & strProductTypeID & "', "					'GENRE 
	sql = sql & "'helllo',"																'IMAGE FILE EXT
	sql = sql & "'" & Replace(objNodeList5(i).text,"'","''") & "', "						'MEMO
	sql = sql & "'" & objNodeList4(i).text & "', "			'UID
	sql = sql & "'" & strUID & "',"														'UID (SENT BY FORM BUT TJECKED BY SCRIPT (ABOVE)
	sql = sql & "'hello',"												'RESELLER
	sql = sql & "'" & objNodeList6(i).text		 & "', "				'PRODUCT MANUFACTOR product_inventoryWeight
	sql = sql & "'" & objNodeList3(i).text		 & "', "				'PRODUCT WEIGTH
	sql = sql & "'" & objNodeList2(i).text		 & "')"					'DELIVERY

on error resume next
  conn.Execute sql, RecAffected
if err <> 0 then
  varCountNotInserted = varCountNotInserted + 1
else
  varCountInserted = varCountInserted + 1
end if

  else
  varCountAllreadyIn = varCountAllreadyIn + 1
  if rs2("product_nettoPrice") <> objNodeList8(i).text then
    strSQL3 = "UPDATE products SET " 
	strSQL3 = strSQL3 & "product_nettoPrice = '" & objNodeList8(i).text & "' " 
    strSQL3 = strSQL3 & " WHERE product_boughtLink = '" & objNodeList9(i).text & "'"

    Conn.Execute(strSQL3)
  'HVIS PRODUKT GRUPPEN IKKE FINDES:
  end if
    
  end if

The problem is with strGenreID and strProductTypeI D. I guess instead I have to select each Product Group from the XML structure, loop trough each item in that group and go to next group.

Does anybody have any idea how to do so? And are my code totally screwed? (I think it is.. )

Regards.
Frederik

[danp129]

It looks like you're not getting the name for the product...

'Set objNodeList10    = xmlDOC.documentElement.selectNodes("//products/group/product/@name")
Set objNodeList10    = xmlDOC.documentElement.selectNodes("//products/group/product/name")

Also, it is not going to add a new product because your code to add a product is in an "If not rs.EOF" statement that will be EOF if you don't find a product of the same name.

I would also recommend not using T-SQL and using ADO for adding/updated records. It makes it easier to troubleshoot typo's because you'll get a line number for each column you refer to. Google rs.AddNew rs.Update for examples.