XSL Complex Transform from PowerShell Output

Question

I'm still in the process of learning about XSL and XML schemas but have unforunately come across a rather complicated output from a PowerShell export that I need to transform to a readable format for Access.

Unfortunately has thrown up something rather complicated, and my efforts to attempt to sort it have gone in vain, following the guidance I've been able to scrape :

<Obj RefId="0">
<TN RefId="0">
  <T>System.IO.DirectoryInfo</T>
  <T>System.IO.FileSystemInfo</T>
  <T>System.MarshalByRefObject</T>
  <T>System.Object</T>
</TN>
<ToString>Archive Files - Requested</ToString>
<Props>
  <S N="Name">Archive Files - Requested</S>
  <Obj N="Parent" RefId="1">
    <TNRef RefId="0" />
    <ToString>Archive Files</ToString>
    <Props>
      <S N="Name">Archive Files</S>
      <S N="Parent"></S>
      <B N="Exists">true</B>
      <S N="Root">N:\</S>
      <S N="FullName">N:\Archive Files</S>
      <S N="Extension"></S>
      <DT N="CreationTime">2016-02-24T08:23:01.3111895+00:00</DT>
      <DT N="CreationTimeUtc">2016-02-24T08:23:01.3111895Z</DT>
      <DT N="LastAccessTime">2019-09-19T03:00:01.2502407+01:00</DT>
      <DT N="LastAccessTimeUtc">2019-09-19T02:00:01.2502407Z</DT>
      <DT N="LastWriteTime">2016-02-24T08:25:10.3301992+00:00</DT>
      <DT N="LastWriteTimeUtc">2016-02-24T08:25:10.3301992Z</DT>
      <S N="Attributes">Directory</S>
    </Props>
    <MS>
     <S N="BaseName">Archive Files</S>
      <S N="Mode">d----</S>
    </MS>
  </Obj>

Ideally I'd like things to make things flat and have it look something like:

<Root>
    <Record>      
      <RefID>#</RefID>
      <Name>Archive Files</Name>
      <Parent></Parent>
      <FullName>N:\Archive Files</FullName>
      <Extension></Extension>
      <CreationTime>2016-02-24T08:23:01.3111895+00:00</Creationtime>
      <LastAccessTime>2019-09-19T03:00:01.2502407+01:00</LastAccessTime>
      <LastWriteTime">2016-02-24T08:25:10.3301992+00:00</LastWriteTime>
      <Attributes>Directory</Attributes>
      <BaseName>Archive Files</BaseName>
      <Mode>d----</Mode> 
    </Record>
</Root>

Is this XML complete? The first <Obj> and <Props> elements are note closed as it is presented. — twfurst
– twfurst, Commented Sep 24, 2020 at 12:42
This is only a section of it, with generally the information I'm after. I can post the entire thing if that'd help. — NotQuiteAKing
– NotQuiteAKing, Commented Sep 24, 2020 at 13:00
@ChristopherPrince yes, having the full structure would be helpful. — twfurst
– twfurst, Commented Sep 24, 2020 at 13:19

Mathias R. Jessen · Accepted Answer · 2020-09-24 13:24:32Z

1

The sample XML in your post looks an awfully lot like CliXml - PowerShell's internal serialization format.

You can use Import-CliXml to deserialize the objects:

# Import from CliXml
$directoryData = Import-CliXml -Path .\path\to\input.xml

# Export to a format Access can consume
$directoryData |Export-Csv .\path\to\output.csv -NoTypeInformation

answered Sep 24, 2020 at 13:24

Mathias R. Jessen

178k13 gold badges175 silver badges234 bronze badges

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

1 Comment

NotQuiteAKing Over a year ago

Hi, yes it was an CliXml export originally and this has solved my issue, thanks! I wound be curious how this could be resolved through an XSL schema, which is what I was attempting.

Theo · Accepted Answer · 2020-09-24 13:34:16Z

If I understand correctly, you have an xml file you would like to convert into another (less complex) xml.

Using your example (and corrected the closing tags), You could do something like this:

$recordTemplate = @"
    <Record>      
        <RefID>{0}</RefID>
        <Name>{1}</Name>
        <Parent>{2}</Parent>
        <FullName>{3}</FullName>
        <Extension>{4}</Extension>
        <CreationTime>{5}</CreationTime>
        <LastAccessTime>{6}</LastAccessTime>
        <LastWriteTime>{7}</LastWriteTime>
        <Attributes>{8}</Attributes>
        <BaseName>{9}</BaseName>
        <Mode>{10}</Mode> 
    </Record>
"@

[xml]$xml = Get-Content -Path 'D:\test\complex.xml' -Raw
$records = foreach ($item in $xml.Obj.Props.Obj) {
    # create a Hashtable for the 'S' elements
    $sHash = @{}
    $item.Props.S | ForEach-Object { $sHash[$_.N] = $_.'#text' }

    # create a Hashtable for the 'DT' elements
    $dtHash = @{}
    $item.Props.DT | ForEach-Object { $dtHash[$_.N] = $_.'#text' }

    # get the two values from the MS element 
    # (you could also do this with a hashtable of course..)
    $baseName = ($item.MS.S | Where-Object { $_.N -eq 'BaseName' }).'#text'
    $mode = ($item.MS.S | Where-Object { $_.N -eq 'Mode' }).'#text'

    # output a Record node using the template
    $recordTemplate -f $item.RefId, $sHash.Name, $sHash.Parent,
                       $sHash.FullName, $sHash.Extension,
                       $dtHash.CreationTime, $dtHash.LastAccessTime,
                       $dtHash.LastWriteTime, $sHash.Attributes,
                       $baseName, $mode
}

# create the full XML as string
$newXml = @"
<?xml version="1.0" encoding="utf-8"?>
<Root>
$($records -join [environment]::NewLine)
</Root>
"@

# save the xml string to disk
$newXml | Set-Content -Path 'D:\test\readable.xml' -Encoding Utf8

Output:

<?xml version="1.0" encoding="utf-8"?>
<Root>
    <Record>      
        <RefID>1</RefID>
        <Name>Archive Files</Name>
        <Parent></Parent>
        <FullName>N:\Archive Files</FullName>
        <Extension></Extension>
        <CreationTime>2016-02-24T08:23:01.3111895+00:00</CreationTime>
        <LastAccessTime>2019-09-19T03:00:01.2502407+01:00</LastAccessTime>
        <LastWriteTime>2016-02-24T08:25:10.3301992+00:00</LastWriteTime>
        <Attributes>Directory</Attributes>
        <BaseName>Archive Files</BaseName>
        <Mode>d----</Mode> 
    </Record>
</Root>

Michael Kay · Accepted Answer · 2020-09-24 20:08:39Z

0

In XSLT you can do most of the work with a template rule:

<xsl:template match="*[@N]">
  <xsl:element name="{@N}">
    <xsl:value-of select="."/>
  </xsl:element>
</xsl:template>

That's the only part that I can see being a little bit tricky, so I'll leave the rest to you.

answered Sep 24, 2020 at 20:08

Michael Kay

165k11 gold badges97 silver badges173 bronze badges

Collectives™ on Stack Overflow

XSL Complex Transform from PowerShell Output

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related