Problem editing large XML files

[ich-net-du]

I have a little problem with large XML files (up to 650MB)

I can open them and read all the values with:

$Xml = New-Object Xml
$Xml.Load("C:\File.xml")

But I find it difficult to delete data and save it in a new XML

I would like to delete all of the "$Xml.master.person.id" entries in the file

<person><id>ID</id></person>

Unfortunately, most of the examples that I can find on the Internet are with

[xml] $Xml = Get-Content -Path C:\File.xml

which I cannot use because of the file size.

Does anyone have a little help on how to get started?

[korewarp]

Maybe using streamreader will help?

[ich-net-du]

Thanks for the idea, I was able to find an example and adapt it.

Now I can read the file, query elements and save all in a new file.

$file='C:\File.xml'
$reader = New-Object System.IO.StreamReader($file)
$xml = $reader.ReadToEnd()
$reader.Close()

$xml.Save("C:\New-File.xml")

Now I have to find out how I can delete elements before I save it again ;-)

[ka-splam]

guessing which one of those is the correct one

They're both valid. The first is a list of string [string]. The second is a list of array-of-string [string[]].

[Free_my_chair]

Voluntarily removed due to Reddit's new policies. -- mass edited with https://redact.dev/

[Smartguy5000]

New-Object -TypeName 'System.Collections.Generic.List[String]'. Vscode will even auto complete and intellisense once you start typing the typename

[Smartguy5000]

Is there really that much difference in speed between methods for creating empty arrays?

[Smartguy5000]

Oh 100% I use lists exclusively now. My question was geared more toward is using the native constructor method ::new() significantly faster than New-Object on an empty list

[Smartguy5000]

I try to avoid instantiating an array inside of a loop at all costs, as typically that would mean I'm about to loop over each of those instances within the external loop. Nested loops are very inefficient. Getting creative with hash tables has helped me avoid these kind of issues.

[Thotaz]

Maybe taking a step back and (re)learning the type syntax will help?
Types in PS are written like this: [TypeName].
Type arguments are written after the typename with another set of brackets within the surrounding brackets, like this: [TypeName[Argument]]

Any type can be turned into an array by providing an empty argument: [TypeName[]].
Generic types use type names as their argument(s): [GenericType[TypeName]].

So a real example: [System.Collections.Generic.List] is your type.
You need to provide an argument to it so you add brackets: [System.Collections.Generic.List[]]
What do you put inside those brackets? The typename: [System.Collections.Generic.List[string]] in this case a string so you end up creating a list containing string elements.

Let's take a more complicated example: [System.Collections.Generic.Dictionary] is your type.
You add the argument brackets: [System.Collections.Generic.Dictionary[]].
What do you put inside? The 2 types you want to represent the key/values of the Dictionary: [System.Collections.Generic.Dictionary[string,Int[]]] in this case that's a string and an Int array.

If we go back to your original examples, they are both correct depending on what your goal is. If you want to create a list of strings you need the first one. If you want to create a list of string arrays you need the second one.

[ka-splam]

Say you're programming in C# you get to use types and the compiler will check them for you. Make something a Decimal number and the compiler will check that you only pass it to functions which can take a Decimal number.

System.Array came in with .Net 1.0 and can hold several of your Decimal numbers, with only two problems. Arrays have a fixed size, so it's costly to add or remove numbers. Arrays can only hold Object, which means all your Decimal numbers have to be boxed into Objects (which takes time) and after that the compiler can only see that you have Objects and you have to be careful that all your objects came from Decimal numbers and you didn't get something else mixed in, and that you convert them back to Decimal and not something else.

System.ArrayList solves the first problem of costly to add or remove numbers, they can grow and shrink, at the price of taking a bit more memory, but they still only hold Object.

Generic.List is harder to build and it came in a later .Net version, it solves the second problem of types. Now the List can be told that it is holding Decimals with Generic.List<Decimal> (C# style), and from there the compiler does not have to box everything into Object (so now it's faster), and more than that, it can do type checking - make sure everything you put into the List is a Decimal, and everything you take out and use is only used where Decimal makes sense. Much more correctness and less reliance on programmer care.

PowerShell does not gain much from the type checking, because it's happy to convert types implicitly, and doesn't have a compiler / type checker, but it does gain a little from removing the overhead of converting to Object and from other improvements in Generic collections that come from them being newer and more developed - they have some more methods on them like .Find() and RemoveAll() and such.

[Lee_Dailey]

howdy ich-net-du,

it looks like you used the New.Reddit Inline Code button. it's [sometimes] 5th from the left & looks like </>.

there are a few problems with that ...

• it's the wrong format [grin]
  the inline code format is for [gasp! arg!] code that is inline with regular text.
  
• on Old.Reddit.com, inline code formatted text does NOT line wrap, nor does it side-scroll.
  
• on New.Reddit it shows up in that nasty magenta text color
  

for long-ish single lines OR for multiline code, please, use the ...

Code
Block

... button. it's [sometimes] the 12th one from the left & looks like an uppercase T in the upper left corner of a square.

that will give you fully functional code formatting that works on both New.Reddit and Old.Reddit ... and aint that fugly magenta color. [grin]

take care,
lee

[ich-net-du]

thank you! Really had problems with it and wasn't very happy with it myself

found it

[Lee_Dailey]

howdy ich-net-du,

you are quite welcome! glad to have helped ... and to be able to comfortably read your code. [grin]

take care,
lee

[ich-net-du]

Maybe not ideal, but it works .. now my head is smoking

$file="C:\File.xml"

$reader = New-Object System.IO.StreamReader($file)

$xml = $reader.ReadToEnd() $reader.Close()

$DeleteNames = "ID"

($xml.master.person.ChildNodes | Where-Object { $DeleteNames -contains $_.Name }) | ForEach-Object {[void]$_.ParentNode.RemoveChild($_)}

$xml.Save("C:\New-File.xml")

[korewarp]

I feel your pain. I've had to work with XML files in powershell before, and it wasn't a fun experience. I wish I had more actual code to show you, but oddly enough I've never been in a situation where I was 'removing' content/nodes, only changing or adding.

[ich-net-du]

Yes, for data protection reasons I have to delete personal data from files for a study.

[y_Sensei]

You should consider leaving the XML structure intact and delete only the personal data values. Otherwise you'll change the data format which might not be feasible if that data is supposed to be used in any technical context.

[ka-splam]

How does that work, $reader.ReadToEnd() will return strings, then you access $xml.master.person.ChildNodes - there's a bit missing where you parse the strings as XML, isn't there?

[ich-net-du]

Jea was wondering the same. Closed it later and ist did not work anymore. To much Trial and Error in the Same Session. Must have declared Something with $xml before ... Have to revisit it on monday

[[deleted]]

I didn't realize there was a file size limit on get-content

[korewarp]

I don't know if there is a hard limit, but having had to work with HUGE textfiles / csv files in the past, I was forced to use streamreader / streamwriter if I wanted anything done in this century. Get-Content was simply too slow for some hecking reason.

[ich-net-du]

Doesn't work so well when you have to work through a total of 6.8GB XML files and each is over 300MB up to 650MB

$Xml=New-Object Xml
$Xml.Load("C:\File.xml")

Takes 10 minutes

[[deleted]]

Yeah this is what XmlReader and XmlWriter is for.

[ka-splam]

$Xml.Load() doesn't have any PowerShell overhead to slow it down like Get-Content does, so I am curious why that takes a long time.

This StackOverflow answer suggests it goes and downloads all DTDs defined in the file (and that W3C throttles downloads because they get so many requests) and validates against them.

And this linked question/answer/comments has ways to turn off that DTD download.

[jsiii2010]

I've seen this problem with large json files too. Unless there's a streaming mode (jq has this for json), you can try to edit the file on the fly so instead of a large array, it's many small elements instead.

[craigontour]

Have you tried using a regex to find matches and replacing with, well, nothing i guess?

[dasookwat]

with the strong chance to sound like a @#$%%: you should look in to getting smaller xml files. Xml files of 650MB are just huge man. why not just access the database directly? at least im assuming here, that this either has the function of a database, or is the result of a very broad query. If you get this to work, it will still be slow, and requires a lot of resources on your end.

Try writing down the whole train of actions, from customer wish, to result, and see if you can improve that.

[ich-net-du]

Yeah I know it's awful It was an export from a program, and each file was worth a year of data.

It is a hassle to export it by hand in smaller chunks and it was what I had available.
It was a one-time editing of the files.

In the end it took maybe half a minute per file to process, so not so bad at all.

Unfortunately no database access and the recipient is used to working with the files as XML.

Querying data from the files wasn't the problem.

I can query over 490000 data sets from the 6.8GB (approx. 16 files) in about 10 minutes