Solved

Extracting xml values nodes with vbscript

Posted on 2013-01-09
2
606 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

This article will show, step by step, how to integrate R code into a R Sweave document
The Windows functions GetTickCount and timeGetTime retrieve the number of milliseconds since the system was started. However, the value is stored in a DWORD, which means that it wraps around to zero every 49.7 days. This article shows how to solve t…
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…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

738 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