none
Getting #text from an XML node in PS RRS feed

  • Question

  • I have an XML file that I'm pulling down from an ATOM feed from SSRS.

    Here's an example:

    <?xml version="1.0" encoding="utf-8" standalone="yes"?> <feed xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns="http://www.w3.org/2005/Atom"> <title type="text"></title> <id>uuid:dbcb4e96-c7ba-432f-9b71-b3b80adb2326;id=3141</id> <updated>2015-10-16T02:17:15Z</updated> <entry> <id>uuid:dbcb4e96-c7ba-432f-9b71-b3b80adb2326;id=3142</id> <title type="text"></title> <updated>2015-10-16T02:17:15Z</updated> <author /> <content type="application/xml"> <m:properties> <d:CollID>N000001</d:CollID> <d:Type>%</d:Type> <d:netbios_name0>LAPTOP000001</d:netbios_name0> <d:Name_of_the_local_Group>Administrators</d:Name_of_the_local_Group> <d:Account_Contained_within_the_Group>David Smith</d:Account_Contained_within_the_Group> <d:Account_Type>UserAccount</d:Account_Type> <d:Domain_for_Account>CONTOSO-US</d:Domain_for_Account> <d:Type_of_Account>Domain</d:Type_of_Account> <d:Last_HW_Scan m:type="Edm.DateTime">2015-10-15T18:46:39</d:Last_HW_Scan> </m:properties> </content> </entry> <entry> <id>uuid:dbcb4e96-c7ba-432f-9b71-b3b80adb2326;id=3143</id> <title type="text"></title> <updated>2015-10-16T02:17:15Z</updated> <author /> <content type="application/xml"> <m:properties> <d:CollID>N000001</d:CollID> <d:Type>%</d:Type> <d:netbios_name0>LAPTOP000002</d:netbios_name0> <d:Name_of_the_local_Group>Administrators</d:Name_of_the_local_Group> <d:Account_Contained_within_the_Group>Karen.Roberts</d:Account_Contained_within_the_Group> <d:Account_Type>UserAccount</d:Account_Type> <d:Domain_for_Account>CONTOSO-US</d:Domain_for_Account> <d:Type_of_Account>Domain</d:Type_of_Account> <d:Last_HW_Scan m:type="Edm.DateTime">2015-10-15T19:37:02</d:Last_HW_Scan> </m:properties> </content> </entry> </feed>

    And with this code, I get the data:

    Invoke-WebRequest -Uri $link -UseDefaultCredentials -OutFile output.xml
    
    [xml] $xml = get-content ".\output.xml"
    
    $entries = $xml.GetElementsByTagName("m:properties")

    Now at this point if I try to export $entries to csv, it all works except Last_HW_Scan of course does not display the actual text.

    I can manually get that text by doing this:

    $xml.GetElementsByTagName("d:Last_HW_Scan").'#text'

    Is there a way to get the text from something like this:

    $entries | Select netbios_name0,Name_of_the_local_Group,Account_Contained_within_the_Group,Account_Type,Domain_for_Account,Type_of_Account,Last_HW_Scan.'#text'

    Now I just used that as an example, but what really I am looking for is a column called Last_HW_Scan with the text value within that tag.

    Any ideas on that?

    Thanks


    Friday, October 16, 2015 3:25 AM

Answers

  • I have solved my own problem. The answer was use an custom attribute:

    $entries | Select netbios_name0,Name_of_the_local_Group,Account_Contained_within_the_Group,Account_Type,Domain_for_Account,Type_of_Account, @{Label="Last_HW_Scan";Expression={($_.Last_HW_Scan.'#text')}}

    Just posting it so it may help someone else.

    • Marked as answer by Stonent Friday, October 16, 2015 3:39 AM
    Friday, October 16, 2015 3:39 AM

All replies

  • I have solved my own problem. The answer was use an custom attribute:

    $entries | Select netbios_name0,Name_of_the_local_Group,Account_Contained_within_the_Group,Account_Type,Domain_for_Account,Type_of_Account, @{Label="Last_HW_Scan";Expression={($_.Last_HW_Scan.'#text')}}

    Just posting it so it may help someone else.

    • Marked as answer by Stonent Friday, October 16, 2015 3:39 AM
    Friday, October 16, 2015 3:39 AM
  • Try this:

    [xml]$xml = @'
    <?xml version="1.0" encoding="utf-8" standalone="yes"?>
    	<feed xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices"
    xmlns:m=http://schemas.microsoft.com/ado/2007/08/dataservices/metadata xmlns="http://www.w3.org/2005/Atom"> <title type="text"></title> <id>uuid:dbcb4e96-c7ba-432f-9b71-b3b80adb2326;id=3141</id> <updated>2015-10-16T02:17:15Z</updated> <entry> <id>uuid:dbcb4e96-c7ba-432f-9b71-b3b80adb2326;id=3142</id> <title type="text"></title> <updated>2015-10-16T02:17:15Z</updated> <author /> <content type="application/xml"> <m:properties> <d:CollID>N000001</d:CollID> <d:Type>%</d:Type> <d:netbios_name0>LAPTOP000001</d:netbios_name0> <d:Name_of_the_local_Group>Administrators</d:Name_of_the_local_Group> <d:Account_Contained_within_the_Group>David Smith</d:Account_Contained_within_the_Group> <d:Account_Type>UserAccount</d:Account_Type> <d:Domain_for_Account>CONTOSO-US</d:Domain_for_Account> <d:Type_of_Account>Domain</d:Type_of_Account> <d:Last_HW_Scan m:type="Edm.DateTime">2015-10-15T18:46:39</d:Last_HW_Scan> </m:properties> </content> </entry> <entry> <id>uuid:dbcb4e96-c7ba-432f-9b71-b3b80adb2326;id=3143</id> <title type="text"></title> <updated>2015-10-16T02:17:15Z</updated> <author /> <content type="application/xml"> <m:properties> <d:CollID>N000001</d:CollID> <d:Type>%</d:Type> <d:netbios_name0>LAPTOP000002</d:netbios_name0> <d:Name_of_the_local_Group>Administrators</d:Name_of_the_local_Group> <d:Account_Contained_within_the_Group>Karen.Roberts</d:Account_Contained_within_the_Group> <d:Account_Type>UserAccount</d:Account_Type> <d:Domain_for_Account>CONTOSO-US</d:Domain_for_Account> <d:Type_of_Account>Domain</d:Type_of_Account> <d:Last_HW_Scan m:type="Edm.DateTime">2015-10-15T19:37:02</d:Last_HW_Scan> </m:properties> </content> </entry> </feed> '@
    $ns = @{
    d = "http://schemas.microsoft.com/ado/2007/08/dataservices"
    m = "http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"
    }
    $xml | Select-Xml -XPath '//m:properties' -Namespace $ns | select -expand node


    \_(ツ)_/



    • Edited by jrv Friday, October 16, 2015 3:49 AM
    Friday, October 16, 2015 3:44 AM
  • In XML we do not generally use GetElement as that is specific too the metadata and not the content.  "SelectNodes" or "SelectingleNode" with XPath gets XML in a controlled way like SQL gets tuples in a controlled way.

    \_(ツ)_/

    Friday, October 16, 2015 3:48 AM