How to Query & Extract data from OneStream metadata XML using XPath & XSLT

Background

OneStream supports exporting metadata into XML file for backup and restore purpose (via menu Application > Tools > Load/Extract). This blog covers technique to extract this information from metadata XML using technology named XSLT (eXtensible Stylesheet Language Transformation), which can read XML hierarchy & extract information from it.

Tools Required

Microsoft Visual Studio supports creating/editing XML & XSLT files, with in-built intellisense (auto-complete) and a validator, which checks for correctness of XSLT file. Visual Studio comes with XSLT processor for handy XML transformations which developers might require. Microsoft offers Community Edition of Visual Studio, available freely, suitable for light-weight development & tasks.

Data at a Glance

Below is the demo Account Member hierarchy which we shall be extracting from XML (screenshot below)

Below is the screenshot of Metadata XML as appearing in Visual Studio, extracted via Load/Extract menu

Understanding XPath

XML file contains hierarchal data. Querying tree structure data is a tricky task compared to tabular data, which can be queried easily using SQL. XPath is used to query XML data.

Let’s see, we want to query description value of Account member 1001. Below will be the XPath expression for this

/OneStreamXF/metadataRoot/dimensions/dimension[@type=’Account’]/members/member[@name=’1001′]/@description

XML tags are represented by /tag and XML attributes by @attribute. XPath supports filtering of data, by specifying query condition in square braces for that tag.

Drafting XSLT

XSLT is used for querying & transforming data from XML file, and generating output in XML or text format. XSLT is written using XML itself, with few XML tags instructing how to transform data. Visual Studio ships XSLT processor, capable of executing on-the-fly transformations via GUI menu. Below is the demo XSLT file, which extracts data from above XML file & generates textual output in Tab delimited format, which can be dumped into Excel or even imported into SQL Server easily.

Revolutionize Your Business With Generative AI

From product design and software development to virtual agents, content creation, and reporting, GenAI is transforming business. Our AI experts help you unlock GenAI’s full potential and drive growth.

Let’s Get Started

XSLT can be copy-paste from below

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl"
>
    <xsl:output method="text" indent="yes"/>

    <xsl:template match="/">
        <xsl:text>Dimension&#x9;Name&#x9;Description&#x9;Account Type&#xA;</xsl:text>
        <xsl:for-each select="OneStreamXF/metadataRoot/dimensions/dimension[@type='Account']/members/member">
            <xsl:value-of select="../../@name"/>
            <xsl:text>&#x9;</xsl:text>
            <xsl:value-of select="@name"/>
            <xsl:text>&#x9;</xsl:text>
            <xsl:value-of select="@description"/>
            <xsl:text>&#x9;</xsl:text>
            <xsl:value-of select="./properties/property[@name='AccountType']/@value"/>
            <xsl:text>&#xA;</xsl:text>
        </xsl:for-each>
    </xsl:template>
</xsl:stylesheet>

Let’s understand various parts of XSLT:

<xsl:output method=”text” indent=”yes”/>

This instruct XSLT to generate output in textual format

<xsl:text>Dimension&#x9;Name&#x9;Description&#x9;Account Type&#xA;</xsl:text>

This line will insert a static column header in output file. XSLT being XML internally, needs escaping of tab (&#x9;) & newline (&#xA;)

<xsl:for-each select=”OneStreamXF/metadataRoot/dimensions/dimension[@type=’Account’]/members/member”>

Above XSLT line, runs a for-each loop of all the member under the dimension which are of type Account.

<xsl:value-of select=”@description”/>

This line emits content of description attribute of member tag from XML.

<xsl:value-of select=”../../@name”/>

../ is XPath expression to fetch value of relative parent. So we are going 2 levels up, to then dimension XML node and then extracting value of name attribute from it.

<xsl:value-of select=”./properties/property[@name=’AccountType’]/@value”/>

./ is XPath expression to extract values from relative child XML node.

Generating Output File

Steps to generate textual output file

1. Open XSLT file in Visual Studio
2. Go to Properties window and browse XML file in Input and specify location of Output file in Output browse section
   
3. Navigate to XML > Start XSLT without Debugging
   
4. This will generate & save Output file and open it in Visual Studio
   
5. This content can be copy-pasted into Excel or even imported into Database using BULK INSERT statement
   

Other Benefits

• It can filter & extract data from entire backup metadata XML contains multiple Dimensions like Account, Entity etc.
• This approach is not limited to Accounts Dimension, but works for all the Dimensions like Entity, Scenario, etc by just changing XPath filter to [@type=’Entity’] and so on.
• This approach can be extended to pull even further columns like IsIC, etc
• Multiple for-each loop can be initiated in single XSLT file to scan all Dimensions like Account, Entity, Scenario, etc to generate consolidated output to be upload into some Database or Data Lake.
• XSLT transformation can be automated via C#/VBA program (in .NET using XslCompiledTransform), or by invoking XSLT compiler from command-line Check this MSDN tutorial

Conclusion

XML is globally used for data interchange, enjoying compatibility with majority of software. OneStream highly leverages XML to backup almost every object / artifact available in the system. Objective of this blog is not just to perform specific task covered in case study, but to gain basic understanding of concept of XPath & XSLT. With a good command over XSLT, one can apply this technique even to re-create XML files with bulk modifications. Endless possibilities exists with varied business use cases one can think of.