Extract data from XML file using Powershell

Question

I want to extract download URLs from xml report generated by MBSA scan. Previously I want to use regular expressions but some people dissuade me from this idea so now I am trying to use XmlDocument Class but I have a problem to deal with this.

Here is small part of MBSA scan report:

<XMLOut>
    <CatalogInfo>
        <CreationDate>2018-07-10T10:38:04Z</CreationDate>
    </CatalogInfo>
    <Check ID="500" Grade="5" Type="5" Cat="1" Rank="1" Name="SQL Server Security Updates" URL1="Help/Check5311.html" URL2="Help/Check5311fix.html" GroupID="0a4c6c73-8887-4d7f-9cbe-d08fa8fa9d1e" GroupName="SQL Server">
        <Advice>No security updates are missing.</Advice>
        <Detail>
            <UpdateData ID="MS06-061" GUID="07609d43-d518-4e77-856e-d1b316d1b8a8" BulletinID="MS06-061" KBID="925673" Type="1" IsInstalled="true" Severity="4" RestartRequired="false">
                <Title>MSXML 6.0 RTM Security Update  (925673)</Title>
                <References>
                    <BulletinURL>http://www.microsoft.com/technet/security/bulletin/MS06-061.mspx</BulletinURL>
                    <InformationURL>http://support.microsoft.com/kb/925673</InformationURL>
                    <DownloadURL>http://www.download.windowsupdate.com/msdownload/update/v3-19990518/cabpool/msxml6-kb925673-enu-amd64_cc347d98b9fe1e417cb73f0ddf004d1f94a4bfcf.exe</DownloadURL>
                </References>
                <OtherIDs>
                    <OtherID Type="CVE">CVE-2006-4685</OtherID>
                    <OtherID Type="CVE">CVE-2006-4686</OtherID>
                </OtherIDs>
            </UpdateData>
        </Detail>
    </Check>
    <Check ID="500" Grade="2" Type="5" Cat="1" Rank="1" Name="Windows Security Updates" URL1="Help/Check5311.html" URL2="Help/Check5311fix.html" GroupID="6964aab4-c5b5-43bd-a17d-ffb4346a8e1d" GroupName="Windows">
        <Advice>141 security updates are missing. 4 service packs or update rollups are missing.</Advice>
        <Detail>
            <UpdateData ID="MS15-118" GUID="a0c35f05-9153-439f-a8e9-109d4324c750" BulletinID="MS15-118" KBID="3097989" Type="1" IsInstalled="false" Severity="3" RestartRequired="false">
                <Title>Security Update for Microsoft .NET Framework 3.5.1 on Windows 7 and Windows Server 2008 R2 SP1 for x64-based Systems (KB3097989)</Title>
                <References>
                    <BulletinURL>http://www.microsoft.com/technet/security/bulletin/MS15-118.mspx</BulletinURL>
                    <InformationURL>http://support.microsoft.com/kb/3097989</InformationURL>
                    <DownloadURL>http://download.windowsupdate.com/d/msdownload/update/software/secu/2015/10/windows6.1-kb3097989-x64_892eaa91fcd986ed1249fceb875118bd63646266.cab</DownloadURL>
                </References>
            </UpdateData>
            <UpdateData ID="MS14-046" GUID="ee136505-4841-4e95-9e60-ca2f84f60c12" BulletinID="MS14-046" KBID="2943357" Type="1" IsInstalled="false" Severity="3" RestartRequired="false">
                <Title>Security Update for Microsoft .NET Framework 3.5.1 on Windows 7 and Windows Server 2008 R2 SP1 for x64-based Systems (KB2943357)</Title>
                <References>
                    <BulletinURL>http://www.microsoft.com/technet/security/bulletin/MS14-046.mspx</BulletinURL>
                    <InformationURL>http://support.microsoft.com/kb/2943357</InformationURL>
                    <DownloadURL>http://download.windowsupdate.com/c/msdownload/update/software/secu/2014/07/windows6.1-kb2943357-x64_dc59f4f51d16484d7b72cb38d8b8931f7e38e524.cab</DownloadURL>
                </References>
            </UpdateData>
            <UpdateData ID="MS15-029" GUID="322e5906-e2ae-4fb0-b297-876725555c09" BulletinID="MS15-029" KBID="3035126" Type="1" IsInstalled="false" Severity="3" RestartRequired="false">
                <Title>Security Update for Windows 7 for x64-based Systems (KB3035126)</Title>
                <References>
                    <BulletinURL>http://www.microsoft.com/technet/security/bulletin/MS15-029.mspx</BulletinURL>
                    <InformationURL>http://support.microsoft.com/kb/3035126</InformationURL>
                    <DownloadURL>http://download.windowsupdate.com/d/msdownload/update/software/secu/2015/02/windows6.1-kb3035126-x64_adbc52e8abd005e2e8b9e02325cfe45717a2b0ee.cab</DownloadURL>
                </References>
            </UpdateData>
            MANY MANY MORE...
            <UpdateData ID="MS11-100" GUID="57260dfe-227c-45e3-9ffc-2fc77a67f95a" BulletinID="MS11-100" KBID="2656356" Type="1" IsInstalled="false" Severity="4" RestartRequired="false">
                <Title>Security Update for Microsoft .NET Framework 3.5.1 on Windows 7 and Windows Server 2008 R2 SP1 for x64-based Systems (KB2656356)</Title>
                <References>
                    <BulletinURL>http://www.microsoft.com/technet/security/bulletin/MS11-100.mspx</BulletinURL>
                    <InformationURL>http://go.microsoft.com/fwlink/?LinkID=237378</InformationURL>
                    <DownloadURL>http://download.windowsupdate.com/msdownload/update/software/secu/2011/12/windows6.1-kb2656356-x64_01b0f5428ef6eb2782e6f2c617f06fba8bbf4460.cab</DownloadURL>
                </References>
            </UpdateData>
        </Detail>
    </Check>
    <Check ID="180" Grade="4" Type="1" Cat="1" Rank="10" Name="Incomplete Updates" URL1="Help/Check5340.html" URL2="Help/Check5340fix.html" >
        <Advice>No incomplete software update installations were found.</Advice>
    </Check>
</XMLOut>

I only want download URLs from "Check" node whose name is "Windows Security Updates".

So far I have following code:

[xml]$MBSAscann = Get-Content -Path "C:\Users\AAA\Desktop\results1.xml"
$WindowsSecuritUpdatesNode = $MBSAscann.SelectNodes("//Check") | ?{$_.Name -eq "Windows Security Updates"}

I tried to use such thing:

$WindowsSecuritUpdatesNode.Detail.UpdateData.References.DownloadURL

but this does not work (this command works only up to "UpdateData": $WindowsSecuritUpdatesNode.Detail.UpdateData).

I tried also

$WindowsSecuritUpdatesNode.SelectNodes("//DownloadURL")

but this returned me download URLs from all "Check" nodes, not only from this one whose name is "Windows Security Updates".

I stuck and I am lack of ideas how to solve this.

Define "does not work". $WindowsSecuritUpdatesNode.Detail.UpdateData.References.DownloadURL gives me a list of the values of the <DownloadURL> nodes as I would have expected. SelectNodes("//DownloadURL") doesn't work b/c you're selecting from the document root. You need to select from the current node (SelectNodes('.//DownloadURL'), note the leading dot). — Ansgar Wiechers
– Ansgar Wiechers, Commented Aug 13, 2018 at 13:18
@AnsgarWiechers Thank you very much! $WindowsSecuritUpdatesNode.Detail.UpdateData.References.DownloadURL now works (I do not know what I was previously typing, but it didn't return any value. Sorry for confusion). SelectNodes('.//DownloadURL') also works perfect! — Kubaba
– Kubaba, Commented Aug 13, 2018 at 13:25
@Kubaba Doing this [xml]$doc = Get-Content -Path $path bears the risk of silently breaking the data in an XML document, because there is no reliable way for Get-Content to guess the file encoding. Please always load XML documents like this $doc = New-Object xml; $doc.Load($path). The XML parser has automatic file encoding detection, use it. — Tomalak
– Tomalak, Commented Aug 13, 2018 at 15:37

Peter M. · Accepted Answer · 2018-08-13 15:30:48Z

1

Sometimes the Select-Xml cmdlet is more flexible for parsing XML and it might even be faster (but I have not verified this)

(Select-Xml -Content $XmlOut -XPath "XMLOut/Check[@Name='Windows Security Updates']/descendant::DownloadURL").node."#text"

$XmlOut contains your XML.

answered Aug 13, 2018 at 15:30

Peter M.

9481 gold badge8 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Extract data from XML file using Powershell

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related