One of the best features of Alteryx is its hackability. If getting a macro to do what you want looks impossible, think again! I’ve started using “Update Raw XML with Formula” on the Action tool to get that little bit extra out of my macros. If you’re new to macros in general, this post might be a bit tricky. I’ll cover…
- Viewing XML in Alteryx
- A quick crash course in XML
XML in Alteryx
No one is interested in the history of XML (at least I’m certainly not). But if you are, there’s Wikipedia. What you need to know is XML is behind everything you see and do in Alteryx. Every tool is XML. The entire workbook is XML. You can right-click any workbook file, open it with a text editor (e.g. Notepad), and you get XML.
XML needs to be enabled in user settings to view it in Alteryx.
Options -> User Settings -> Advanced -> Display XML in Properties Window
There’s now a new icon for every tool in the configuration pane!
XML Crash Course
I find examples helpful in learning and applying a concept. Here’s a good resource for a more general overview of XML, but I’ll cover the essentials needed for Alteryx.
Let’s create a Text Input with some simple data. The new icon </> opens the XML View of the tool we just created.
Creating the text input…
Adding a new column…
Adding a new row…
The key takeaway here is seeing how the tool configuration relates to the tool XML view. It will be different for each tool, however, becomes intuitive once you’re a familiar with XML.
The Action Tool
Connect an action tool to the text input and select Update Raw XML with Formula. If you’re interested, the syntax displayed here is XPath, but it’s essentially the XML with drop-down menus.
Note: the formula you create needs to be XML (not what it looks like in XPath).
Inner vs Outer XML
There are two options – update inner XML and update outer XML. Both are useful depending on what you want to do, and I’ll cover both with examples.
XML has either two tags (<> … </>) or one tag (<…/>). Outer XML includes the tags, inner XML is whatever is inside the tags (including other tags).
Let’s get our action tool to change the number 5 to 22. Currently, our XML for that line looks like <c>5</c>. The number 5 is inside the two tags, so changing this can be done choosing Update Inner XML and entering ’22’.
Alternatively, this can also be done by choosing Update Outer XML. The full XML must be entered ‘<c>22</c>’ as it outer includes the tags.
Single Tag (Outer) Example
Let’s get a bit more tricky and change the “col 1″ field name. The XML looks like <Field name=”col 1” />. Because there’s no internal XML here, the only method is updating outer XML.
Multiple Inner Example
One last example, inner XML includes everything between the inner and outer XML tags. So, selecting r and updating the inner XML we can update an entire row ‘<c>one</c><c>two</c><c>three</c>’.
Partial Multiple Inner Example
You may be wondering, what happens if I don’t include all the tags? Interestingly, it doesn’t error and ignores the missing values. This can be very powerful with the right tool. For example, a select tool can be configured to select a single column and rename it. With the text input tool, we get nulls.
As always, thanks for taking the time to read my blog! If you have any comments, suggestions or want to chat, free to connect with me on my LinkedIn!
~ Ryan Edwards