Creating a custom XML from a hash table

Question

I have read some very interesting posts on the topic of my question in the past few weeks and have gotten close, but I need a very specific XML created from a hash table that i create by comparing a CSV to Active directory that outputs a csv with specific user data.
The specific fields are these: XrefCode, EmployeeNumber, FirstName, LastName, BusinessEmail, StartDate. The XML I need to have this data put in is extremely specific in format and I cannot find a way get this to work. Here is the XML how it needs to end up like. So it has to have a header of <EmployeeImport> at the top of the document and </EmployeeImport> at the bottom, as well as in the middle of the data <ContactInformation> and </ContactInformation>.

Here is a sample of the data i use, very basic. My code reaches to AD and populates the email field.

Number Display Name Status Location Ledger Code Business Email First Name Last Name 1234567 Doe, John Active 0GB50 John Doe 2345678 Smith, Jane Active 0GB50 Jane Smith

The hash table build looks like...

   $match = New-Object PSObject
            $match | Add-Member NoteProperty "XRefCode" $first.SamAccountName
            $match | Add-Member NoteProperty "EmployeeNumber" $first.SamAccountName
            $match | Add-Member NoteProperty "FirstName" $first.'First Name'
            $match | Add-Member NoteProperty "LastName" $first.'Last Name'
            $match | Add-Member NoteProperty "ContactInformationTypeXrefCode" 'BusinessEmail'
            $match | Add-Member NoteProperty "EffectiveStart" $TodaysDay
            $match | Add-Member NoteProperty "ElectronicAddress" $second.mail
            $combine += $match

The csv looks like the following: Sorry it doesn't line up well

XRefCode EmployeeNumber FirstName   LastName ContactInformationTypeXrefCode EffectiveStart  IsForSystemCommunication    ElectronicAddress
1234567    1234567       Joe          Doe      BusinessEmail         04/16/2021 0   [email protected]
2345678    2345678       Jane        Smith     BusinessEmail    04/16/2021  0    [email protected]

<EmployeeImport>
 <Employee>
  <XRefCode>1234567</XRefCode>
  <EmployeeNumber>1234567</EmployeeNumber>
  <FirstName>Joe</FirstName>
  <LastName>Doe</LastName>
    <ContactInformation>
     <ContactInformationTypeXrefCode>BusinessEmail</ContactInformationTypeXrefCode>
     <EffectiveStart>2020-04-16</EffectiveStart>
     <ElectronicAddress>[email protected]</ElectronicAddress>
    </ContactInformation>
 </Employee>
 <Employee>
  <XRefCode>2345678</XRefCode>
  <EmployeeNumber>2345678</EmployeeNumber>
  <FirstName>Jane</FirstName>
  <LastName>Smith</LastName>
    <ContactInformation>
     <ContactInformationTypeXrefCode>BusinessEmail</ContactInformationTypeXrefCode>
     <EffectiveStart>2020-04-16</EffectiveStart>
     <ElectronicAddress>[email protected]</ElectronicAddress>
    </ContactInformation>
 </Employee>
</EmployeeImport>

The code I found that was close was this...

$output |% {'<Objects>'} {$_.psobject.properties |% {'<Object>'} {"<$($_.name)>$($_.value)</$($_.name)>"} {'  </Object>'} } {'</Objects>'}

However, this doesn't add the header and footer needed as well as the middle Object between for the ContactInformation.

I might need to do this as custom for my case, but i'm not sure how to build this out. If that's the case, could someone point me in the right direction to customize this for this specific case?

Thanks

And $output is a collection of objects that have XrefCode, EmployeeNumber, FirstName, ... etc. as properties? — Mathias R. Jessen
– Mathias R. Jessen, Commented Apr 16, 2021 at 20:34
Correct, right now the output is in a hash that is a csv with the needed headers. I can give an example. — Jeff
– Jeff, Commented Apr 16, 2021 at 20:42
Can you please provide sample data and code used to convert it to hashtable? so that anyone can reproduce and test you case. — metablaster
– metablaster, Commented Apr 16, 2021 at 21:02

Mathias R. Jessen · Accepted Answer · 2021-04-16 22:13:37Z

1

Start by creating an [xml] document containing a EmployeeImport root-level element:

$xml = [xml]'<EmployeeImport/>'
$emplImport = $xml.DocumentElement

Now you'll want to add an <Employee> node to the root node for each input object:

$output = Import-Csv .\path\to\objects.csv

foreach($object in $output){
    # Create `<Employee>` node
    $employee = $emplImport.AppendChild($xml.CreateElement('Employee'))
    
    foreach($property in $object.psobject.Properties){
        # Create an xml child-element for each property on the input object
        $childNode = $employee.AppendChild($xml.CreateElement($property.Name))
        $childNode.InnerText = $property.Value
    }
}

And finally save the resulting xml document to disk:

# Important: only pass full/rooted paths to $xml.Save()
$outputPath = Resolve-Path .\path\to\output.csv

$xml.Save($outputPath)

Resulting file should like like this:

<EmployeeImport>
  <Employee>
    <XRefCode>1234567</XRefCode>
    <EmployeeNumber>1234567</EmployeeNumber>
    <FirstName>Joe</FirstName>
    <LastName>Doe</LastName>
    <ContactInformationTypeXrefCode>BusinessEmail</ContactInformationTypeXrefCode>
    <EffectiveStart>04/16/2021</EffectiveStart>
    <IsForSystemCommunication>0</IsForSystemCommunication>
    <ElectronicAddress>[email protected]</ElectronicAddress>
  </Employee>
  <Employee>
    <XRefCode>2345678</XRefCode>
    <EmployeeNumber>2345678</EmployeeNumber>
    <FirstName>Jane</FirstName>
    <LastName>Smith</LastName>
    <ContactInformationTypeXrefCode>BusinessEmail</ContactInformationTypeXrefCode>
    <EffectiveStart>04/16/2021</EffectiveStart>
    <IsForSystemCommunication>0</IsForSystemCommunication>
    <ElectronicAddress>[email protected]</ElectronicAddress>
  </Employee>
</EmployeeImport>

answered Apr 16, 2021 at 22:13

Mathias R. Jessen

178k13 gold badges175 silver badges234 bronze badges

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

2 Comments

Santiago Squarzon Over a year ago

This is great, you're missing <ContactInformation> though

Jeff Over a year ago

Love this, on the right path. Missing what Santiago suggested. But this is great!

Santiago Squarzon · Accepted Answer · 2021-04-16 23:14:05Z

0

This is my approach on your question, I prefer Mathias solution because it is clean code and this is pretty ugly but serves it's purpose.

$csv=@'
XRefCode,EmployeeNumber,FirstName,LastName,ContactInformationTypeXrefCode,EffectiveStart,IsForSystemCommunication,ElectronicAddress
1234567,1234567,Joe,Doe,BusinessEmail,04/16/2021,0,[email protected],
2345678,2345678,Jane,Smith,BusinessEmail,04/16/2021,0,[email protected]
'@|ConvertFrom-Csv

$properties=$csv[0].PSObject.Properties.Name

[xml]$xml=@"
<EmployeeImport>
$(foreach($line in $csv){
@'
 <Employee>
  <XRefCode>{0}</XRefCode>
  <EmployeeNumber>{1}</EmployeeNumber>
  <FirstName>{2}</FirstName>
  <LastName>{3}</LastName>
    <ContactInformation>
     <ContactInformationTypeXrefCode>{4}</ContactInformationTypeXrefCode>
     <EffectiveStart>{5}</EffectiveStart>
     <IsForSystemCommunication>{6}</IsForSystemCommunication>
     <ElectronicAddress>{7}</ElectronicAddress>
    </ContactInformation>
 </Employee>
'@ -f ($properties|%{$line.$_})
})
</EmployeeImport>
"@

answered Apr 16, 2021 at 23:14

Santiago Squarzon

65.6k5 gold badges26 silver badges60 bronze badges

7 Comments

Jeff Over a year ago

I have been playing with this a bit and I keep getting the following error: Error formatting a string: Index (zero based) must be greater than or equal to zero and less than the size of the argument list.. At line:4 char:1 + @' + ~~ + CategoryInfo : InvalidOperation: ( <Employee> ...> </Employee>:String) [], RuntimeException + FullyQualifiedErrorId : FormatError

Santiago Squarzon Over a year ago

The error is related to the data source (your csv) not the code. I'm unable to reproduce this error using the example code I posted. You need to inspect your csv. @Jeff

Jeff Over a year ago

Oh, i think i may be on to something. Forget the last response. There are 7 properties in the csv, and if using 0, this references to there being 8. One field is statically "0". Now I just need to figure out the exporting and I can test. This is very close!! Thank you

Santiago Squarzon Over a year ago

@Jeff actually there were 8 properties in the csv in your original post, now I see that IsForSystemCommunication seem to be removed from your PSObject. If you can explain where exactly is the data coming from or give me an actual representation of the input data to be converted to XML I can help you.

Jeff Over a year ago

the data is merged data from AD and a cav from HR. In then end the "IsForSystemCommunication" actually is always 0, so i took that out, which brought the properties down to 7. This worked. Then I created and output variable for the location then saved the XML using $xml.Save($Output). This worked like a charm! I'm about to submit this for testing. Thank you so much for this help. (I dont have enough reputation to add points visually, but I have clicked up arrow!)

|

Collectives™ on Stack Overflow

Creating a custom XML from a hash table

2 Answers 2

2 Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related