none
Import and alter XML file with Powershell RRS feed

  • Question

  • I have spent most of the day and can’t break this.

    Reading data and using it in CSV files is PERFECT in Powershell.  So easy to work off of.

    XML is NOT.

    I am moving GPO from one domain to a test domain.  I need to manipulate the Manifest.XML file.

    If I open Manifest.XML in XML as a XML table it produces these headers.

    ns1:version

    ns1:SIPresent

    ns1:GPOGuid

    ns1:GPODomain

    ns1:GPODomainGuid

    ns1:GPODomainController

    ns1:BackupTime

    ns1:ID

    ns1:Comment

    ns1:GPODisplayName

    If I open Manifest.XML in XML as a read only workbook it produces these headers.

    /Backups
    /@mfst:version /@mfst:version/#agg /BackupInst/@mfst:SIPresent /BackupInst/BackupTime /BackupInst/Comment /BackupInst/GPODisplayName /BackupInst/GPODomain /BackupInst/GPODomainController /BackupInst/GPODomainGuid /BackupInst/GPOGuid /BackupInst/ID

    If I open it as a CSV in notepad I see this

    <Backups xmlns="http://www.microsoft.com/GroupPolicy/GPOOperations/Manifest" xmlns:mfst="http://www.microsoft.com/GroupPolicy/GPOOperations/Manifest" mfst:version="1.0"><BackupInst><GPOGuid><![CDATA[{FFC779C1-E3E9-4EC4-90CE-31D3200FE3E9}]]></GPOGuid><GPODomain><![CDATA

    Is there a way to import this as a CSV and alter it?

    I believe the answer is in this but I am tired and hoping someone can get me started.  I understand working with CSV files I am hoping this is similar.

    $GPOCSV =  [xml](get-content  C:\2LAB\data\GPOBackup\manifest.xml )
    $GPOCSV | get-member

    Thanks


    Lishron

    Saturday, March 7, 2015 3:53 AM

Answers

  • Here is how to extract the data:

    $p=@(
        @{N='BackupTime';E={$_.backuptime.'#cdata-section'}},
        @{N='GPOGUID';E={$_.GpoGUID.'#cdata-section'}},
        @{N='GPODisplayName';E={$_.GPODisplayName.'#cdata-section'}},
        @{N='Comment';E={$_.Comment.'#cdata-section'}}
    )
    $manifest=[xml](Get-Content C:\temp\gpobackups\manifest.xml)
    $manifest.Backups.BackupInst | select $p
    
    You can add the rest of the items the same way.  Once you have this you can export it to Excel.(CSV)


    ¯\_(ツ)_/¯

    • Marked as answer by Lishron Sunday, March 8, 2015 1:24 AM
    Saturday, March 7, 2015 9:58 AM

All replies

  • XML and CSV are very different (obviously), whilst a straight translation amy be possible in some cases it's not worth the time or trouble doing it.

    XML is really easy to use in PowerShell, at least for consuming it.

    $GPOxml =  [xml](get-content  C:\2LAB\data\GPOBackup\manifest.xml )
    
    #This will get you the backup node
    $GPOxml.Backups
    
    #This will get you all the <BackupInst> nodes
    $GPOxml.Backups.BackupInst
    
    #You can then loop through them either using a pipeline or a foreach operation
    foreach ($instruction in $GPOxml.Backups.BackupInst)
    {
       $GPOGuid = $intruction.GPOGuid
    }

    There's some good blog topics on this, try this as a starter: http://blogs.technet.com/b/heyscriptingguy/archive/2014/06/11/use-powershell-to-explore-structure-of-xml-document.aspx

    Saturday, March 7, 2015 8:44 AM
  • Here is how to extract the data:

    $p=@(
        @{N='BackupTime';E={$_.backuptime.'#cdata-section'}},
        @{N='GPOGUID';E={$_.GpoGUID.'#cdata-section'}},
        @{N='GPODisplayName';E={$_.GPODisplayName.'#cdata-section'}},
        @{N='Comment';E={$_.Comment.'#cdata-section'}}
    )
    $manifest=[xml](Get-Content C:\temp\gpobackups\manifest.xml)
    $manifest.Backups.BackupInst | select $p
    
    You can add the rest of the items the same way.  Once you have this you can export it to Excel.(CSV)


    ¯\_(ツ)_/¯

    • Marked as answer by Lishron Sunday, March 8, 2015 1:24 AM
    Saturday, March 7, 2015 9:58 AM
  • Thank you both so much!

    jrv yours worked perfectly.  I could not get Alex's to work.  It just produced a loop of of blank lines no mater how i tried to alter it.   Never the less it is so kind of each of you to help.

    Thanks again.


    Lishron

    Sunday, March 8, 2015 1:26 AM
  • XML is really a bitch until you get it.  Once gotten it is trivial.

    Ciao - back to 007 reruns.


    ¯\_(ツ)_/¯

    Sunday, March 8, 2015 2:59 AM