Joining two objects from xml

PowerShell Help
1

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


<span class="c"># Need to join results together</span>

<span class="nb">Export-Csv</span> <span class="n">-InputObject</span> <span class="nv">$results</span> <span class="n">-append</span> <span class="n">-Path</span> <span class="nv">$resultfilepathtemp</span> <span class="n">-NoTypeInformation</span> 



}

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"

2

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.

3

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