Import XML records using powershell

Question

I am trying to import XML data into powershell and then onto a SQL server table, encountering errors with it.

<?xml version="1.0"?>  
<RecordsExport xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">  
<RecordsExport>  
  <ResultSet>  
    <SRN>1</SRN>  
    <Name>Tom</Name>  
    <Gender>Male</Gender>  
    <ChangeDate xsi:nil="true" />  
  </ResultSet>  
  <ResultSet>  
    <SRN>2</SRN>  
    <Name>Jack</Name>  
    <Gender>Male</Gender>  
    <ChangeDate xsi:nil="true" />  
  </ResultSet>  
</RecordsExport>  

Existing Ps code

$xmldata = Get-Content -Path 'C:\temp\test.xml'  
$import_data = foreach ($data in $xmldata.RecordsExport.ResultSet){  
  
[PSCustomObject]@{  
    SRN = $data.SRN  
    ServiceID = $data.Name  
    Platform = $data.Gender  
    Domain = $data.ChangeDate  
}  
  
}  


  

Answer 1

Your XML file isn't correct. You have two "RecordsExport" tags and the conversion to XML fails with this diagnostic:

Error: "Unexpected end of file has occurred. The following elements are not closed: RecordsExport. Line 16, position 18."

This version of the XML file corrects that:

<?xml version="1.0"?>
 <RecordsExport xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RecordsExport>
   <ResultSet>
     <SRN>1</SRN>
     <Name>Tom</Name>
     <Gender>Male</Gender>
     <ChangeDate xsi:nil="true" />
   </ResultSet>
   <ResultSet>
     <SRN>2</SRN>
     <Name>Jack</Name>
     <Gender>Male</Gender>
     <ChangeDate xsi:nil="true" />
   </ResultSet>
 </RecordsExport>
</RecordsExport>

This version of your script correctly casts the contents of the XML file as an "[xml]" object and adjusts the "ForEach" to account for the additional "RecordsExport" tag:

$xmldata = [xml](Get-Content -Path 'C:\junk\test.xml' -raw)
$import_data = foreach ($data in $xmldata.RecordsExport.RecordsExport.ResultSet) {
    [PSCustomObject]@{
        SRN       = $data.SRN
        ServiceID = $data.Name
        Platform  = $data.Gender
        Domain    = $data.ChangeDate
    }
}

The results of running the script against the corrected file:

SRN ServiceID Platform Domain
--- --------- -------- ------
1   Tom       Male     ChangeDate
2   Jack      Male     ChangeDate

Answer 2

Hi,

You have to cast $xmldata as an XML object.

[xml]$xmldata = Get-Content -Path 'C:\temp\test.xml'  

In your XML sample, there are two start tags <RecordsExport> on line 2 and 3 but only one end tag on line 16.

Best Regards，
Ian Xue

============================================

If the Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.