XML: search and replace childnode

Hi all,

I use ADO to create a customer recordset (adoCustomer). From a Customer dialog, after adding customers , I save all records into a XML document (Customer.xml)

In the case I editing a record, how could I search and replace this record in the XML document?

Supposed ADO recordset, Customer dialog, and XML document existed


i.e
I wanna replace the Henry Ford record in the XML below with the edited info:

CustomerGroup = 2 (no changed)
CustomerID = 3456 (no changed)
CustomerName = Henry Mustang
CustomerAddress = 200 King Street



Customer.XML:

<CustomerList>
 <Customer>
   <CustomerGroup>1</CustomerGroup>
   <CustomerID>1234</CustomerID>
   <CustomerName>Bill Gates</CustomerName>
   <CustomerAddress>100 Queen Street</CustomerAddress>
 </Customer>
 <Customer>
   <CustomerGroup>2</CustomerGroup>
   <CustomerID>3456</CustomerID>
   <CustomerName>Henry Ford</CustomerName>
   <CustomerAddress>200 Queen Street</CustomerAddress>
 </Customer>
 <Customer>
   <CustomerGroup>2</CustomerGroup>
   <CustomerID>5678</CustomerID>
   <CustomerName>Michael Jackson</CustomerName>
   <CustomerAddress>300 Queen Street</CustomerAddress>
 </Customer>
</CustomerList>


thanks in advance
nguyenn
LVL 1
nguyennAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
Building on dekeldate code, here is the complete (and I believe completely debugged) code:

Dim xmldoc As MSXML.DOMDocument

Set xmldoc = New MSXML.DOMDocument
If xmldoc.Load("Customer.xml") Then
   If EditItem("3456", xmldoc, "2", "Henry Mustang", "200 King Street") Then
      xmldoc.save "CustomerNew.xml"
      MsgBox "XML Document saved"
   Else
      MsgBox "XML Node not changed"
   End If
End If

End Sub

Private Function EditItem(CustomerID As String, _
                                     xmldoc As DOMDocument, _
                                     CustomerGroup As String, _
                                     CustomerName As String, _
                                     CustomerAddress As String) As Boolean
Dim oElem As IXMLDOMElement

On Error GoTo ErrHandler

EditItem = False  ' Assume the worst
Set oElem = xmldoc.selectSingleNode("//Customer[CustomerID='" & CustomerID & "']")
If Not oElem Is Nothing Then
   oElem.selectSingleNode("CustomerGroup").Text = CustomerGroup
   oElem.selectSingleNode("CustomerName").Text = CustomerName
   oElem.selectSingleNode("CustomerAddress").Text = CustomerAddress
   EditItem = True
End If
         
Set oElem = Nothing
Exit Function

ErrHandler:
With xmldoc.parseError
   If .errorCode Then
      MsgBox "Error #:" & CStr(.errorCode) & vbCr & .reason
   Else
      MsgBox "Error #:" & CStr(Err.Number) & vbCr & Err.Description
   End If
End With

End Function
0
 
planoczCommented:
Hi nguyenn,
Try this bit of code...

'Find and update the record for Henry Mustang


set RS as New ADODB.Recordset

RS.Find "CustomerName = #Henry Mustang#,SearchDirection:= adForward, Start:=adBookmarkFirst
 If RS.EOF Then
    RS.Update Fields:= "CustomerName", "CustomerAddress",
     Values:="Henry Mustang","200 King Street"
  End If
  RS.Save Destination:= yourXMLFile, PersistFormat:= adPersistXML
  RS.Close
  Set RS = Nothing
0
 
planoczCommented:
sorry change
 If RS.EOF Then

to

 If RS.EOF = False Then
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Julian_KCommented:
Hi :-)
There is something you can use, but not very good. In case no other ideas come out I'll send an example.
What I think of, is opening the ".XML" file as a text file (Open <FileName> for Input as #x), and copying the content to another file, meanwhile changing the needed records. Then killing the old file, and renaming the new.

But it will work SLOW, if your file is big.
0
 
nguyennAuthor Commented:
Hi Plancoz

I got error at this line:

RS.Save Destination:= yourXMLFile, PersistFormat:= adPersistXML

Runtime error 58 - file already exists


Hi Julian,
You're right, with that method absolutely runs slow in my case, I guess we have another way to do it.

Thanks all
0
 
Dave_GreeneCommented:
nguyenn,

Are you required to keep the XML format shown above? or can you change it?

Being that this is a relatively new technology, it seems that everyone creates little design problems for themselves.

Here is how I would change your XML

<CustomerList>
   <Customer Group="1" ID="1234" Name="Bill Gates" Address="100 Queen Street"/>




0
 
nguyennAuthor Commented:
Hi Plancoz,

Your way seems update the recordset, not update the XML document. It will replace the old document with the new one
0
 
nguyennAuthor Commented:
Thanks Dave, but I been asked to create the Customer.xml with that format
0
 
Dave_GreeneCommented:
If your XML was formed as attributes you could search your contents like so

sID = "1234"
sGroup = "1"

Set oNode = oDOM.selectSingleNode("CustomerList/Customer[@ID $eq$ " & sID & "][@Group
$eq$ " & sGroup & "]")

Once you have found your node the rest is simple...
0
 
Dave_GreeneCommented:
I've never worked with poorly designed XML before or you would have your answer already...  I'll have to look into some things...
0
 
nguyennAuthor Commented:
I'm testing the way you sugested, I'll let you know if I got luck

Thanks Dave
0
 
planoczCommented:
Add this between the RS.EOF statement and the Save statement.
If DIR$(yourXMLFile) <> "" then
   Kill yourXMLFile
EndIf

This will recreate the XML with the new update.
0
 
nguyennAuthor Commented:
It doesnt work, Planocz. As I mentioned above, your code replaces the old XML document with the new one ONLY has a record. I want to search and replace a specific record in the Customer.xml based on the required format

0
 
planoczCommented:
I will recheck the problem at this end. Thanks
0
 
nguyennAuthor Commented:
thanks for your time, planocz. I'm using selectSingNode to search, and try a way to replace old node with a new node.

Thanks again
0
 
Dave_GreeneCommented:
Here is an example of how to edit a node

Public Function EditItem(nLineNo As Long, nOffset As Long) As Boolean
   
   On Error GoTo EH:
   
   EditItem = FAIL
   
   Const EditAddr      As Byte = 1

   Dim oNode           As IXMLDOMNode
   Dim oChild          As IXMLDOMNode
   Dim oUpdateChild    As IXMLDOMNode
   
   Set oNode = m_XML_DOM.selectSingleNode("Markup/HyperLinks")
   
   '
   ' set desired node
   '
   Set oChild = SearchItem(nLineNo, nOffset)
   
   If Not oChild Is Nothing Then
       
       '
       ' copy old child to child to be updated
       '
       Set oUpdateChild = oChild
       
       With oUpdateChild.Attributes
           
           If .getNamedItem("ImgID").Text <> "" And .getNamedItem("Address").Text <> "" Then
               
               Dim strTemp         As String
               Dim strFileOnly     As String
               
               strTemp = .getNamedItem("Address").Text
               
               '
               ' check for web address
               '
               If InStr(1, strTemp, "http://") = 0 Then
                   
                   '
                   ' check for local file
                   '
                   If InStr(2, strTemp, ":\") Then
                       '
                       ' local file, remove the file location
                       '
                       strFileOnly = StripPath(strTemp)
                       '
                       ' change address value
                       '
                       .getNamedItem("Address").Text = strFileOnly
                   
                   End If
                   
               End If
           
           End If
           
       End With
       '
       ' replace old node with updates
       '
       oNode.replaceChild oUpdateChild, oChild
       
       EditItem = SUCCESS
       
   End If
           
   Set oNode = Nothing
   Set oChild = Nothing
   Set oUpdateChild = Nothing
   
   Exit Function
EH:
   MsgBox Err.Number & Err.Description
   ProcessError
End Function
0
 
planoczCommented:
I checked the code and it works fine. I may have to send you the total code setup. The method that I use will change the XML file to have the orignal code and also show the updated code. This is good for possible problems in the future that you might have to go backwards or just having a historical background on your data.
If I can help just let me know.
0
 
dekeldateCommented:
Looking at your XML I see the CustomerID probably does not change and is unique. If that is the case as simpler bit of code would be

Public Function EditItem(customerID as long, xmlDoc as DOMDocumnet) As Boolean
  dim oElem           as IXMLDOMElement

  '
  ' set desired node
  '
    set oElem = xmlDoc.selectSingleNode("//Customer[CustomerID='" & customerID & "']")

  if not oElem = Nothing then

      oElem.selectSingleNode("CustomerName").text = "Henry Mustang"
      oElem.selectSingleNode("CustomerAddress" = "200 King Street"
     
      EditItem = SUCCESS
     
  End If
         
  Set oElem = Nothing
  Exit Function
End Function
 
0
 
nguyennAuthor Commented:
To Dave,

When you pass into EditItem function, what are the value of nLineNo and nOffset ?



To Planocz,

With .Save destination command, it requires to kill an old XML file before enable create a new one(while I need to update specific items only), and it doesnt allow us to save a XML document with a predefined format


To Deketldate,

You're right, CustomerID is a primary key, but your code doesnt change the XML at all. Do I need to add anything else after that?

Thanks for all of your help
nguyen
0
 
Dave_GreeneCommented:
Hi nguyenn,

This was the XML I was coding against.  Should help

<?xml version="1.0"?>
<Markup>
<HyperLinks>
 <Link Line="26" Offset="1102" Length="29" ImgID="" Address="http://www.yahoo.com/"/>
 <Link Line="21" Offset="841" Length="24" ImgID="" Address="http://www.search.com/"/>
 <Link Line="28" Offset="1216" Length="21" ImgID="" Address="http://www.cnn.com/"/>
 <Link Line="37" Offset="1479" Length="27" ImgID="" Address="http://www.otcstreet.com/trivia/otctrivia.cfm"/>
</HyperLinks>
</Markup>
0
 
nguyennAuthor Commented:
Hi Dave,

I tried your code, but it never met this condition:

If not (oUpdateChild.Attributes.getNamedItem ("CustomerName") is Nothing) Then
    oUpdateChild.Attributes.getNamedItem("CustomerName").Text = "New Name"
End If

With my case, I assigned
Set oNode = domDoc.selectSingleNode("//RDSOperation[CustomerID='" & intAccount & "']")

Do you see anything wrong?

Thanks
nguyenn
0
 
Dave_GreeneCommented:
Did you change your XML structure like I suggested?  If not if won't work
0
 
nguyennAuthor Commented:
Nope, I wish I could changed it. But as I told you I'm working on an existed XML, which I dont have the permission to change its structure
0
 
nguyennAuthor Commented:
Thanks apeckin, you help me out to solve my problem.

I also thanks for everybody who gave me your ideas and source code

Have a nice long weekend all :)
nguyenn
0
All Courses

From novice to tech pro — start learning today.