Solved

Extracting xml values nodes with vbscript

Posted on 2013-01-09
2
591 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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
scripting, exchange 35 40
Need to learn promise API 2 28
XSLT Help 12 20
AWS CLI - Instances, Volumes & Tagging 2 20
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Many times as a report developer I've been asked to display normalized data such as three rows with values Jack, Joe, and Bob as a single comma-separated string such as 'Jack, Joe, Bob', and vice versa.  Here's how to do it. 
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…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

759 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

21 Experts available now in Live!

Get 1:1 Help Now