Help with mapping a column as childnode in XML using vba

roipatrick

New Member
Joined
Mar 20, 2014
Messages
39
I have a sheet which can be mapped as XML and it is running well. But I want to map a certain column which will become the childnode of a tag.

A normal XML tag is shown below

Code:
<entry>
                <animal ID>
                <animal name>
                <animal group>
         </entry>

Now, what I'm trying to accomplish is something like this

Code:
<entry no="1">   <------Theres a column in my excel which i created to be the child node but im finding it hard to do it
                <animal ID>
                <animal name>
                <animal group>
         </entry>

Here's the mapping code of my excel sheet to become exportable into XML file

Code:
ActiveWorkbook.Sheets("Animals").Activate   ActiveSheet.EnableCalculation = False
   Dim oMyMap As XmlMap
   Dim strXPath As String
   Dim oMyList As ListObject
   Dim oMyNewColumn As ListColumn


   ' Add a schema map.
   ' ThisWorkbook.XmlMaps.Add (ThisWorkbook.Path & "\Myschema.xsd")
   ThisWorkbook.XmlMaps.Add ("C:\Users\1443\Desktop\grouping\schema.xml")


   ' Identify the target schema map.
   Set oMyMap = ThisWorkbook.XmlMaps("Animal_Map")
    
   ' Create a new list in A1.
   Range("A1").Select
   
   Set oMyList = ActiveSheet.ListObjects.Add
   strXPath = "/Animal/entry/AnimalId"
   oMyList.ListColumns(1).XPath.SetValue oMyMap, strXPath








   Set oMyNewColumn = oMyList.ListColumns.Add
   strXPath = "/Animal/entry/AnimalName"
   oMyList.ListColumns(2).XPath.SetValue oMyMap, strXPath




   Set oMyNewColumn = oMyList.ListColumns.Add
   strXPath = "/Animal/entry/AnimalGroup"
   oMyList.ListColumns(3).XPath.SetValue oMyMap, strXPath

   oMyList.ListColumns(1).Name = "AnimalID"   oMyList.ListColumns(2).Name = "AnimalName"
   oMyList.ListColumns(3).Name = "AnimalGroup"

Now, How can I add a column let's say G coloumn in excel as their child node.

Table show below

ABCDEFG
Animal IdAnimal NameAnimal GroupNo
123DogCanine1
122CatFeline2
121SnakeReptile3

<tbody>
</tbody>
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
I have a sheet which can be mapped as XML and it is running well. But I want to map a certain column which will become the childnode of a tag.

A normal XML tag is shown below

<entry><entry>
<animalid/>
<animalname/>
<animalgroup/>
</entry>
<animal id="">
<animalname>
<animalgroup>
</animalgroup></animalname></animal></entry>

Now, what I'm trying to accomplish is something like this

<entry no="1"><entry no="1"><entry no="1"> <------Theres a column in my excel which i created to be the child node but im finding it hard to do it
<animalid/>
<animalname/>
<animalgroup/>
</entry>
<animal id="">
<animalname>
<animalgroup>
</animalgroup></animalname></animal></entry>
<animal id="">
<animal name="">
<animal group="">
</animal></animal></animal></entry>

Here's the mapping code of my excel sheet to become exportable into XML file

Code:
ActiveWorkbook.Sheets("Animals").Activate   ActiveSheet.EnableCalculation = False
   Dim oMyMap As XmlMap
   Dim strXPath As String
   Dim oMyList As ListObject
   Dim oMyNewColumn As ListColumn


   ' Add a schema map.
   ' ThisWorkbook.XmlMaps.Add (ThisWorkbook.Path & "\Myschema.xsd")
   ThisWorkbook.XmlMaps.Add ("C:\Users\1443\Desktop\grouping\schema.xml")


   ' Identify the target schema map.
   Set oMyMap = ThisWorkbook.XmlMaps("Animal_Map")
    
   ' Create a new list in A1.
   Range("A1").Select
   
   Set oMyList = ActiveSheet.ListObjects.Add
   strXPath = "/Animal/entry/AnimalId"
   oMyList.ListColumns(1).XPath.SetValue oMyMap, strXPath








   Set oMyNewColumn = oMyList.ListColumns.Add
   strXPath = "/Animal/entry/AnimalName"
   oMyList.ListColumns(2).XPath.SetValue oMyMap, strXPath




   Set oMyNewColumn = oMyList.ListColumns.Add
   strXPath = "/Animal/entry/AnimalGroup"
   oMyList.ListColumns(3).XPath.SetValue oMyMap, strXPath

   oMyList.ListColumns(1).Name = "AnimalID"   oMyList.ListColumns(2).Name = "AnimalName"
   oMyList.ListColumns(3).Name = "AnimalGroup"

Now, How can I add a column let's say G coloumn in excel as their child node.

Table show below

ABCDEFG
Animal IdAnimal NameAnimal GroupNo
123DogCanine1
122CatFeline2
121SnakeReptile3

<tbody>
</tbody>


I updated my post thru reply. Dont know where the edit button is.
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,332
Members
448,566
Latest member
Nickdozaj

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top