locked
XML Retrieve First Record of Attribute Where... RRS feed

  • Question

  • I have a script that gets the first attribute of an Audits Element in my xml file.
    The audit element looks like this

    <Audits><Audit timestamp="" user="TestUSER" action="Listed"/>Jason Pope</Audits>
    My scripts below gets the first audit data regardless of what the Action is, I only want the first Audits Record where the
    Action is sold. There could be multiple Audits, but I don't want to retrieve any other Audit data just the first one  where the action = Sold.  In my current script it gets me the agents name (Jason Pope) but I only want the first agents name where action is sold.  There are multiple actions Sold,Listed,Pending,Removed. I just want the name of the Agent who sold.


    Here is my current

    PowerShell

     $varDirectory = "D:\Downloads\XmlTest\"
    $files = Get-ChildItem $varDirectory -file -Filter *.xml;
      ForEach ($file in $files)
      {
      [xml] $xd = Get-Content $file
      $varSold = $xd.selectnodes("/RealEstate/House/Audits/Audit")  | Select-Object -first 1
     Write $varSold
      }


    Sample XML Data

    <RealEstate specVersion="5.09">
    <Header><SenderID>AB12345</SenderID>
    <ReceiverID>AB56789</ReceiverID>
    <DocTypeID>House</DocTypeID>
    <DocCount>1</DocCount>
    <Date type="generation">04/12/2019 16:05</Date>
    <ListingRequest><Date>04/12/2019 16:05</Date>
    <Reason>Rule Set House Extract</Reason>
    <User UserId="TestUSer" FirstName="Test" LastName="User"><ContactMethods><ContactMethod sequenceNum="1" type="Phone"></ContactMethod>
    <ContactMethod sequenceNum="1" type="Email">testuser@here.com</ContactMethod>
    </ContactMethods>
    </User>
    </ListingRequest>
    </Header>
    <House internalId="24412065930" type="Residence"><ListingNumbers><ListingNumber type="House Number" isPrimary="true">8456874</ListingNumber>
    <ListingNumber type="LST" isPrimary="false">789036954</ListingNumber>
    </ListingNumbers>
    <Comments><Comment type="SpecialInstructions">Awesome House BUY IT NOW</Comment>
    </Comments>
    <Status>NEW</Status>
    <AssignedTo></AssignedTo>
    <Owner>Joe Jones</Owner>
    <Audits><Audit timestamp="" user="TestUSER" action="Sold"/>Jason Pope</Audits>
    </House>
    </RealEstate>

    Thanks

    Tuesday, April 16, 2019 3:50 PM

Answers

  • I got it to work but had to use two variables to get the results I wanted.
    $varsold give me the time the user and the text. I only want text.
    If I put.text at the end I get an error

    If I add a second variable and append .text I get the results I want. I am fine with this.

     $varSold=$xd.selectSingleNode("//Audit[@action='Sold']")
     $varSold2 = $($varSold.'#text')
    
      Write $varSold2


    • Edited by gtjr92 Wednesday, April 17, 2019 11:37 AM
    • Marked as answer by gtjr92 Wednesday, April 17, 2019 2:27 PM
    Wednesday, April 17, 2019 1:54 AM
  • You need to spend some time learning PowerShell.  All of the guessing will only keep you confused.

    $xd.selectSingleNode("//Audit[@action='Extracted']/../text()").Value

    You can learn XPath here: https://www.w3schools.com/xml/xpath_intro.asp


    \_(ツ)_/

    • Marked as answer by gtjr92 Wednesday, April 17, 2019 2:27 PM
    Wednesday, April 17, 2019 3:14 AM

All replies

  • How about this?

    $varSold = $xd.selectnodes("/RealEstate/House/Audits/Audit")  | Where {$_.Action -eq 'Sold'} |Select-Object -first 1


    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)

    Tuesday, April 16, 2019 6:00 PM
  • To get an element by its attribute value just query for the attribute:

    $varSold = $xd.selectnodes("/RealEstate/House/Audits/Audit[@action='Sold'")


    \_(ツ)_/

    Tuesday, April 16, 2019 6:20 PM
  • Hmm, this works:

    select-xml /RealEstate/House/Audits/Audit[@action][1] action.xml | select -expand node


    but not this:


    select-xml /RealEstate/House/Audits/Audit[@action='Sold'][1] action.xml | select -expand node

    • Edited by JS2010 Tuesday, April 16, 2019 6:55 PM
    Tuesday, April 16, 2019 6:54 PM
  • This is easier:

    $xd.selectnodes("/RealEstate/House/Audits/Audit[@action='Sold'")[0]

    or this for the first node:

    $xd.SelectSingleNode("/RealEstate/House/Audits/Audit[@action='Sold'")

    Tis is the correct way.

    (select-xml -Path  action.xml -XPath '/RealEstate/House/Audits/Audit[@action="Sold"]').Node

    An XPath is not an array.  The argument can be an array but it cannot be sub-arrayed when it is a string.

    By not using the named parameters you will be creating an ambiguity which may not work as you expect.


    \_(ツ)_/

    Tuesday, April 16, 2019 7:01 PM
  • Oh ok, thanks.  You can pick the first result within the xpath expression as well.  The difference comes from adding the quotes.  Hmm, I'm not getting "Jason Pope".

    select-xml '/RealEstate/House/Audits/Audit[@action="Sold"][1]' action.xml |
      select -expand node

    timestamp user     action
    --------- ----     ------
              TestUSER Sold

    • Edited by JS2010 Tuesday, April 16, 2019 7:19 PM
    Tuesday, April 16, 2019 7:15 PM
  • I am always forgetting about XPATH! :-/

    FYI, your example is missing a "]". S/B "...[@action='Sold']"

    But, that XML file the OP gave as an example, and the description of the data he wants to extract just don't seem to agree. The name "Jason Pope" is a "#text" attribute of the "<Audits>" element, not an attribute of the only "<Audit>" element in the file. Neither your XPATH or my example gets that #text data.

    Maybe he meant he wanted to get the "user" attribute from the <Audit> element?


    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)

    Tuesday, April 16, 2019 7:16 PM
  • Thanks for all the replies, I'm not real familiar with all the proper terms for XML, but yes I want to get the data in the area where the name Jason Pope is in the audits is, whatever that section/element is called where the action=Sold. Hope that clarifies.
    Tuesday, April 16, 2019 7:54 PM
  • Something like that?  I'm not sure what multiple examples would look like.  Maybe this isn't the best xml design?

    $xml.selectnodes("/RealEstate/House/Audits[descendant::Audit[@action='Sold'][1]]")

    Audit #text
    ----- -----
    Audit Jason Pope

    • Edited by JS2010 Tuesday, April 16, 2019 8:08 PM
    Tuesday, April 16, 2019 7:59 PM
  • This

    $xml.selectnodes("/RealEstate/House/Audits[descendant::Audit[@action='Sold'][1]]")

    didn't return any data. I did not get errors but no data. I know there are ones that are sold in there. I tried [0] and [1]

    thanks


    • Edited by gtjr92 Tuesday, April 16, 2019 8:15 PM
    Tuesday, April 16, 2019 8:14 PM
  • Can you give an example with multiple Audits?  That code works with your given example.

    • Edited by JS2010 Tuesday, April 16, 2019 8:53 PM
    Tuesday, April 16, 2019 8:27 PM
  • Can you give an example with multiple Audits?

    You can use the xpath OR operators.

    Examples: https://stackoverflow.com/questions/5350666/xpath-or-operator-for-different-nodes


    \_(ツ)_/

    Tuesday, April 16, 2019 8:37 PM
  • To get the text of a node just reference its value.


    \_(ツ)_/

    Tuesday, April 16, 2019 8:39 PM
  • Example:

    [xml]$xd = @'
    <RealEstate specVersion="5.09">
        <Header>
            <SenderID>AB12345</SenderID>
                <ReceiverID>AB56789</ReceiverID>
                <DocTypeID>House</DocTypeID>
                <DocCount>1</DocCount>
                <Date type="generation">04/12/2019 16:05</Date>
                <ListingRequest>
                    <Date>04/12/2019 16:05</Date>
                    <Reason>Rule Set House Extract</Reason>
                    <User UserId="TestUSer" FirstName="Test" LastName="User">
                        <ContactMethods>
                            <ContactMethod sequenceNum="1" type="Phone"></ContactMethod>
                            <ContactMethod sequenceNum="1" type="Email">testuser@here.com</ContactMethod>
                        </ContactMethods>
                    </User>
                </ListingRequest>
        </Header>
        <House internalId="24412065930" type="Residence">
            <ListingNumbers>
                <ListingNumber type="House Number" isPrimary="true">8456874</ListingNumber>
                <ListingNumber type="LST" isPrimary="false">789036954</ListingNumber>
            </ListingNumbers>
            <Comments>
                <Comment type="SpecialInstructions">Awesome House BUY IT NOW</Comment>
            </Comments>
            <Status>NEW</Status>
            <AssignedTo></AssignedTo>
            <Owner>Joe Jones</Owner>
            <Audits><Audit timestamp="" user="TestUSER" action="Sold"/>Jason Pope</Audits>
        </House>
    </RealEstate>
    '@
    $xd.selectSingleNode("//Audit[@action='Sold']").User


    \_(ツ)_/




    • Edited by jrv Tuesday, April 16, 2019 9:09 PM
    Tuesday, April 16, 2019 8:47 PM
  • The above gets the User attribute.  To get the text the easiest way is like this:

    $xd.selectsinglenode("//Audit[@action='Sold']/..").'#text'


    \_(ツ)_/


    • Edited by jrv Tuesday, April 16, 2019 9:09 PM
    Tuesday, April 16, 2019 8:53 PM
  • Another method is to retrieve the text node only and display its value:

     $xd.selectsinglenode("//Audit[@action='Sold']/../text()").Value


    \_(ツ)_/


    • Edited by jrv Tuesday, April 16, 2019 9:09 PM
    Tuesday, April 16, 2019 8:55 PM
  • To use Select-Xml things get more complicated:

     (select-xml -xpath "//Audit[@action='Sold']/../text()" -Path test.xml).Node.Value


    \_(ツ)_/

    Tuesday, April 16, 2019 9:01 PM
  • You can also use "parent" and "child" tokens to retrieve the parent of the queried node.  The shortcuts for current and parent are "." and ".." and, of course, "/" is the child reference.


    \_(ツ)_/



    • Edited by jrv Tuesday, April 16, 2019 9:04 PM
    Tuesday, April 16, 2019 9:02 PM
  • I fixed the incorrect $xml with $xd.  Problem of copying pieces quickly.


    \_(ツ)_/

    Tuesday, April 16, 2019 9:10 PM
  • I got it to work but had to use two variables to get the results I wanted.
    $varsold give me the time the user and the text. I only want text.
    If I put.text at the end I get an error

    If I add a second variable and append .text I get the results I want. I am fine with this.

     $varSold=$xd.selectSingleNode("//Audit[@action='Sold']")
     $varSold2 = $($varSold.'#text')
    
      Write $varSold2


    • Edited by gtjr92 Wednesday, April 17, 2019 11:37 AM
    • Marked as answer by gtjr92 Wednesday, April 17, 2019 2:27 PM
    Wednesday, April 17, 2019 1:54 AM
  • You need to spend some time learning PowerShell.  All of the guessing will only keep you confused.

    $xd.selectSingleNode("//Audit[@action='Extracted']/../text()").Value

    You can learn XPath here: https://www.w3schools.com/xml/xpath_intro.asp


    \_(ツ)_/

    • Marked as answer by gtjr92 Wednesday, April 17, 2019 2:27 PM
    Wednesday, April 17, 2019 3:14 AM
  • I already tried that it returned no data, tried it again just to be sure.
    I would love to learn more powershell, but there is only so much time in a day.
    I only need powershell on occasion.
    When I need it for particular tasks I try to find the best way to do it.If I cannot then I will ask.
    I appreciate all the help from you and everyone else.
    Wednesday, April 17, 2019 11:42 AM
  • No, you'll take our solutions and you'll like it, lol.

    Wednesday, April 17, 2019 2:00 PM