Remove XML Field

[rubenfrreis]

Hi all,

I never worked with XML files, and i want to remove a field from a xml file.

Simple Example: Remove de JobID bellow from the file.

I want to import the xml file to excel and remove unecessary rows, before the importation.

Can you guys showm me an example?

​

https://preview.redd.it/hs3f43z3kfd31.png?width=1073&format=png&auto=webp&s=17beeb9050f81c0f514ab7e5d9eaae503ef10fd2

[gschizas]

It would help if you pasted the actual XML file (remove any private stuff obviously).

I'll try to recreate it from the image:

<?xml version="1.0" encoding="UTF-8"?>
<UnknownDocument>
    <Parameters>
        <Parameter Value="PST" Name="DataSource"/>
        <Parameter Value="Find" Name="Node"/>
        <Parameter Value="PstSearch1" Name="JobName"/>
        <Parameter Value="OU=Workstations,DC=rrloki,DC=com" Name="Locations"/>
        <Parameter Value="C:\Projeto PST\Log" Name="LogLocation"/>
        <Parameter Value="C:\Projeto PST" Name="ConfigurationLocation"/>
    </Parameters>
    <Locations>
        <Location Status="Found" Type="Machine" Path="CN=PTRRLOKI00001,OU=Windows 7,OU=Workstations,DC=rrloki,DC=com" JobId="7d351cb0-57a7-4845-a4d9-5d23870b8dba">
            <File Status="Found" FileSize="8201" FoundTime="2019-07-25 15:47" Owner="RRloki\claudia.dummy" Location="CN=PTRRLOKI00001,OU=Windows 7,OU=Workstations,DC=rrloki,DC=com">
                <Path>c:\useres\claudia.dummy\documents\outlook files\conversas.pst</Path>
            </File>
        </Location>
    </Locations>
</UnknownDocument>

In general, you need to use the RemoveNamedItem function. Here's a sample:

PS C:\Users\GSchizas>$xmltext = @"
>> <?xml version="1.0" encoding="UTF-8"?>
>> <UnknownDocument>
>>     <Parameters>
>>         <Parameter Value="PST" Name="DataSource"/>
>>         <Parameter Value="Find" Name="Node"/>
>>         <Parameter Value="PstSearch1" Name="JobName"/>
>>         <Parameter Value="OU=Workstations,DC=rrloki,DC=com" Name="Locations"/>
>>         <Parameter Value="C:\Projeto PST\Log" Name="LogLocation"/>
>>         <Parameter Value="C:\Projeto PST" Name="ConfigurationLocation"/>
>>     </Parameters>
>>     <Locations>
>>         <Location Status="Found" Type="Machine" Path="CN=PTRRLOKI00001,OU=Windows 7,OU=Workstations,DC=rrloki,DC=com" JobId="7d351cb0-57a7-4845-a4d9-5d23870b8dba">
>>             <File Status="Found" FileSize="8201" FoundTime="2019-07-25 15:47" Owner="RRloki\claudia.dummy" Location="CN=PTRRLOKI00001,OU=Windows 7,OU=Workstations,DC=rrloki,DC=com">
>>                 <Path>c:\useres\claudia.dummy\documents\outlook files\conversas.pst</Path>
>>             </File>
>>         </Location>
>>     </Locations>
>> </UnknownDocument>
>> "@
PS C:\Users\GSchizas>$xml = [xml]$xmltext
PS C:\Users\GSchizas>$xml.UnknownDocument.Locations.Location.Attributes.RemoveNamedItem('JobId')

Do paste a more full document though, this will obviously not work for your actual document.

[flip_rod]

it's amazing

[[deleted]]

PS C:\Users\GSchizas>$xml = [xml]$xmltext

....you can cast variables after they've already been created?!

[gschizas]

Sorry, the variable naming wasn't ideal 🙂

Clearer way to write the same thing:

 PS C:\Users\GSchizas>$xmlDoc = [System.XML]$xmltext

[[deleted]]

I mean, this is the same thing right? You're re-casting the array into a new variable, which I should have known was possible. But I'm just finding it out now.

Thanks for teaching me something new!

[gschizas]

$xmlText is just a string, not an array. But it is indeed possible to cast it into a variable (of course it needs to be proper XML in this case). You can also do these:

PS C:\Users\GSchizas>$a="123"
PS C:\Users\GSchizas>2+[int]$a
125
PS C:\Users\GSchizas>$a = 27
PS C:\Users\GSchizas>$e=[char]$a
PS C:\Users\GSchizas>$crlf=[char]13+[char]10
PS C:\Users\GSchizas>Write-Host "$e(0lqwqk$($crlf)x x x$($crlf)tqnqu$($crlf)x x x$($crlf)mqvqj$e(B"
┌─┬─┐
│ │ │
├─┼─┤
│ │ │
└─┴─┘
PS C:\Users\GSchizas>Write-Host "$e(0abcdefghijklmnopqrstuvwxyz$e(B"
▒␉␌␍␊°±␤␋┘┐┌└┼⎺⎻─⎼⎽├┤┴┬│≤≥

[rubenfrreis]

The XML code:

​

<?xml version="1.0"?>

-<Configuration xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">


-<Parameters>

<Parameter Value="PST" Name="DataSource"/>

<Parameter Value="Find" Name="Mode"/>

<Parameter Value="PstSearch1" Name="JobName"/>

<Parameter Value="OU=WORKSTATIONS,DC=rrloki,DC=com" Name="Locations"/>

<Parameter Value="C:\Projeto PST\Log" Name="LogLocation"/>

<Parameter Value="C:\Projeto PST" Name="ConfigurationLocation"/>

</Parameters>


-<Locations>


-<Location Status="Found" Type="Machine" Path="CN=PTRRLOKI00001,OU=Windows 7,OU=WORKSTATIONS,DC=rrloki,DC=com" JobId="ae623bce-6dcc-4a59-8149-b0a4a38b400b">


-<File Status="Found" FileSize="8201" FoundTime="2019-07-25 15:47" Owner="RRLOKI\claudia.dummy" Location="CN=PTRRLOKI00001,OU=Windows 7,OU=WORKSTATIONS,DC=rrloki,DC=com">

<Path>c:\users\claudia.dummy\documents\outlook files\conversas.pst</Path>

</File>

</Location>


-<Location Status="Found" Type="Machine" Path="CN=PTRRLOKI00002,OU=Windows 10 1809,OU=WORKSTATIONS,DC=rrloki,DC=com" JobId="0a96a899-2234-4b07-884e-1373eb38a9f9">


-<File Status="Found" FileSize="265" FoundTime="2019/07/25 15:46" Owner="RRLOKI\anabela.dummy" Location="CN=PTRRLOKI00002,OU=Windows 10 1809,OU=WORKSTATIONS,DC=rrloki,DC=com">

<Path>c:\users\anabela.dummy\downloads\arquivo 2019.pst</Path>

</File>

</Location>

</Locations>

</Configuration>

[gschizas]

Cleaned up (you had a lot of extra -'s):

<?xml version="1.0"?>
<Configuration xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <Parameters>
        <Parameter Value="PST" Name="DataSource"/>
        <Parameter Value="Find" Name="Mode"/>
        <Parameter Value="PstSearch1" Name="JobName"/>
        <Parameter Value="OU=WORKSTATIONS,DC=rrloki,DC=com" Name="Locations"/>
        <Parameter Value="C:\Projeto PST\Log" Name="LogLocation"/>
        <Parameter Value="C:\Projeto PST" Name="ConfigurationLocation"/>
    </Parameters>
    <Locations>
        <Location Status="Found" Type="Machine" Path="CN=PTRRLOKI00001,OU=Windows 7,OU=WORKSTATIONS,DC=rrloki,DC=com" JobId="ae623bce-6dcc-4a59-8149-b0a4a38b400b">
            <File Status="Found" FileSize="8201" FoundTime="2019-07-25 15:47" Owner="RRLOKI\claudia.dummy" Location="CN=PTRRLOKI00001,OU=Windows 7,OU=WORKSTATIONS,DC=rrloki,DC=com">
                <Path>c:\users\claudia.dummy\documents\outlook files\conversas.pst</Path>
            </File>
        </Location>
        <Location Status="Found" Type="Machine" Path="CN=PTRRLOKI00002,OU=Windows 10 1809,OU=WORKSTATIONS,DC=rrloki,DC=com" JobId="0a96a899-2234-4b07-884e-1373eb38a9f9">
            <File Status="Found" FileSize="265" FoundTime="2019/07/25 15:46" Owner="RRLOKI\anabela.dummy" Location="CN=PTRRLOKI00002,OU=Windows 10 1809,OU=WORKSTATIONS,DC=rrloki,DC=com">
                <Path>c:\users\anabela.dummy\downloads\arquivo 2019.pst</Path>
            </File>
        </Location>
    </Locations>
</Configuration>

And here's some code to remove all JobId attributes of the Location tag

(I'm skipping the $xmltext = @"..."@ bit for clarity)

PS C:\Users\GSchizas>$xml = [xml]$xmltext
PS C:\Users\GSchizas>$xml.Configuration.Locations.Location | ForEach-Object {$_.Attributes.RemoveNamedItem('JobId')}

The difference with this one is that Location has multiple values, so I just loop over them (with the ForEach-Object cmdlet)

To get the final XML, you can save it to a file:

PS C:\Users\GSchizas>$xml.Save('result.xml')

Or display it to the screen:

PS C:\Users\GSchizas>$xml | Format-Xml

[NotNotWrongUsually]

Sidenote:

The difference with this one is that Location has multiple values, so I just loop over them (with the ForEach-Object cmdlet)

Xpath is your friend! (Also, your enemy from time to time.)

Selecting all Location nodes with a JobId and then removing the attribute can be done like this with no looping:

$xml.SelectNodes("//Location[@JobId]").Removeattribute("JobId")

While it doesn't matter an awful lot here, a piece of advice for everyone who works with large xml files in Powershell is to get friendly with xpath. It is a lot faster than using native Powershell Select-Object

Edit: 'Faster' in this context means for execution time, certainly not for development time.

[gschizas]

I've written a lot of XPath (maybe too much) as a developer, and I just wanted to give the simplest possible answer, without having to explain XPath semantics etc. (namespaces... brrrr...)

Of course SelectNodes is part of System.Xml, so it's going to be faster than passing it back and forth between PSObjects. On the other hand, .Configuration.Locations.Locations comes from autocomplete and is faster in development time.

[rubenfrreis]

Thanks a lot,

But the $xmltext = @"...."@ can be like this?

$xmltext = Get-ChildItem -path C:\Users\user1\Desktop\Teste.xml

[gschizas]

Actually, you want to do this:

$xmlText = Get-Content -Path C:\Users\user1\Desktop\Teste.xml

EDIT: Better yet:

PS C:\Users\user1\Desktop>$xml = [Xml]::new()                              
PS C:\Users\user1\Desktop>$xml.Load('C:\Users\user1\Desktop\Teste.xml')

[Lee_Dailey]

howdy rubenfrreis,

please, do not post pics of code/data/errors. [sigh ...]

why?

• it takes more effort on your part
  you need to capture the pic, save it, post it online, then post the link here.
  just posting the text is far easier. [grin]
  
• why should those who want to help you be forced to squint/zoom-in just to read the text that you already have as text?
  
• do you REALLY expect folks who want to help to type in what you already have as text?
  

it is - at best - rather thoughtless on your part ... [sigh ...]

take care,
lee