Solved

How to append an xml file based on matching IDs with another xml file

Posted on 2011-02-25
15
267 Views
Last Modified: 2012-06-27
Hello,

I need to include the two rows below to an exiting Link.xml file, how do I loop through my xml file to append these two rows?

<Record></Record>
<Registration> </Registration>

After including the rows inmy link.xml file, I need to populate them with data from a Fuze.xml file where an ID of the Link.xml file equals to the ID Fuze.xml file.

How do I loop through both files to populate the two rows in the Link.xml where
Link.ID = Fuze.ID

Thanks,

Victor
0
Comment
Question by:vcharles
  • 8
  • 7
15 Comments
 
LVL 74

Expert Comment

by:käµfm³d 👽
Comment Utility
I'm kind of confused by the statement, "how do I loop through my xml file to append these two rows." If you are appending them, they should just go at the end. Are you saying you want them placed at a particular position, or were you just saying loop with the intent of getting to the end of the document?
0
 

Author Comment

by:vcharles
Comment Utility
Hi,

Below is abetter explanation of what I'm trying to do.

For example Link.xml contains

<Row>
<Name>1<Name>
<Model> 45 rest Road </Model>
</Row>

I want to append the two rows below to Link.xml:
<Record></Record>
<Registration> </Registration>

Afterwards, using Fuze.xml which contains the following rows:
<Name>1<Name>
<Record></Record>
<Registration> </Registration>

I want to loop through Fuze.xml to include its values:

<Record></Record>
<Registration> </Registration>

Where Name of Fuze.xml = Name for Link.xml

Thanks,

Victor









Fuze.xml contains

0
 
LVL 74

Expert Comment

by:käµfm³d 👽
Comment Utility
The problem I have, though, is that you have not posted enough XML for us to provide a decent example. There is nothing in the Fuze.xml example that would tell me those rows belong together (i.e. there is no parent node).
0
 

Author Comment

by:vcharles
Comment Utility
Hi,

Below is a much better explanation of what I'm trying to do.

For example Link.xml contains

<Row>
<Name>1<Name>
<Model> 45 rest Road </Model>
</Row>
<Row>
<Name>2<Name>
<Model> 55 rest Road </Model>
</Row>
<Row>
<Name>3<Name>
<Model> 65 rest Road </Model>
</Row>


I want to append the two rows below to Link.xml:
<Record></Record>
<Registration> </Registration>

Afterwards, using Fuze.xml which contains the following rows:
<Name>1<Name>
<Record>CLOSED</Record>
<Registration>OPEN</Registration>
<Name>2<Name>
<Record>OPEN</Record>
<Registration>LATE</Registration>
<Name>3<Name>
<Record>PENDING</Record>
<Registration>NA</Registration>


I want to loop through Fuze.xml to include its values:

<Record></Record>
<Registration> </Registration>

Where Name of Fuze.xml = Name for Link.xml, Base on the examples above, the link.xml file would have the following:

<Row>
<Name>1<Name>
<Model> 45 rest Road </Model>
<Record>CLOSED</Record>
<Registration>OPEN</Registration>
</Row>
<Row>
<Name>2<Name>
<Model> 55 rest Road </Model>
<Record>OPEN</Record>
<Registration>LATE</Registration>
</Row>
<Row>
<Name>3<Name>
<Model> 65 rest Road </Model>
<Record>PENDING</Record>
<Registration>NA</Registration>
</Row>


Thanks,

Victor
0
 
LVL 74

Expert Comment

by:käµfm³d 👽
Comment Utility
You have some problems with you XML. Firstly, you don't have a root node in either file. Secondly, in Fuze.xml, there is nothing associating groups of nodes together. In Link.xml, there is a row element, and I can clearly see that a row has a name and a model. In Fuze.xml, however, there is just a glob of nodes....   there is nothing that tells me any particular name belongs with any specific record or any specific registration. There is nothing in your Fuze.xml that prevents the computer from interpreting <Name>1</Name> being paired with <Registration>LATE</Registration> or with <Record>PENDING</Record>.
0
 

Author Comment

by:vcharles
Comment Utility
Hi,

I'm sorry for the confusion, I was using the files as an example, below is a copy of the actual files that I'm using.

I would like to append Link.xml with <FuzeName_ID></FuzeName_ID> , <FuzeType_ID></FuzeType_ID>; Then populate them based on matched Fuze_ID with LinkFuze.xml
 
LinkFuze.xml:
 
<?xml version="1.0" standalone="yes"?>
<DocumentElement>
<Row>
<Fuze_ID>1</Fuze_ID>
<BM>1</BM>
<TYPE>3</TYPE>
<FuzeName_ID>5</FuzeName_ID>
<FuzeType_ID>7</FuzeType_ID>
</Row>
<Row>
<Fuze_ID>2</Fuze_ID>
<BM>2</BM>
<TYPE>1</TYPE>
<FuzeName_ID>9</FuzeName_ID>
<FuzeType_ID>6</FuzeType_ID>
</Row>
<Row>
<Fuze_ID>3</Fuze_ID>
<BM>1</BM>
<TYPE>5</TYPE>
<FuzeName_ID>8</FuzeName_ID>
<FuzeType_ID>9</FuzeType_ID>
</Row>
<Row>
<Fuze_ID>4</Fuze_ID>
<BM>0</BM>
<TYPE>2</TYPE>
<FuzeName_ID>5</FuzeName_ID>
<FuzeType_ID>7</FuzeType_ID>
</Row>
<Row>
<Fuze_ID>5</Fuze_ID>
<BM>1</BM>
<TYPE>3</TYPE>
<FuzeName_ID>7</FuzeName_ID>
<FuzeType_ID>2</FuzeType_ID>
</Row>
<Row>
<Fuze_ID>6</Fuze_ID>
<BM>1</BM>
<TYPE>8</TYPE>
<FuzeName_ID>5</FuzeName_ID>
<FuzeType_ID>4</FuzeType_ID>
</Row>
<Fuze_ID>7</Fuze_ID>
<BM>1</BM>
<TYPE>5</TYPE>
<FuzeName_ID>5</FuzeName_ID>
<FuzeType_ID>4</FuzeType_ID>
</Row>
<Fuze_ID>8</Fuze_ID>
<BM>1</BM>
<TYPE>9</TYPE>
<FuzeName_ID>7</FuzeName_ID>
<FuzeType_ID>2</FuzeType_ID>
</Row>
</DocumentElement>
 
 
Link.xml
 
<?xml version="1.0" standalone="yes"?>
<DocumentElement>
<Row>
<Fuze_ID>1</Fuze_ID>
<BM>0</BM>
<TYPE>3</TYPE>
</Row>
<Row>
<Fuze_ID>2</Fuze_ID>
<BM>1</BM>
<TYPE>2</TYPE>
</Row>
<Row>
<Fuze_ID>3</Fuze_ID>
<BM>0</BM>
<TYPE>3</TYPE>
</Row>
<Row>
<Fuze_ID>4</Fuze_ID>
<BM>2</BM>
<TYPE>4</TYPE>
</Row>
<Row>
<Fuze_ID>5</Fuze_ID>
<BM>5</BM>
<TYPE>8</TYPE>
</Row>
<Row>
<Fuze_ID>6</Fuze_ID>
<BM>1</BM>
<TYPE>2</TYPE>
</Row>
<Row>
<Fuze_ID>7</Fuze_ID>
<BM>8</BM>
<TYPE>8</TYPE>
</Row>
<Row>
<Fuze_ID>8</Fuze_ID>
<BM>3</BM>
<TYPE>6</TYPE>
</Row>
</DocumentElement>
 
Thanks.

Victor
0
 
LVL 74

Expert Comment

by:käµfm³d 👽
Comment Utility
Ahhh...  That makes more sense  = )

Here is one approach to doing what you seek. The output is going to "result.xml" just so you can compare "before" and "after." You can change the save location accordingly.
Dim fDoc As New XmlDocument()
Dim lDoc As New XmlDocument()

fDoc.Load("LinkFuze.xml")
lDoc.Load("Link.xml")

For Each rowElement As XmlNode In lDoc.SelectNodes("//Row")
	Dim currentId As String = rowElement.SelectSingleNode("Fuze_ID").InnerText
	Dim name As XmlNode = rowElement.AppendChild(lDoc.CreateElement("FuzeName_ID"))
	Dim type As XmlNode = rowElement.AppendChild(lDoc.CreateElement("FuzeType_ID"))

	' Find currentId in LinkFuze.xml
	Dim xpath As String = "//Row[Fuze_ID = " + currentId + "]"
	Dim fuzeRowElement As XmlNode = fDoc.SelectSingleNode(xpath)

	If fuzeRowElement IsNot Nothing Then
		Dim fuzeNameElement As XmlNode = fuzeRowElement.SelectSingleNode("FuzeName_ID")

		If fuzeNameElement IsNot Nothing Then
			name.InnerText = fuzeNameElement.InnerText
		End If

		Dim fuzeTypeElement As XmlNode = fuzeRowElement.SelectSingleNode("FuzeType_ID")

		If fuzeTypeElement IsNot Nothing Then
			type.InnerText = fuzeTypeElement.InnerText
		End If
	End If
Next

lDoc.Save("result.xml")

Open in new window

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 74

Expert Comment

by:käµfm³d 👽
Comment Utility
Make sure you have Imports System.Xml for the above example  : )
0
 

Author Comment

by:vcharles
Comment Utility
Thank you very much for the code. I'm running this code during an update and I only want to save the data in the link.xml file only when there is a matching Fuze_ID in the LinkFuze.xml file. How do modify the code to stop users from updating Link.xml unless Fuze_ID in LinkFuze.xml = Fuze_ID in Link.xml?

Thanks,

Victor
0
 
LVL 74

Expert Comment

by:käµfm³d 👽
Comment Utility
Can you clarify your last comment for me? Currently, the code should only update those nodes where an ID exists in Fuze.xml; however, it does append the requested nodes whether there is a match or not. This has the potential of leaving empty nodes wherever the ID does not exist in Fuze.xml. Are you saying that you only want to update if one-or-more IDs exist in Fuze.xml? In other words, if there are no matching IDs in Fuze.xml, don't append anything?

P.S.

Here was the output generated from the sample data you posted:
<?xml version="1.0" standalone="yes"?>
<DocumentElement>
  <Row>
    <Fuze_ID>1</Fuze_ID>
    <BM>0</BM>
    <TYPE>3</TYPE>
    <FuzeName_ID>5</FuzeName_ID>
    <FuzeType_ID>7</FuzeType_ID>
  </Row>
  <Row>
    <Fuze_ID>2</Fuze_ID>
    <BM>1</BM>
    <TYPE>2</TYPE>
    <FuzeName_ID>9</FuzeName_ID>
    <FuzeType_ID>6</FuzeType_ID>
  </Row>
  <Row>
    <Fuze_ID>3</Fuze_ID>
    <BM>0</BM>
    <TYPE>3</TYPE>
    <FuzeName_ID>8</FuzeName_ID>
    <FuzeType_ID>9</FuzeType_ID>
  </Row>
  <Row>
    <Fuze_ID>4</Fuze_ID>
    <BM>2</BM>
    <TYPE>4</TYPE>
    <FuzeName_ID>5</FuzeName_ID>
    <FuzeType_ID>7</FuzeType_ID>
  </Row>
  <Row>
    <Fuze_ID>5</Fuze_ID>
    <BM>5</BM>
    <TYPE>8</TYPE>
    <FuzeName_ID>7</FuzeName_ID>
    <FuzeType_ID>2</FuzeType_ID>
  </Row>
  <Row>
    <Fuze_ID>6</Fuze_ID>
    <BM>1</BM>
    <TYPE>2</TYPE>
    <FuzeName_ID>5</FuzeName_ID>
    <FuzeType_ID>4</FuzeType_ID>
  </Row>
  <Row>
    <Fuze_ID>7</Fuze_ID>
    <BM>8</BM>
    <TYPE>8</TYPE>
    <FuzeName_ID>5</FuzeName_ID>
    <FuzeType_ID>4</FuzeType_ID>
  </Row>
  <Row>
    <Fuze_ID>8</Fuze_ID>
    <BM>3</BM>
    <TYPE>6</TYPE>
    <FuzeName_ID>7</FuzeName_ID>
    <FuzeType_ID>2</FuzeType_ID>
  </Row>
</DocumentElement>

Open in new window

0
 

Author Comment

by:vcharles
Comment Utility
Hi,

I want to append even if the IDs don't match, but I only want to add records to link.xml when the Fuze_IDs from both files are equal.

The code is working when the Fuze_IDs are equal, but when they are not equal, the data elements appends as <Parent_Fuze_ID />, is there a way to append it as <Parent_Fuze_ID></Parent_Fuze_ID> even when the IDs don't match?

Thank You.

Victor
0
 

Author Comment

by:vcharles
Comment Utility
Hello,

Just realized, if the data ia appended with no data, the next time we run the code to update Link.xml, it will append again. Is it possible  use the append part of the code as a one time execution and use the part to populate the data as part of the add application, for example we would only be able to add data to the 2 new data elements in Link.xml only when the Fuze_IDs match?  
Thanks,
Victor
0
 
LVL 74

Accepted Solution

by:
käµfm³d   👽 earned 500 total points
Comment Utility
>>  is there a way to append it as <Parent_Fuze_ID></Parent_Fuze_ID>

AFAIK, <Parent_Fuze_ID /> and <Parent_Fuze_ID></Parent_Fuze_ID> mean exactly the same thing. I agree that visually it is sometime easier to see the latter as being empty, but I don't know of a way, using XmlDocument alone, to format the node in that manner. You could read the file into a string, provided it is not too large, after you do the node adding. Then you could simply do a string.Replace(source, "<Parent_Fuze_ID />", "<Parent_Fuze_ID></Parent_Fuze_ID>") to overwrite those empty nodes into your desired format.


>>  Is it possible  use the append part of the code as a one time execution and use the part to populate the data as part of the add application

See if the following modification corrects what you described.
Dim fDoc As New XmlDocument()
Dim lDoc As New XmlDocument()

fDoc.Load("LinkFuze.xml")
lDoc.Load("Link.xml")

For Each rowElement As XmlNode In lDoc.SelectNodes("//Row")
    Dim currentId As String = rowElement.SelectSingleNode("Fuze_ID").InnerText
    Dim name As XmlNode = rowElement.SelectSingleNode("FuzeName_ID")
    Dim type As XmlNode = rowElement.SelectSingleNode("FuzeType_ID")

    If name Is Nothing Then
        name = rowElement.AppendChild(lDoc.CreateElement("FuzeName_ID"))
    End If

    If type Is Nothing Then
        type = rowElement.AppendChild(lDoc.CreateElement("FuzeType_ID"))
    End If

    ' Find currentId in LinkFuze.xml
    Dim xpath As String = "//Row[Fuze_ID = " + currentId + "]"
    Dim fuzeRowElement As XmlNode = fDoc.SelectSingleNode(xpath)

    If fuzeRowElement IsNot Nothing Then
        Dim fuzeNameElement As XmlNode = fuzeRowElement.SelectSingleNode("FuzeName_ID")

        If fuzeNameElement IsNot Nothing Then
            name.InnerText = fuzeNameElement.InnerText
        End If

        Dim fuzeTypeElement As XmlNode = fuzeRowElement.SelectSingleNode("FuzeType_ID")

        If fuzeTypeElement IsNot Nothing Then
            type.InnerText = fuzeTypeElement.InnerText
        End If
    End If
Next

lDoc.Save("result.xml")

Open in new window

0
 

Author Comment

by:vcharles
Comment Utility
Hello,

I am sorry for the late reply, how would you modify the code to append and populate three data elements in link.xml depending on it's IDs equaling IDs from three other files?

For example:

Link.fuze_I'd = Linkfuze_fuze_I'd
Link.prop_I'd = Linkprop_ptop_I'd
link.proj_Id = Linkproj_pro_I'd

Thanks,

victor
0
 

Author Closing Comment

by:vcharles
Comment Utility
Thank You!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now