Filtering an XML Table

I have a few questions:

1. I have an XML table with multiple keys. Each key has a set of properties. I would like to use a spreadsheet formula to get a table back with only one of the keys.

For example, I have a Tictactoe board with three rows and three columns:

I want to return the first row in a table, like this:

Can't figure out how to do it.

I can get things like "R1,R2,R3" and "C1,C2,C3" and ""-,-,O" just fine, but not the XML.

I can extract all the values I want and reconstruct the XML, but it's a lot of work.

2. I'd like to rename a row. Can't figure that out either.

Also I don't know a handy way to create XML, because of the quotes problem. I end up with things like

="<Property name="&Q&"C1"&Q&" value="&Q&A11&Q&">"

where Q is a named range containing a quotation mark. I can chose the values by creating a formula list, displaying all the attributes and filtering for the key I want. But it isn't pretty.

Find more posts tagged with

Infor EPM Discussions

Comments

JacobDavis

Hi Bernard. I'm working on something similar at the moment and I was playing around a bit with your question.

I was able to create a new XML which pulls only row R1 using the following formula:

=SETPROPERTY(A3,"RowName",GETPROPERTIESA(A1,"R1"),GETPROPERTYVALUESTABLE(A1,"R1",GETPROPERTIESA(A1,"R1")))

Where cell A1 contains your original XML table, A2 contains the formula, and A3 is the new XML to be written (could be a variable instead for instance). "RowName" is the new name you want to give row R1 in your new XML.

Best,

Jacob

BFinucane

Thanks, that's a good solution. But mine has 20 properties, so I was hesitating to do the whole thing by hand, and am not familiar with a "A" functions.

Kai Reischl

Hi Bernard (and Jacob),

there's an easier way: =EXTRACTPROPERTIES("<your XML>","R1")
The EXTRACTPROPERTIES formula allows you to filter by row name, property name, and value, and even accepts placeholders. For example, this formula: =EXTRACTPROPERTIES("<your XML>","*","C1")
... would give you this result:
<Table>
<Row name="R1">
<Property name="C1" value="-"/>
</Row>
<Row name="R2">
<Property name="C1" value="-"/>
</Row>
<Row name="R3">
<Property name="C1" value="X"/>
</Row>
</Table>

Br Kai

0712131256598360.doc

BFinucane

wow that's what I was looking for. Thanks

BFinucane

The function GETPROPERTYVALUESTABLE is not mentioned in the help file, as far as I know. Crucially, no information ans vailable on how to specify more than one row.

I also noticed that the help file ARRAYSORT contains false information.

BFinucane

Kai,

can you tell me a trick for extracting a key (with values) from one XML and inserting it into another? SETPROPERTY only lets me work with intertwined lists of strings manually inserted into the formula.

I see how nesting would work, but it gets ugly with more than a few properties.

Kai Reischl

Hi,

use MERGEPROPERTIES with your original XML and the EXTRACTPROPERTIES as arguments. (Warning: if the key in your extracted properties already exists in your original XML, that might lead to unexpected results.)

BFinucane

What unexpected results do you mean? I would expect any existing property to be overwritten and any new property to be added. Would unadded existing properties disappear?

It would also be nice if help considered MERGEPROPERTIES and EXTRACTPROPERTIES to be related topics. Or that either one is related to XML properties.

And in a final question, is there any way to rename a key?

Copyright © 2025 Infor. All rights reserved.