asked on

Aggregate function in XML

The following under 10 statements VBA code

Sub learnXPath_02_29_2013()
Const XML_TEXT As String = _
"<Projects>" & _
"<Project Name='LLoyd_George_LV' Path='\\igorinspiron\pacrat\expert\Lloyd_George_LV\' Ahu='0' Chi='0' Hyd='0' Met='0' CreatedOn='2/18/2013'/>" & _
"<Project Name='nsabig4-SQL' Path='\\igorinspiron\Pacrat\Expert\NSABIG4\' Ahu='213' Chi='39' Hyd='1' Met='114' CreatedOn='2/18/2013 7:25:07 PM'/>" & _
"<Project Name='jhmi-SQL' Path='\\dev2\clientsites\jhmi\' Ahu='0' Chi='0' Hyd='0' Met='0' CreatedOn='2/18/2013 7:25:07 PM'/>" & _
"<Project Name='NIH_TWINBROOKS_3-SQL' Path='C:\_igor\PACRAT\nih_twinbrooks_3-sql\' Ahu='1' Chi='0' Hyd='0' Met='0' CreatedOn='2/18/2013 7:25:07 PM'/>" & _
"<Project Name='Chemistry_PU' Path='D:\Pacrat\Princeton\B75_chemistry\' Ahu='0' Chi='0' Hyd='0' Met='0' CreatedOn='2/18/2013 7:25:07 PM'/>" & _
"<Project Name='INC1' Path='\\igorlaptop\pacrat\nsa\big4\' Ahu='55' Chi='14' Hyd='93' Met='93' CreatedOn='2/18/2013 7:25:07 PM'/>" & _
"<Project Name='LT21-SQL' Path='\\fdefs1\pacrat\LT21\LT21-SQL\' Ahu='226' Chi='80' Hyd='2' Met='2' CreatedOn='2/18/2013 7:25:07 PM'/>" & _
"<Project Name='INC1-SQL' Path='\\igorlaptop\c$\_igor\pacrat\nsa\big4\' Ahu='55' Chi='14' Hyd='93' Met='93' CreatedOn='2/18/2013 7:25:07 PM'/>" & _
"<Project Name='nsabig4' Path='\\dev2\ClientSites\IMakedon\NSA 2009\' Ahu='64' Chi='20' Hyd='1' Met='93' CreatedOn='2/18/2013 7:25:07 PM'/>" & _
"<Project Name='St_Mary_College' Path='\\igorinspiron\pacrat\expert\St_Mary_College\' Ahu='0' Chi='0' Hyd='0' Met='0' CreatedOn='2/18/20'/>" & _
"<Project Name='UNH' Path='\\Igorinspiron\Pacrat\Expert\UNH\' Ahu='9' Chi='0' Hyd='0' Met='0' CreatedOn='2/18/2013 7:25:07 PM'/>" & _
"<Project Name='unc' Path='\\Igorinspiron\Pacrat\Expert\UNC\' Ahu='14' Chi='0' Hyd='0' Met='0' CreatedOn='2/18/2013 7:25:07 PM'/>" & _
"<Project Name='lt21' Path='\\dev2\clientsites\lt21\lt21-sql\' Ahu='0' Chi='0' Hyd='0' Met='0' CreatedOn='2/18/2013 7:25:07 PM'/>" & _
"<Project Name='princeton' Path='\\Igorinspiron\Pacrat\Expert\Prinston_University\B119_Robertson_Hall\' Ahu='0' Chi='0' Hyd='0' Met='0' CreatedOn=''/>" & _
"<Project Name='ANC' Path='\\igorinspiron\pacrat\expert\anc\' Ahu='0' Chi='0' Hyd='0' Met='11' CreatedOn='2/18/2013 7:25:07 PM'/>" & _
"<Project Name='b68' Path='\\igorinspiron\pacrat\Expert\Prinston_University\B68_Fisher_Bendheim\' Ahu='8' Chi='0' Hyd='0' Met='0' CreatedOn=''/>" & _
"<Project Name='B12' Path='\\igorinspiron\pacrat\Expert\b12_JonesHall\' Ahu='0' Chi='0' Hyd='0' Met='0' CreatedOn='2/18/2013 7:25:07 PM'/>" & _
"<Project Name='B164' Path='\\igorinspiron\pacrat\Expert\B164_Lewis_Library\' Ahu='0' Chi='0' Hyd='0' Met='0' CreatedOn='2/18/2013 7:25:07 PM'/>" & _
"</Projects>"
    Dim dom As DOMDocument
    Set dom = New DOMDocument
    Debug.Print "dom.LoadXML(XML_TEXT) = " & dom.LoadXML(XML_TEXT)
    Debug.Print "dom.XML=" & vbNewLine & Replace(dom.XML, "><", ">" & vbNewLine & "<")
    Debug.Print "Length=" & dom.getElementsByTagName("Project").Length
    Set dom = Nothing
End Sub

Open in new window

returns this result in MS Access Devenv Immediate Window:

dom.LoadXML(XML_TEXT) = True
dom.XML=
<Projects>
<Project Name="LLoyd_George_LV" Path="\\igorinspiron\pacrat\expert\Lloyd_George_LV\" Ahu="0" Chi="0" Hyd="0" Met="0" CreatedOn="2/18/2013"/>
<Project Name="nsabig4-SQL" Path="\\igorinspiron\Pacrat\Expert\NSABIG4\" Ahu="213" Chi="39" Hyd="1" Met="114" CreatedOn="2/18/2013 7:25:07 PM"/>
<Project Name="jhmi-SQL" Path="\\dev2\clientsites\jhmi\" Ahu="0" Chi="0" Hyd="0" Met="0" CreatedOn="2/18/2013 7:25:07 PM"/>
<Project Name="NIH_TWINBROOKS_3-SQL" Path="C:\_igor\PACRAT\nih_twinbrooks_3-sql\" Ahu="1" Chi="0" Hyd="0" Met="0" CreatedOn="2/18/2013 7:25:07 PM"/>
<Project Name="Chemistry_PU" Path="D:\Pacrat\Princeton\B75_chemistry\" Ahu="0" Chi="0" Hyd="0" Met="0" CreatedOn="2/18/2013 7:25:07 PM"/>
<Project Name="INC1" Path="\\igorlaptop\pacrat\nsa\big4\" Ahu="55" Chi="14" Hyd="93" Met="93" CreatedOn="2/18/2013 7:25:07 PM"/>
<Project Name="LT21-SQL" Path="\\fdefs1\pacrat\LT21\LT21-SQL\" Ahu="226" Chi="80" Hyd="2" Met="2" CreatedOn="2/18/2013 7:25:07 PM"/>
<Project Name="INC1-SQL" Path="\\igorlaptop\c$\_igor\pacrat\nsa\big4\" Ahu="55" Chi="14" Hyd="93" Met="93" CreatedOn="2/18/2013 7:25:07 PM"/>
<Project Name="nsabig4" Path="\\dev2\ClientSites\IMakedon\NSA 2009\" Ahu="64" Chi="20" Hyd="1" Met="93" CreatedOn="2/18/2013 7:25:07 PM"/>
<Project Name="St_Mary_College" Path="\\igorinspiron\pacrat\expert\St_Mary_College\" Ahu="0" Chi="0" Hyd="0" Met="0" CreatedOn="2/18/20"/>
<Project Name="UNH" Path="\\Igorinspiron\Pacrat\Expert\UNH\" Ahu="9" Chi="0" Hyd="0" Met="0" CreatedOn="2/18/2013 7:25:07 PM"/>
<Project Name="unc" Path="\\Igorinspiron\Pacrat\Expert\UNC\" Ahu="14" Chi="0" Hyd="0" Met="0" CreatedOn="2/18/2013 7:25:07 PM"/>
<Project Name="lt21" Path="\\dev2\clientsites\lt21\lt21-sql\" Ahu="0" Chi="0" Hyd="0" Met="0" CreatedOn="2/18/2013 7:25:07 PM"/>
<Project Name="princeton" Path="\\Igorinspiron\Pacrat\Expert\Prinston_University\B119_Robertson_Hall\" Ahu="0" Chi="0" Hyd="0" Met="0" CreatedOn=""/>
<Project Name="ANC" Path="\\igorinspiron\pacrat\expert\anc\" Ahu="0" Chi="0" Hyd="0" Met="11" CreatedOn="2/18/2013 7:25:07 PM"/>
<Project Name="b68" Path="\\igorinspiron\pacrat\Expert\Prinston_University\B68_Fisher_Bendheim\" Ahu="8" Chi="0" Hyd="0" Met="0" CreatedOn=""/>
<Project Name="B12" Path="\\igorinspiron\pacrat\Expert\b12_JonesHall\" Ahu="0" Chi="0" Hyd="0" Met="0" CreatedOn="2/18/2013 7:25:07 PM"/>
<Project Name="B164" Path="\\igorinspiron\pacrat\Expert\B164_Lewis_Library\" Ahu="0" Chi="0" Hyd="0" Met="0" CreatedOn="2/18/2013 7:25:07 PM"/>
</Projects>
Length=18

Open in new window

Please help me with writing (the best of course) VBA code that prints the sum of all "AHU" attribute values in the above context. Loopless code is preferable.
Thanks.

midfde

ASKER

Code

Debug.Print 0 + 213 + 0 + 1 + ...

Open in new window

does not count.

zc2

Here's a code with a loop:

    Dim Ahu, Ahus, res
    res = 0
    Set Ahus = dom.documentElement.selectNodes("//@Ahu")
   	for each Ahu In Ahus
   	    res = res + CInt(Ahu.text)
    next
    Debug.Print "Result=" & res

Open in new window

This is a loopless code (I won't call it the best, though):

    Dim XSL_TEXT : XSL_TEXT = "<?xml version='1.0'?>" & _
"<xsl:stylesheet version='1.0' xmlns:xsl='http://www.w3.org/1999/XSL/Transform'>" & _
"<xsl:output method='text'/>" & _
"	<xsl:template match='/'>" & _
"		<xsl:value-of select='sum(//@Ahu)'/>" & _
"	</xsl:template>" & _
"</xsl:stylesheet>"
    Dim xsl As DOMDocument
    Set dxsl = New DOMDocument
    xsl.LoadXML(XSL_TEXT)
    Debug.Print "Result=" & dom.transformNode(xsl)

Open in new window

midfde

ASKER

It may be a naive question, but is it possible to somehow insert an inline aggregate Sum() function in XPath for

dom.selectSingleNode(<XPath Expression>)?

Open in new window

Oh, it was so long ago...

zc2

I believe selectSingleNode() returns a nodeset, it's impossible to make it return a scalar value.

midfde

ASKER

I meant somehow return a node whose "text" value is a sum of... or something like that...

ASKER CERTIFIED SOLUTION

zc2

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

midfde

ASKER

It was so long ago when I used to work with XML / DOM technologies, that I thought I simply forgot a lot, and was easy to be reminded with something simple.
Thanks, zc2, anyway.

zc2

Welcome