Solved

Extracting xml values nodes with vbscript

Posted on 2013-01-09
2
602 Views
Last Modified: 2013-01-09
Im new with xml and vbscript , I need some help to extract the value of "entrada-fecha-y-hora" of the first line <ev:evento> and "salida-fecha-y-hora" of the second line <ev:evento> to export to an excel file, my vbscript is the following:

Dim s
Dim t
Set oDOM = CreateObject("Msxml2.DOMDocument.3.0")
oDOM.preserveWhiteSpace = False
oDOM.Load strXMLFileToProcess ' I use this to pass the file name
Set Root = oDOM.selectNodes("//dia:detalle-de-eventos/ev:evento")
For x = 0 to Root.lenght - 1
      s = s & Root(x).selectSingleNode("@entrada-fecha-y-hora").text
       t = t & Root(x).selectSingleNode("@salida-fecha-y-hora").text
Next
MsgBox s 'For test only
MsgBox t 'For test only

and the xml file is

<?xml version="1.0" encoding="UTF-16"?>
<!--Documento de exportacion de analisis de asistencia y puntualidad TimeWork-->
<doc:tw-xml-doc-export-analisis-periodo doc-version="1.02" doc-id="01" componente-version-minima-requerida="1" db-fuente="\\Bioserver2\tw\TimeWork Reloj Checador\Datos.TWD" xmlns:doc="http://www.relojchecador.com/twxml/01/1.01/" xmlns:emps="http://www.relojchecador.com/twxml/01/1.01/empleados/" xmlns:emp="http://www.relojchecador.com/twxml/01/1.01/empleados/empleado/" xmlns:per="http://www.relojchecador.com/twxml/01/1.01/empleado/empleado/periodo/" xmlns:dias="http://www.relojchecador.com/twxml/01/1.01/empleado/empleado/periodo/dias/" xmlns:dia="http://www.relojchecador.com/twxml/01/1.01/empleado/empleado/periodo/dias/dia/" xmlns:hor="http://www.relojchecador.com/twxml/01/1.01/empleado/empleado/periodo/dias/dia/horario/" xmlns:ev="http://www.relojchecador.com/twxml/01/1.01/empleado/empleado/periodo/dias/dia/eventos/" xmlns:ri="http://www.relojchecador.com/twxml/01/1.01/empleado/empleado/periodo/dias/dia/registrosinformativos/" xmlns:perm="http://www.relojchecador.com/twxml/01/1.01/empleado/empleado/periodo/dias/dia/permisos/permiso/" xmlns:punt="http://www.relojchecador.com/twxml/01/1.01/empleado/empleado/periodo/puntualidad/">
    <doc:empleados inicio-minimo-fecha-y-hora="#01/04/2013 00:00:00#" fin-maximo-fecha-y-hora="#01/04/2013 23:59:59#" parametroDef1-nombre="Departamento" parametroDef2-nombre="Puesto" parametroDef3-nombre="Subdepartmento" parametroDef4-nombre="Área" parametroDef5-nombre="Ciudad" parametroDef6-nombre="Estado">
        <emps:empleado id="14" numero="39" nombre="Manuel" apellidos="Martinez Vargas" ingreso-fecha-y-hora="#07/01/2004 00:00:00#" inicio-registros-fecha-y-hora="#03/10/2012 00:00:00#" numero-empleado-noi="83" parametro1="Laboratorio" parametro2="Recolector" parametro3="(No asignado)" parametro4="Marketing" parametro5="Ciudad de Mexico" parametro6="Distrito Federal">
            <emp:detalle-de-periodo inicio-fecha-y-hora="#01/04/2013 00:00:00#" fin-fecha-y-hora="#01/04/2013 23:59:59#" nombre-horario="0830x1630/1500-1530">
                <per:detalle-de-dias>
                    <dias:dia fecha-y-hora="#01/04/2013 00:00:00#">
                        <dia:asistencia asistido="1" falta="0" inhabil="0" laborable="1"/>
                        <dia:comida debe-registrar="1" fue-registrada="1" descontar-si-no-registra="0" mins-descontados-por-no-registrar="0"/>
                        <dia:horario id="10" tipo="fijo" tipo-num="0">
                            <hor:limites inicio-fecha-y-hora="#01/04/2013 00:00:00#" fin-fecha-y-hora="#01/04/2013 23:59:59#"/>
                            <hor:trabajo entrada-fecha-y-hora="#01/04/2013 08:30:00#" salida-fecha-y-hora="#01/04/2013 16:30:00#"/>
                            <hor:comida salida-fecha-y-hora="#01/04/2013 15:00:00#" regreso-fecha-y-hora="#01/04/2013 15:30:00#"/>
                        </dia:horario>
                        <dia:puntualidad mins-retardo="0" mins-salida-pre="-18"/>
                        <dia:asignacion-tiempo-extra antes-entrada="0" despues-salida="1" comida="0" dia-festivo="1" mins-minimos-para-considerarse="30"/>
                        <dia:tiempo-trabajo mins-debe-laborar="450" mins-laborado="462" mins-extra="0" mins-a-favor="12"/>
                        <dia:detalle-de-eventos primera-entrada-fecha-y-hora="#01/04/2013 08:34:00#" ultima-salida-fecha-y-hora="#01/04/2013 16:48:00#" hay-eventos-incompletos="0">
                            <ev:evento entrada-fecha-y-hora="#01/04/2013 08:34:00#" salida-fecha-y-hora="#01/04/2013 15:27:00#"/>
                            <ev:evento entrada-fecha-y-hora="#01/04/2013 15:59:00#" salida-fecha-y-hora="#01/04/2013 16:48:00#"/>
                        </dia:detalle-de-eventos>
                        <dia:registros-informativos/>
                        <dia:detalle-de-permisos>
                            <perm:permiso tipo="llegar tarde" tipo-num="0" autoriza="" motivo="Hijo"/>
                        </dia:detalle-de-permisos>
                    </dias:dia>
                </per:detalle-de-dias>
                <per:puntualidad>
                    <punt:retardos totales="0" sin-permiso="0" con-permiso="0" mins-totales="0" mins-sin-permiso="0" mins-con-permiso="0"/>
                    <punt:salidas-pre totales="0" mins-totales="0" sin-permiso="0" mins-sin-permiso="0" con-permiso="0" mins-con-permiso="0"/>
                </per:puntualidad>
                <per:tiempo-trabajo mins-laborado="462" mins-descontado-por-no-registrar-comida="0" mins-debe-laborar="450" mins-debe-laborar-restando-faltas="450" mins-extra="0" mins-extra-restando-faltas="0" mins-a-favor="12" mins-a-favor-restando-faltas="12"/>
                <per:permisos llegar-tarde="1" salir-temprano="0" no-asistir="0" vacaciones="0" sanciones="0" incapacidades="0"/>
                <per:asistencia total-dias="1" dias-laborables="1" dias-laborables-asistidos="1" dias-no-laborables="0" dias-no-laborables-asistidos="0" dias-inhabiles="0" dias-inhabiles-asistidos="0" dias-totales-asistidos="1" faltas="0" faltas-con-permiso-con-sueldo="0" faltas-con-permiso-sin-sueldo="0" ausencias-por-incapacidad="0" faltas-por-sancion="0"/>
            </emp:detalle-de-periodo>
        </emps:empleado>
    </doc:empleados>
</doc:tw-xml-doc-export-analisis-periodo>
0
Comment
Question by:adrigor
2 Comments
 
LVL 35

Accepted Solution

by:
Robert Schutt earned 500 total points
ID: 38761204
Assuming both attributes are present in all evento nodes you could use the following code instead of the for-next loop to fetch the start from the first evento and the end from the last evento:
Set Root = oDOM.selectNodes("//dia:detalle-de-eventos/ev:evento")
s = Root(0).selectSingleNode("@entrada-fecha-y-hora").text
t = Root(Root.length - 1).selectSingleNode("@salida-fecha-y-hora").text
MsgBox "Start: " & s & vbCrLf & "End: " & t

Open in new window

0
 

Author Closing Comment

by:adrigor
ID: 38761445
Thanks!!!! I was going insane...
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Confluence of Individual Knowledge and the Collective Intelligence At this writing (summer 2013) the term API (http://dictionary.reference.com/browse/API?s=t) has made its way into the popular lexicon of the English language.  A few years ago, …
This article will show, step by step, how to integrate R code into a R Sweave document
Learn the basics of strings in Python: declaration, operations, indices, and slicing. Strings are declared with quotations; for example: s = "string": Strings are immutable.: Strings may be concatenated or multiplied using the addition and multiplic…
Learn the basics of while and for loops in Python.  while loops are used for testing while, or until, a condition is met: The structure of a while loop is as follows:     while <condition>:         do something         repeate: The break statement m…

820 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