Extracting Data

[dnayana]

Hi All!

First, thank you in advance to anyone who can provide assistance.

I'm a newbie to XML and this is my first XML project which is a very challenging one.

I've been assigned a task where I need to update data within a 3rd party's XML file (the structure is in XML format but the file extension is pdb -- don't know if this will be an issue) with data from another XML file.

I have created VBA code within MS Access where it exports specific tables to an XML file. It also creates the XSD & XSL files.

I analyzed the 3rd party's XML file. I'm able to locate the elements that contains the data that needs to be updated/replaced with the data within the elements created by the MS Access xsl file.

I'm at a lost of how to write the XML code that will just update/replace the data within the respective elements of the 3rd Party's XML file with the data within the elements of the XML file generated by MS Access.

I'm familiar with VBA and ColdFusion, so if these are much simpler avenues to take to complete this task, please let me know and if possible provide guidance (i.e. links, sample code)

Application.ExportXML
   ObjectType:=acExportTable, _
   DataSource:="tblTable1", _
   DataTarget:="C:\tblTable1.xml", _
   SchemaTarget:="tblTable1Schema.xsd", _
   PresentationTarget:="tblTable1.xsl"

Application.ExportXML
   ObjectType:=acExportTable, _
   DataSource:="tblTable2", _
   DataTarget:="C:\tblTable2.xml", _
   SchemaTarget:="tblTable2Schema.xsd", _
   PresentationTarget:="tblTable2.xsl"

<xsl:template match="//dataroot>
  <xsl:for-each select="tblTable1"> 
     <tr>
       <td><xsl:value-of select="txtField1"></td>
       <td><xsl:value-of select="txtField2"></td>
     </tr>
  </xsl:for-each>
</xsl:template>

<c:Tables>
  <o:Table ID="o555"> 
     <a:name>Name of Table</a:name>  [b][red]Equal to [MS Access] tblTable1.txtField1[/red][/b]
     <a:comment>Table's Desc.</a:comment>[b][red]Equal to [MS Access] tblTable1.txtField2[/red][/b]
       <c:Columns>
         <o:ColumnID="o444">  
           <a:name>txtColumnFieldName</a:name> [b][red]Equal to [MS Access] tblTable2.txtField1[/red][/b]
           <a:comment>txtColumnFieldDesc</a:comment> [b][red]Equal to [MS Access] tblTable2.txtField2[/red][/b]
         </o:Column>
       </c:Columns>
  </o:Table>
</c:Tables>

Again, thank you in advance to anyone who can provide assistance/guidance!

Thanks,
Nicole

 

[tsuji]

[0] I read the xsl output etc, and say to myself, ms office probably need 2-3 more releases to really have a chance to properly handle something as simple as the request using built-in gui and automation to access proper. However, the same functionality can simply be achieved using tools available all along since the turn of year 2000 and free of charge.

[1] To achieve the need, you can use a straightforward, though take some many lines, in a vbs or js as the gluing language.

[1.1] Establish a connection to the db via adodb.connection/adodb.recordset. With proper sql, taken into the account of the criteria targetting the specific places of the 3rd party's xml document (like id of o:Table being o555 looking up table tblTable1 and id of the child o:ColumnID being 0444 looking up tblTable2).

[1.2] From the sql retrieve the targetted txtField1 and txtField2 in the respective tables.

[1.3] At the same time load the 3rd party's .pdb with msxml2.domdocument. Use xpath to locate the target nodes.

[1.4] If those nodes do not exist, create them at position with reference to the proper parent nodes.

[1.5] Assign the text nodevalue to the nodes so located with the db field data as retrieved from [1.2].

[1.6] After the update, save the .pdb and it is done.

[2] The above is the outline of what would appear in any kind of implementation. Since you're familiar with vbs, you can move the whole logic into some module/macro. In that case you need to open the access db to call the function up. Whereas, in vbs/js standalone script, you don't need to have access open up with user interface... But, do what is familiar to you. The purpose is to get the result, not to get pretty and fancy toys.