none
Extract Data from XML RRS feed

  • Question

  • Hi,

    I have an XML with this structure (the <Entry> parameters are much more):

    <?xml version="1.0" encoding="utf-8" standalone="yes"?>
    <KeePassFile>
    	<Root>
    		<Group>
    			<UUID>/VdBmHZszkOtUrNEclLTWA==</UUID>
    			<Name>Test</Name>
    			<Notes></Notes>
    			<IconID>49</IconID>
    			<Times>
    				<CreationTime>2019-01-16T07:38:08Z</CreationTime>
    			</Times>
    			<Group>
    				<UUID>+MXh9LS3DEaMFJtGn9vEsA==</UUID>
    				<Name>General</Name>
    				<Notes></Notes>
    				<IconID>48</IconID>
    				<Times>
    					<CreationTime>2019-01-16T07:38:16Z</CreationTime>
    				</Times>
    				<Entry>
    					<UUID>kkB8TmQ36Ua00yvkDW7X7A==</UUID>
    					<Times>
    						<CreationTime>2019-01-16T09:22:10Z</CreationTime>
    						<LastModificationTime>2019-01-16T09:38:40Z</LastModificationTime>
    						<LastAccessTime>2019-01-16T09:38:40Z</LastAccessTime>
    						<ExpiryTime>2019-01-19T00:00:00Z</ExpiryTime>
    						<Expires>True</Expires>
    						<UsageCount>2</UsageCount>
    						<LocationChanged>2019-01-16T09:22:10Z</LocationChanged>
    					</Times>
    					<String>
    						<Key>Notes</Key>
    						<Value></Value>
    					</String>
    					<String>
    						<Key>Password</Key>
    						<Value ProtectInMemory="True">ihrc4hsOormHChu1mLLi</Value>
    					</String>
    					<String>
    						<Key>Title</Key>
    						<Value>test</Value>
    					</String>
    					<String>
    						<Key>URL</Key>
    						<Value></Value>
    					</String>
    					<String>
    						<Key>UserName</Key>
    						<Value>testtesttest</Value>
    					</String>
    					<AutoType>
    						<Enabled>True</Enabled>
    						<DataTransferObfuscation>0</DataTransferObfuscation>
    					</AutoType>
    				</Entry>
    				<Entry>
    					<UUID>kkB8TmQ36Ua00yvkDW7X7A==</UUID>
    					<Times>
    						<CreationTime>2019-01-16T09:22:10Z</CreationTime>
    						<LastModificationTime>2019-01-16T09:38:40Z</LastModificationTime>
    						<LastAccessTime>2019-01-16T09:38:40Z</LastAccessTime>
    						<ExpiryTime>2019-01-20T00:00:00Z</ExpiryTime>
    						<Expires>True</Expires>
    						<UsageCount>2</UsageCount>
    						<LocationChanged>2019-01-16T09:22:10Z</LocationChanged>
    					</Times>
    					<String>
    						<Key>Notes</Key>
    						<Value></Value>
    					</String>
    					<String>
    						<Key>Password</Key>
    						<Value ProtectInMemory="True">ihrc4hsOormHChu1mLLi</Value>
    					</String>
    					<String>
    						<Key>Title</Key>
    						<Value>test2</Value>
    					</String>
    					<String>
    						<Key>URL</Key>
    						<Value></Value>
    					</String>
    					<String>
    						<Key>UserName</Key>
    						<Value>testtesttest</Value>
    					</String>
    					<AutoType>
    						<Enabled>True</Enabled>
    						<DataTransferObfuscation>0</DataTransferObfuscation>
    					</AutoType>
    				</Entry>
    			</Group>
    	</Root>
    </KeePassFile>

    I'd like to extract for each <Entry> where <Expires> is True, the <Value> of <Key> Title, and the <ExpiryTime>

    I'm starting my script just extracting the <Value> for now but I can't understand why I get also the Value where Expires is not True:

    if($xml.KeePassFile.Root.Group.Group.Entry.Times.Expires -eq $True) {
    
        if($xml.KeePassFile.Root.Group.Group.Entry | Where-Object {$_.UUID -ne $null})  {
    
        
        $xml.KeePassFile.Root.Group.Group.Entry.String | Where-Object {$_.Key -eq "Title"} | Select-Object Value
        }
    }

    A little help would be much appreciated


    • Moved by Bill_Stewart Friday, March 15, 2019 6:10 PM This is not "fix/debug/rewrite my script for me" forum
    Thursday, January 17, 2019 8:48 AM

All replies

  • You need a data extractor.  It is like tooth extractor but more painful.

    To do what you asked you would use an XPath query.  It can all be done I  one short line of code.

    First start by posting XML that is not broken.

    The code is:

    $xml.SelectNodes('//Entry/Times') | Where{$_.Expires -eq 'True'}

    All of the "Entry" I  the example above is "True".


    \_(ツ)_/

    Thursday, January 17, 2019 9:06 AM
  • To get all entry nodes where "Expires" equal "True" we would do this.

    $nodes = $xml.SelectNodes('//Entry[Times/Expires="True"]')
    

    Which results in this:

    PS D:\scripts> $nodes[0].Times
    
    
    CreationTime         : 2019-01-16T09:22:10Z
    LastModificationTime : 2019-01-16T09:38:40Z
    LastAccessTime       : 2019-01-16T09:38:40Z
    ExpiryTime           : 2019-01-19T00:00:00Z
    Expires              : True
    UsageCount           : 2
    LocationChanged      : 2019-01-16T09:22:10Z
    
    
    
    PS D:\scripts> $nodes[0].String
    
    Key      Value
    ---      -----
    Notes
    Password Value
    Title    test
    URL
    UserName testtesttest
    
    
    PS D:\scripts>


    \_(ツ)_/

    Thursday, January 17, 2019 9:13 AM
  • If you need to force the whole path to be sure you are at the correct starting node then you can do thi;

    $xml.SelectNodes('/KeePassFile/Root/Group/Entry[Times/Expires="True"]')


    \_(ツ)_/

    Thursday, January 17, 2019 9:23 AM
  • I've used:

    [System.Xml.XmlDocument]$file = new-object 
    
    System.Xml.XmlDocument$file.load("path_to_my_xml")
    
    $xml_title=$file.SelectNodes("/KeePassFile/Root/Group/Group/Entry")
    
    foreach ($temp in $xml_title) {    
    
    echo $temp.Times.ExpiryTime + $temp.String.Value
    
    }

    And I get all the info for all the entries. Now I need to figure it out how to just get just the <Value> when <key> is "Title" and filter the result for <Expires> = 'True'

    Edit: I'm close with

    [System.Xml.XmlDocument]$file = new-object System.Xml.XmlDocument
    $file.load("path to xml")
    
    $xml= $file.SelectNodes("/KeePassFile/Root/Group/Group/Entry")
    
    
    
    foreach ($temp in $xml) {
      
      if($temp.Times.Expires -eq 'True') {
    
      echo $temp.String.Value + $temp.Times.ExpiryTime 
    
      }
    }

    I don't want all the data but just <Value> of the <Key> Title and <ExpiryTime> but I don't know how to do it


    • Edited by viciovb Thursday, January 17, 2019 11:41 AM
    Thursday, January 17, 2019 11:33 AM
  • $xml.SelectNodes('/KeePassFile/Root/Group/Entry[Times/Expires="True"]/String[Key="Title"]').Value

    \_(ツ)_/

    Thursday, January 17, 2019 11:45 AM
  • $nodes = $xml.SelectNodes('/KeePassFile/Root/Group/Entry')
    $nodes.SelectNodes('./String[Key="Title"]').Value

    \_(ツ)_/

    Thursday, January 17, 2019 11:49 AM
  • Also:

    ($nodes[0].String |?{$_.Key -eq 'Title'}).Value


    \_(ツ)_/

    Thursday, January 17, 2019 11:52 AM
  • $nodes = $xml.SelectNodes('/KeePassFile/Root/Group/Entry')
    $nodes.SelectNodes('./String[Key="Title"]').Value

    \_(ツ)_/

    This is much better than my solution thank you. Just one last question. How do I get together this with 

    $nodes.SelectNodes('./Times[Expires="True"]').ExpiryTime 

    so I can retrieve the Title value and ExpiryTime together

    Thursday, January 17, 2019 12:24 PM
  • You have to process the returned nodes in a loop.

    $nodes = $xml.SelectNodes('/KeePassFile/Root/Group/Entry[Times/Expires="True"]')
    foreach ($node in $nodes) {
        # extract data from the $node
    }


    \_(ツ)_/

    Thursday, January 17, 2019 5:20 PM
  • You have to process the returned nodes in a loop.

    $nodes = $xml.SelectNodes('/KeePassFile/Root/Group/Entry[Times/Expires="True"]')
    foreach ($node in $nodes) {
        # extract data from the $node
    }


    \_(ツ)_/

    Thank you, the script seems to work fine now
    Friday, January 18, 2019 8:08 AM
  • This is the scripts that works. Probably there is a way to clean it up.

    Now I'm gonna change the script to format the date in a different format than yyyy-MM-ddThh:mm:ssZ 

    $kps  = 'C:\Program Files (x86)\KeePass Password Safe 2\KPScript.exe'
    $kdbx = 'C:\Temp\Test.kdbx'
    $pass = '12345'
    
    $entries = & "$kps" -c:Export "$kdbx" -pw:$pass -Format:"KeePass XML (2.x)" -OutFile:C:\Temp\TargetFile.xml
    $date = (Get-Date).ToString("yyyy-MM-ddThh:mm:ssZ")
    $date2 = (Get-Date).AddDays(2).ToString("yyyy-MM-ddThh:mm:ssZ")
    $abouttoexpire = $null
    $expired = $null
    
    [System.Xml.XmlDocument]$file = new-object System.Xml.XmlDocument
    $file.load("C:\Temp\TargetFile.xml")
    
    $nodes = $file.SelectNodes('/KeePassFile/Root/Group/Group/Entry[Times/Expires="True"]')
    
    foreach ($temp in $nodes) {
    
        if(($temp.Times.ExpiryTime -ge $date -and $temp.Times.ExpiryTime -le $date2)) {
    
        $abouttoexpire += $temp.SelectNodes('./String[Key="Title"]').Value + "`t `t" + $temp.Times.ExpiryTime + "`n"
        }
        
        elseif ($temp.Times.ExpiryTime -le $date) 
        {
        $expired += $temp.SelectNodes('./String[Key="Title"]').Value + "`t `t" + $temp.Times.ExpiryTime + "`n"
        }
    }
    
    if ($abouttoexpire -ne $null -or $expired -ne $null) {
    
    Send-MailMessage -Subject "Password expiration" -Body "The following password will expire soon `n $abouttoexpire `n The following password are expired `n $expired" -To email_add -From email_add -SmtpServer smtp_server -UseSsl
    
    }
    
    
    Remove-Item -Path "C:\Temp\TargetFile.xml"

    Friday, January 18, 2019 10:25 AM