Import XML data using powershell

Question

I have got an excel file which happens to be an XML table, I have saved it as an xml file instead and whilst trying to use powershell to import it to a SQL database. It is coming up with blank values.

<?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>

Powershell code

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

Get-Content returns an array of strings, you need to convert it to an xml document somehow before you can treat it as such. $xmldata = ... -> [xml]$xmldata = ... should do the trick — Mathias R. Jessen
– Mathias R. Jessen, Commented Feb 23, 2021 at 16:22
@user3535468, don't forget to mark an answer for other user that have the same prob as you :) — user15242289
– user15242289, Commented Feb 25, 2021 at 8:13

score 3 · Accepted Answer · 2021-02-23 16:32:05Z

3

Simply,

$xml_file = get-content "C:\TEMP\test.xml"
$xml_file = [xml]$xml_file

Then, example in your case :

$xml_file.RecordsExport.ResultSet

To print Result Set.

edited Feb 23, 2021 at 16:32

answered Feb 23, 2021 at 16:26

user15242289

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

Comments

Daniel Manta · Accepted Answer · 2023-07-13 02:12:23Z

0

[xml]$xml_file = get-content "C:\TEMP\test.xml"

edited Jul 13, 2023 at 2:12

Daniel Manta

6,76317 gold badges43 silver badges49 bronze badges

answered Jul 7, 2023 at 19:32

Rick

1

Collectives™ on Stack Overflow

Import XML data using powershell

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related