Update Raw XML with Formula - Find Replace

[bkclaw113]

I am working on a proof of concept to update the configuration of tools using the Update RAW XML with Formula action type from within the action tool and running into problems with using the replace function to locate the existing XML and replace it with my desired XML.

I have created a batch macro with 2 control parameters (Find and Replace). Those control parameters feed into a single Action tool that is using the Update Raw XML with Formula action type, and attempting to update the inner XML for the Input Data tool.

This works when I Find/Replace a single value like the file name

Or a single unquoted attribute like Field Length

However when I try to find a longer string that that includes both the tag and an attribute within the tag, like in this example where I am changing both the record limit and the input file name (note the string was copied directly from the macro's XML opened in Notepad ++)

Alteryx appears not to find the string and so the configuration for the input in the macro is not modified. This feels like an issue with the syntax that is being searched for but I have no idea how to determine what the correct syntax should be. Anyone run into this before or have suggestions on how to find and replace large

So with the underlying XML as seen in notepad ++ as:

<File RecordLimit="" SearchSubDirs="False" FileFormat="0" OutputFileName="FileName">D:\Alteryx\Projects\Config Manager\Sample.csv</File>

When I find/replace looking for RecordLimit="" it works

But when I look for File RecordLimit="" it does not work

To get even more specific it works with a single space before record limit ' RecordLimit=""'

But does not work with the "e" from "File" and the space e RecordLimit=""

So I am guessing that maybe there is another character that needs to go around the attribute names found inside of an XML tag (forgive me if I am not using correct nomenclature as I am not an XML person) any suggestions?

[OllieClarke]

@bkclaw113 One thing I'm not sure about is when inputting multiple lines of xml. 

For instance, replacing:

<File RecordLimit="" SearchSubDirs="False" FileFormat="0" OutputFileName="">\\Mac\Home\Documents\Data\Sales\Austria.csv</File>

with

<File RecordLimit="15" SearchSubDirs="False" FileFormat="0" OutputFileName="FileName">\\Mac\Home\Documents\Data\Sales\France.csv</File>

works fine, but trying:

<ImportLine>1</ImportLine>
            <FieldLen>254</FieldLen>

and replacing with

<ImportLine>2</ImportLine>
            <FieldLen>8</FieldLen>

isn't working. 

Running the same find and replace in a text editor, and saving the macro does work though (as you noticed).

This only makes sense to me if the whitespace is not being respected/being treated differently. But the replace function recognises newlines and spaces, so I wouldn't expect this issue with a normal formula tool

Doubly unfortunately, I'm really struggling to think of a way to output the destination of the update xml via formula, so we can test. 

Sorry, I may have been a bit premature with my previous comment's 'solution'...

[bkclaw113]

@OllieClarke, can you share a workflow where you have the example working:

    File RecordLimit="" 

    and replacing with

    File RecordLimit="15"

I have tried that and it does not work for me. FYI I am only on version 21.4 so this may be a bug that was fixed on more recent versions.

[OllieClarke]

Hey @bkclaw113 

I've attached the packaged workflow with macro and file. I tested it on version 2020.3 and it works there too, although I've only used a csv file, but I don't think that would be the issue here.

Hope that helps a bit at least

Ollie

[atcodedog05]

Hi @OllieClarke 

This is my observation. I am using 2021.2

Case 1: I ran your workflow directly with the macro. I got the expected output. - It worked.

Case 2: I inserted and ran your macro in a new canvas and into @bkclaw113 canvas(2021.4). - It failed

Case 3: I inserted and ran @bkclaw113 macro in your canvas(2020.3) - It worked

It seems like the 2020.3 version of the canvas makes it work. I am looking into the canvas XML code to see what is the difference. Maybe adding the piece of code which is distinctive in the 2020.3 canvas might help solve the issue 

Definitely an intriguing use case.

[OllieClarke]

@atcodedog05 

I built the macro on 2022.3, and it worked there too. Interesting to see what changed between 2020.3, 2021.4 and 2022.3…

[bkclaw113]

I used the macro that I created to search all possible character combinations and made it iterative to identify what is being read from the <Destination> field, and it appears that that the order of the attributes within the File tag are not in the same order as seen in the XML view in Alteryx or in the raw text when viewed from Notepad++

Here is a screen shot of the Alteryx XML view of my input file, notice RecordLimit is listed first.

Now when I execute the find/replace against that I am using:

Find: <File FileFormat="0" OutputFileName="FileName" RecordLimit="100"

Replace: <File FileFormat="0" OutputFileName="" RecordLimit="1"

Which is finding the FileFormat first. (note, the <Destination> at least in this case appears to have attributes listed alphabetically, this could be by chance but I will try to test some more to see if it holds true in other circumstances)

This creates a real problem since I am looking for a flexible solution that can change the configuration dynamically. It is not feasible to run thousands of tests through an iterative macro to generate the correct strings through brute force. I tried using the AutoDocumenter (https://gitlab.com/keyrus-us/public/alteryx_auto_doc_revamp/-/tree/master) python code to parse out the XML for the Input tool, but it is returning the attributes in the same order that I am seeing them in ALteryx XML / Notepad++.

The alternative that I can see is to not try to replace the entire configuration block but instead have multiple find/replace that operate in sequence changing 1 attribute at a time so that the overall order does not matter.