Joining two objects from xml

Welcome Forums General PowerShell Q&A Joining two objects from xml

Viewing 2 reply threads
  • Author
    Posts
    • #218280
      Participant
      Topics: 1
      Replies: 0
      Points: -3
      Rank: Member

      Hi All,

      I’m pretty new to powershell scripting and have been learning a bit about it. However, I am trying to understand objects and how to join them.

      I have created the following:

      Powershell
      $XPath = "//INVOICEHEADER"
      $XPath2 = "//FILE"
      # path where we need to look for the xml. * means all files that end in .xml
      $Path = "$sourcelocation\*.xml"
      
      # Select-Xml -Path $Path -XPath $Xpath | 
      # Select-Object -EA SilentlyContinue -ExpandProperty Node |
      # # export results append to the file NNUH.csv
      # Export-Csv -append -Path $resultfilepathtemp -NoTypeInformation 
      Get-ChildItem -Path $Path | 
      
      ForEach-Object {
          $xml = Select-Xml -Path $_.FullName -XPath $Xpath
          $results = Select-Object -InputObject $Xml -EA SilentlyContinue -ExpandProperty Node
          # Write-Output $results
          $xml = Select-Xml -Path $_.FullName -XPath $Xpath2 
          $results2 = Select-Object -InputObject $Xml -EA SilentlyContinue -ExpandProperty Node
      
          # Need to join results together
      
          Export-Csv -InputObject $results -append -Path $resultfilepathtemp -NoTypeInformation 
      }
      

      I’m trying to join two objects that I have picked up from an .xml but there will be multiple ones.  Struggling on how to get this to output to a file.  The .xml looks like this:

      XML
      <?xml version="1.0" encoding="UTF-8"?>
      <INVOICEIMPORT TYPE="INVOICE">
        <INVOICES>
          <INVOICE>
            <INVOICEHEADER>
              <CATEGORY>1</CATEGORY>
              <PURCHASEORDERNO>AA00000</PURCHASEORDERNO>
              <OURREF>AA00000</OURREF>
              <ATTRIB2>Client name</ATTRIB2>
              <ATTRIB3>Address</ATTRIB3>
              <SUPPLIERREF>1000000000</SUPPLIERREF>
              <INVOICEDATE>20200302</INVOICEDATE>
              <ARRIVAL>20200401</ARRIVAL>
              <AMOUNT>100,401.20</AMOUNT>
              <VATAMOUNT>13,900.20</VATAMOUNT>
              <CURRENCY.CURRENCYID>GBP</CURRENCY.CURRENCYID>
              <CLIENT.CODE>9999</CLIENT.CODE>
              <ITVNCODE>No supplier found</ITVNCODE>
            </INVOICEHEADER>
            <FILES>
              <FILE>
                <TRANSACTIONTYPE>X104</TRANSACTIONTYPE>
                <FULLFILENAME>PP_XXXX_00001.pdf</FULLFILENAME>
                <PAGENUMBER>1</PAGENUMBER>
              </FILE>
            </FILES>
          </INVOICE>
        </INVOICES>
      </INVOICEIMPORT>
      

      and the output should be in a similar format as:

      "CATEGORY","PURCHASEORDERNO","OURREF","ATTRIB2","ATTRIB3","SUPPLIERREF","INVOICEDATE","ARRIVAL","AMOUNT","VATAMOUNT","CURRENCY.CURRENCYID","CLIENT.CODE","ITVNCODE","TRANSACTIONTYPE","FULLTIMENAME","PAGENUMBER"
      "1","AA00000","AA00000","Client name","Address","1000000000","20200302","20200401","100,401.20","13,900.20","GBP","9999","No supplier found","X104","PP_XXXX_00001.pdf","1"
      
      
    • #218352
      Participant
      Topics: 3
      Replies: 324
      Points: 1,056
      Helping Hand
      Rank: Community Hero

      Here is one way to do it. I used two files for the test.

      $Path = "$sourcelocation\*.xml"
      $XPaths = @("//INVOICEHEADER","//FILE")
      
      Get-ChildItem -Path $path |
          ForEach-Object {
              $xml = New-Object -TypeName xml
              $xml.load($_)
              $properties = [ordered]@{}
      
              foreach($xpath in $xpaths){
                  Foreach ($item in (Select-XML -Xml $xml -XPath $xpath)){
                      $invoiceproperties = $item.Node.get_childnodes().name
                      foreach($property in $invoiceproperties){
                          $properties[$property] = $item.Node.$property
                      }
              }
          }
      
          New-Object -typename psobject -Property $properties
      
      } | export-csv c:\temp\test.csv -NoTypeInformation
      
      

      I am sure an XML expert could come along and show you a better way but this seems to do what you want. It outputs a PSobject that I pipe to export-csv. Don’t forget to append if needed. Here is the output, output piped to FT, and the CSV file contents.

      CATEGORY : 1
      PURCHASEORDERNO : AA00000
      OURREF : AA00000
      ATTRIB2 : Client name
      ATTRIB3 : Address
      SUPPLIERREF : 1000000000
      INVOICEDATE : 20200302
      ARRIVAL : 20200401
      AMOUNT : 100,401.20
      VATAMOUNT : 13,900.20
      CURRENCY.CURRENCYID : GBP
      CLIENT.CODE : 9999
      ITVNCODE : No supplier found
      TRANSACTIONTYPE : X104
      FULLFILENAME : PP_XXXX_00001.pdf
      PAGENUMBER : 1
      
      CATEGORY : 2
      PURCHASEORDERNO : BA00000
      OURREF : BA00000
      ATTRIB2 : name Client
      ATTRIB3 : Address
      SUPPLIERREF : 2000000000
      INVOICEDATE : 30200302
      ARRIVAL : 30200401
      AMOUNT : 200,401.20
      VATAMOUNT : 23,900.20
      CURRENCY.CURRENCYID : GBP
      CLIENT.CODE : 0999
      ITVNCODE : No supplier found
      TRANSACTIONTYPE : Y104
      FULLFILENAME : QP_XXXX_00001.pdf
      PAGENUMBER : 2
      
      
      CATEGORY PURCHASEORDERNO OURREF ATTRIB2 ATTRIB3 SUPPLIERREF INVOICEDATE ARRIVAL AMOUNT VATAMOUNT
      -------- --------------- ------ ------- ------- ----------- ----------- ------- ------ ---------
      1 AA00000 AA00000 Client name Address 1000000000 20200302 20200401 100,401.20 13,900.20
      2 BA00000 BA00000 name Client Address 2000000000 30200302 30200401 200,401.20 23,900.20
      
      
      "CATEGORY","PURCHASEORDERNO","OURREF","ATTRIB2","ATTRIB3","SUPPLIERREF","INVOICEDATE","ARRIVAL","AMOUNT","VATAMOUNT","CURRENCY.CURRENCYID","CLIENT.CODE","ITVNCODE","TRANSACTIONTYPE","FULLFILENAME","PAGENUMBER"
      "1","AA00000","AA00000","Client name","Address","1000000000","20200302","20200401","100,401.20","13,900.20","GBP","9999","No supplier found","X104","PP_XXXX_00001.pdf","1"
      "2","BA00000","BA00000","name Client","Address","2000000000","30200302","30200401","200,401.20","23,900.20","GBP","0999","No supplier found","Y104","QP_XXXX_00001.pdf","2"
      
      

      I hope this helps.

      • This reply was modified 1 month, 1 week ago by James Petty.
      • This reply was modified 1 month, 1 week ago by Doug Maurer. Reason: Formatting
    • #218541
      Participant
      Topics: 12
      Replies: 1610
      Points: 2,508
      Helping Hand
      Rank: Community Hero

      Added a second node to test, but it can as simple as:

      #Get the top level node that has the InvoiceHeader and File nodes..
      $results = foreach ($invc in (Select-Xml -Path C:\Scripts\temp.xml -XPath '//INVOICE')) {
          #Put the file data into a variable
          $file = $invc.Node.FILES.FILE
          
          #Take invoice header, since it has more properties, and use Select-Object
          #to create a PSObject with calculated properties to add the file data
          $invc.Node.INVOICEHEADER |
          Select-Object -Property CATEGORY,
                                     PURCHASEORDERNO,
                                     OURREF,
                                     ATTRIB2,
                                     ATTRIB3,
                                     SUPPLIERREF,
                                     INVOICEDATE,
                                     ARRIVAL,
                                     AMOUNT,
                                     VATAMOUNT,
                                     CURRENCY.CURRENCYID,
                                     CLIENT.CODE, 
                                     ITVNCODE, 
                                     @{Name='TRANSACTIONTYPE';Expression={$file.TRANSACTIONTYPE}},
                                     @{Name='FULLFILENAME';Expression={$file.FULLFILENAME}},
                                     @{Name='PAGENUMBER';Expression={$file.PAGENUMBER}}
      
      }
      
      $results # | Export-CSV ...
      

      Output:

      CATEGORY            : 1
      PURCHASEORDERNO     : AA00000
      OURREF              : AA00000
      ATTRIB2             : Client name
      ATTRIB3             : Address
      SUPPLIERREF         : 1000000000
      INVOICEDATE         : 20200302
      ARRIVAL             : 20200401
      AMOUNT              : 100,401.20
      VATAMOUNT           : 13,900.20
      CURRENCY.CURRENCYID : GBP
      CLIENT.CODE         : 9999
      ITVNCODE            : No supplier found
      TRANSACTIONTYPE     : X104
      FULLFILENAME        : PP_XXXX_00001.pdf
      PAGENUMBER          : 1
      
      CATEGORY            : 2
      PURCHASEORDERNO     : AA000002
      OURREF              : AA000002
      ATTRIB2             : Client name2
      ATTRIB3             : Address2
      SUPPLIERREF         : 10000000002
      INVOICEDATE         : 202003022
      ARRIVAL             : 202004012
      AMOUNT              : 200,401.20
      VATAMOUNT           : 23,900.20
      CURRENCY.CURRENCYID : USD
      CLIENT.CODE         : 1234
      ITVNCODE            : No supplier found
      TRANSACTIONTYPE     : X105
      FULLFILENAME        : PP_XXXX_00002.pdf
      PAGENUMBER          : 1
      
Viewing 2 reply threads
  • You must be logged in to reply to this topic.