2014-07-02

Retrieving values from XML with PowerShell


This PowerShell script looks up  and returns results from a mini-database that uses XML as its back-end. For the actual program that edits and returns results graphically, I used Java - this was an exercise in learning PowerShell's XML functions. As can be seen below, the in-built XML handling makes working with XML files very straightforward indeed.

The structure of the XML file I'm using to test is:

<?xml version="1.0" encoding="UTF-8"?>
<recordList>
    <record id="0">
        <title>Some Title</title>
        <category>Some Cat</category>
        <notes>Some Notes</notes>
    </record>
    <record id="1">
        <title>Great Expectations</title>
        <category>Categorical</category>
        <notes>Egghead likes his bookie-wook</notes>
    </record>
</recordList>


Each record element has an associated ID attribute and contains the elements Title, Category and Notes.


Now for the script itself. First, define the parameters accepted from the command line. Only search type is mandatory as the returnAllRecords function (called by passing 'all' as the parameter) doesn't require a search term.

[CmdletBinding()]
param(
    [parameter(Mandatory = $true)]
    [ValidateNotNullOrEmpty()]
    [string]$searchType,
    [parameter(Mandatory = $false)]
    [ValidateNotNullOrEmpty()]
    [string]$searchTerm
)



Create an object and load the XML file - in this case: c:\temp\test1.xml:

[System.Xml.XmlDocument]$xmlFile = New-Object System.Xml.XmlDocument
$xmlFile.load("c:\temp\test1.xml")



This function performs lookup by Title. One variable is expected - the search term.
 

Select the /recordList/record node, then loop through each record, checking if the title is like the search term. The * act as wildcards.
 

When each record is read in, the various elements can be read by appending the name to the end of the object, joined with a dot - in this case, title, category and notes
 

PowerShell uses the ` as the escape character - the usual \n, \t and \r from C and similar languages becomes `n, `t and `r.
 

The quotes in the write-host output aren't strictly needed in this case - they're mainly there to provide a visual distinction.

function lookupByTitle ($searchTermTitle) {
    $counter = 0
    $lookup = $xmlFile.SelectNodes("/recordList/record")
    write-host Results for $searchTermTitle in Title:
    foreach ($record in $lookup) {
        if ($record.title -like "*$searchTermTitle*") {
            write-host "Title:`t" $record.title "`t`tCategory:`t" $record.category "`t`tNotes:`t" $record.notes`n
            $counter++
        }
    }
    write-host "Displayed:"$counter "records.`n"
}



Category lookups are handled by a nearly identical function as for titles - category is appended to the object in place of title:

function lookupByCategory ($searchTermCategory) {
    $counter = 0
    $lookup = $xmlFile.SelectNodes("/recordList/record")
    write-host Results for $searchTermCategory in Category:
    foreach ($record in $lookup) {
        if ($record.category -like "*$searchTermCategory*") {
            write-host "Title:`t" $record.title "`t`tCategory:`t" $record.category "`t`tNotes:`t" $record.notes`n
            $counter++
        }
    }
    write-host "Displayed:"$counter "records.`n"
}




This function looks up a record by its ID - in this case, ID isn't an element, it's an attribute of the record element (e.g., <record id="9">), so the method to look it up is a little different.


First off, as the value being passed is a string, it needs to be converted to an integer. To avoid throwing an error when attempting to convert a non-numeric value, the custom function isNumeric() is called to check (this function is declared later in the script). If it isn't a number, no conversion is attempted and an error message is shown. An alternative would be to wrap the line $recordID = [int]$searchTermRecord in a try/catch block.

  • To find the number of records, Count is appended to the element path.
  • In order to allow for a record ID of 0, 1 is subtracted from the value of $totalRecords before testing.
  • A specific record's values are returned by passing the ID in brackets to the full path of the record element node, for example: $xmlFile.recordList.record[1].title

function lookupByRecord ($searchTermRecord) {
    if (isNumeric($searchTermRecord) -is True) {
        $recordID = [int]$searchTermRecord
        $totalRecords = $xmlFile.recordList.record.Count
        write-host "`nTotal records: " $totalRecords
        write-host "Looking up record with ID: " $searchTermRecord`n
        if ($searchTermRecord -gt $totalRecords-1 -or $searchTermRecord -lt 0) {
            write-host "No such record.`n"
        }
        else {
            write-host "Title:`t" $xmlFile.recordList.record[$recordID].title`t`t"Category:`t" $xmlFile.recordList.record[$recordID].category`t`t"Notes:`t" $xmlFile.recordList.record[$recordID].notes`n
        }
    }
    else {
        write-host "Integer expected.`n"
    }
}




The simplest lookup involves returning all records. This function loops through every record and returns the ID, Title, Category and Notes.

function returnAllRecords {
    write-host `n"Returning all records:"`n
    $returnRecords = $xmlFile.SelectNodes("/recordList/record")
    foreach ($record in $returnRecords) {
        write-host Record ID: $record.id`n`tTitle: $record.title`n`tCategory: $record.category`n`tNotes: $record.notes`n
    }
}



The isNumeric function is used by lookupByRecord to test if the passed value is a number or not, using a short Regular Expression. Integers are accepted, as are floating point numbers (anything after the floating point is dropped during the conversion to integer).

NB: http://www.regexr.com/ is a good place to test RegEx before using in scripts.

function isNumeric ($testValue) {
    return $testValue -match "^[\d\.]+$"
}



After declaring all the functions, do a short if/elseif/else to see what sort of search we're doing, then call the function.

As search term isn't mandatory, check if one exists before calling lookupByTitle, lookupByCategory or lookupByRecord.



if ($searchType -like "all") {
    returnAllRecords
}
elseif ($searchType -like "title") {
    if (!$searchTerm) {
        write-host "Search term required for search type: Title.`n"
    }
    else {
        lookupByTitle -searchTermTitle $searchTerm
    }
}
elseif ($searchType -like "category") {
    if (!$searchTerm) {
        write-host "Search term required for search type: Category.`n"
    }
    else {
        lookupByCategory -searchTermCategory $searchTerm
    }
}
elseif ($searchType -like "record") {
    if (!$searchTerm) {
        write-host "Search term required for search type: Record.`n"
    }
    else {
        lookupByRecord -searchTermRecord $searchTerm
    }
}
else {
    write-host "Invalid search type.`n"
}



And the complete script:

1:  <#  
2:  .SYNOPSIS  
3:  Return records from XML-based database  
4:  .DESCRIPTION  
5:  Search by ID (Record), Title, Category or return all records  
6:  .PARAMETER searchType  
7:  The type of search to perform: all/title/category/record  
8:  .EXAMPLE  
9:  .\readXml all  
10:  .EXAMPLE  
11:  .\readXml title "Some Title"  
12:  #>  
13:    
14:  [CmdletBinding()]  
15:  param(  
16:       [parameter(Mandatory = $true)]  
17:       [ValidateNotNullOrEmpty()]  
18:       [string]$searchType,  
19:       [parameter(Mandatory = $false)]  
20:       [ValidateNotNullOrEmpty()]  
21:       [string]$searchTerm  
22:  )  
23:    
24:  [System.Xml.XmlDocument]$xmlFile = New-Object System.Xml.XmlDocument  
25:  $xmlFile.load("c:\temp\test1.xml")  
26:    
27:  function lookupByTitle ($searchTermTitle) {  
28:       $counter = 0  
29:       $lookup = $xmlFile.SelectNodes("/recordList/record")  
30:       write-host "`nResults for" $searchTermTitle "in Title:`n"  
31:       foreach ($record in $lookup) {  
32:            if ($record.title -like "*$searchTermTitle*") {  
33:                 write-host "Title:`t" $record.title "`t`tCategory:`t" $record.category "`t`tNotes:`t" $record.notes`n  
34:                 $counter++  
35:            }  
36:       }  
37:       write-host "Displayed:"$counter "records.`n"  
38:  }  
39:    
40:  function lookupByCategory ($searchTermCategory) {  
41:       $counter = 0  
42:       $lookup = $xmlFile.SelectNodes("/recordList/record")  
43:       write-host "`nResults for" $searchTermCategory "in Category:`n"  
44:       foreach ($record in $lookup) {  
45:            if ($record.category -like "*$searchTermCategory*") {  
46:                 write-host "Title:`t" $record.title "`t`tCategory:`t" $record.category "`t`tNotes:`t" $record.notes`n  
47:                 $counter++  
48:            }  
49:       }  
50:       write-host "Displayed:"$counter "records.`n"  
51:  }  
52:    
53:  function lookupByRecord ($searchTermRecord) {  
54:       if (isNumeric($searchTermRecord) -is True) {  
55:            $recordID = [int]$searchTermRecord  
56:            $totalRecords = $xmlFile.recordList.record.Count  
57:            write-host "`nTotal records: " $totalRecords  
58:            write-host "Looking up record with ID: " $searchTermRecord`n  
59:            if ($searchTermRecord -gt $totalRecords-1 -or $searchTermRecord -lt 0) {  
60:                 write-host "No such record.`n"  
61:            }  
62:            else {  
63:                 write-host "Title:`t" $xmlFile.recordList.record[$recordID].title`t`t"Category:`t" $xmlFile.recordList.record[$recordID].category`t`t"Notes:`t" $xmlFile.recordList.record[$recordID].notes`n  
64:            }  
65:       }  
66:       else {  
67:            write-host "Integer expected.`n"  
68:       }  
69:  }  
70:    
71:  function returnAllRecords {  
72:       write-host `n"Returning all records:"`n  
73:       $returnRecords = $xmlFile.SelectNodes("/recordList/record")  
74:       foreach ($record in $returnRecords) {  
75:            write-host Record ID: $record.id`n`tTitle: $record.title`n`tCategory: $record.category`n`tNotes: $record.notes`n  
76:       }  
77:  }  
78:    
79:  function isNumeric ($testValue) {  
80:       return $testValue -match "^[\d\.]+$"  
81:  }  
82:    
83:  if ($searchType -like "all") {  
84:       returnAllRecords  
85:  }  
86:  elseif ($searchType -like "title") {  
87:       if (!$searchTerm) {  
88:            write-host "Search term required for search type: Title.`n"  
89:       }  
90:       else {  
91:            lookupByTitle -searchTermTitle $searchTerm  
92:       }  
93:  }  
94:  elseif ($searchType -like "category") {  
95:       if (!$searchTerm) {  
96:            write-host "Search term required for search type: Category.`n"  
97:       }  
98:       else {  
99:            lookupByCategory -searchTermCategory $searchTerm  
100:       }  
101:  }  
102:  elseif ($searchType -like "record") {  
103:       if (!$searchTerm) {  
104:            write-host "Search term required for search type: Record.`n"  
105:       }  
106:       else {  
107:            lookupByRecord -searchTermRecord $searchTerm  
108:       }  
109:  }  
110:  else {  
111:       write-host "Invalid search type.`n"  
112:  }  
113:    


Lastly, for good measure, some screenshots:





No comments:

Post a Comment