Update Raw XML with Formula - Find Replace

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?

Test Input Macro.yxzp

Custom Tools

Accepted answers

All comments

OllieClarke

Hi @bkclaw113 I think the issue here is whether the values in your find/replace have quotes in them. as these could (for want of a better word) break the Replace() function, by terminating the string early. You could try wrapping the control parameter in single quotes, assuming they will never appear in the string, if the string could have a combination of double and single quotes, then it might get a bit tricky...

OllieClarke

@bkclaw113 just continuing on that thought, I'd always recommend using the test view in the interface designer so you can see the interface values, or use debug mode to see what is actually going on with the problem values.

ChrisTX

You mentioned viewing the XML in Notepad++. Did you know you can view the XML for each tool in Alteryx, by changing the user setting "Display XML in Properties Window"?

Are you just trying to change the path to your CSV file? In your second screenshot, in the section "XML Element to Update", you have the first line highlighted in blue. If you instead highlight the line for "File - value = \A;teryx....", you should be able to easily update the path to the CSV. In that "XML Element to Update" section, the macro will update whichever line you have highlighted in blue.

And like @OllieClarke mentioned, in the Interface Designer window, the Test View button (magic wand) and the Debug button are a huge help.

Chris

bkclaw113

@ChrisTX, yes I did know about viewing teh XML from within Alteryx, I just prefer using notepad++ as it is easier to copy into and out of and keep notes as I am trying different approaches.

Also the selection of the entire Input, verses the File tag is intentional as I this is just a POC and I ultimately want/need to be able to change any of the tags, so my find/replace needs to act on the entire configuration tag, not just a single child element.

bkclaw113

@OllieClarke since the values that I am using in the Find and Replace control parameters are being passed by value as strings, I am not including any (single or double) quotes in the strings, however I did manual testing and did use single quotes to wrap the strings which only contained double quotes and I still experienced the same issue. Also over the course of testing I did pass values that included double quotes and it worked:

Find: RecordLimit=""

Replace: RecordLimit="1"

Works without issue, so I do not believe it is the quotes

bkclaw113

I am using the interface view and debug mode, as I agree they are terrific tools under normal circumstances to understand odd behavior like this, however in this case, particularly with using the replace function it either finds the search string and performs the replace, or it does not find it and nothing changes. Please let me know if there is something else that I am missing, but I don't think the answer lies in debug mode.

bkclaw113

Just to add some more to this investigation, I took the simple piece that was working:

Find: RecordLimit=""

Replace: RecordLimit="1"

Then I used the generate rows to add every possible character from ascii 1 to 256 in front of each string. The only character that resulted in the replace being executed (and returning only a single row of data) was char(32) which is <space>. I then repeated the process to see if any character prior to the space would work and char(109) (lower case m) worked, which from viewing the raw XML (in either Alteryx or notepadd++) does not appear anywhere. Having success finding that 'm RecordLimit=""' worked as the search string I tried the generate rows again and no character before the 'm' caused the Find value to be found, so I am again at a loss for what to try next.

bkclaw113

@atcodedog05 We talked about this a few weeks ago, wanted to bring the detail to your attention to see if you had any ideas now that it is out of the theoretical and into an actual workflow.

OllieClarke

Hey @bkclaw113 I've been having a play with this. I found that it was the chevrons which were causing the problem.

Finding

File RecordLimit=""

and replacing with

File RecordLimit="15"

works fine, but it doesn't work when you include the < before File.

Luckily if you change your action tool to 'Update Outer XML', then I think that solves the issue. This means that you're including the xml tags in your replace function (more so in the find part), and so they will be updated.

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.

Quick Links

This months top contributors

SophiaF 432

AkimasaKajitani 390

mceleavey 388

mbarone 337

michael_weaver 335