Change XML tags/fields across entire file

[MancDad]

Apologies if the title is't descriptive but I'm not sure on exactly how to describe the issue in short.

I have a large XML file exported from an on-prem system that we need to import the data into another system (SharePoint) using Power Automate. I have created a Flow to consume the XML data in a simple format (as in the second XML example), however as we have over 40,000 document records each with 10 attributes each I can't change this manually on all records.

Is there a way to easily change my XML from the following:

<Document Scheme="PurchaseInvoice" ID="0B807CEEF97411E4B64500505685156C" SchemeHash="FF298EE05B8622172077FD1E1100E20B">
        <Attributes>
            <NamedDV Name="PIRRef" ID="30ADFF586B6BE26316619C294DFEC637">
                <Value Encoding="UTF-8" Type="TEXT" Locale="en-GB">42103</Value>
            </NamedDV>
            <NamedDV Name="InvoiceDate" ID="5C0491A99EC233C72FEB9C205A0F1D5B">
                <Value Encoding="UTF-8" Type="DATE" Plain="@20100107000000" Locale="en-GB" Millis="0">07/01/2010</Value>
            </NamedDV>
            <NamedDV Name="NetAmount" ID="9C7E756656A4DE19FC3209DC4C94BD66">
                <Value Encoding="UTF-8" Type="DECI" Plain="@150" Locale="en-GB">150</Value>
            </NamedDV>
           </Attributes>
       </Document>

to read like:

<Document>
        <Attributes>
            <PIRRef>42103</PIRRef>
            <InvoiceDate>07/01/2010</InvoiceDate>
            <NetAmount>150</NetAmount>
           </Attributes>
       </Document>

Or will I need to look at my flow and adjust it to accept the first example of the XML?

Thanks

 

[atlopes]

MancDad,

There are a few things yet unspecified, but this may be a starting point:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="[URL unfurl="true"]http://www.w3.org/1999/XSL/Transform"[/URL]
    xmlns:xs="[URL unfurl="true"]http://www.w3.org/2001/XMLSchema"[/URL]
    exclude-result-prefixes="xs"
    version="1.0">

    <xsl:output method="xml" indent="yes"/>

    <xsl:template match="/">
        <xsl:element name="TransformedDocuments">
            <xsl:apply-templates select="//Document"/>
        </xsl:element>
    </xsl:template>

    <xsl:template match="Document">
        <xsl:element name="Document">
            <xsl:element name="Attributes">
                <xsl:for-each select="Attributes/NamedDV">
                    <xsl:element name="{@Name}">
                        <xsl:value-of select="."/>
                    </xsl:element>
                </xsl:for-each>
            </xsl:element>
        </xsl:element>
    </xsl:template>

</xsl:stylesheet>

 

[mikrom]

Hi MancDad,

It should be not problem to write a little program which does this work.
You mentioned SharePoint, so I assumed you are on windows and wrote this little example in Vbscript:

mancdad.vbs

[COLOR=#804040][b]dim[/b][/color] xmlDoc[COLOR=#804040][b],[/b][/color] objNodeList

[COLOR=#0000ff]'set xmlDoc = CreateObject("MSXML2.DOMDocument")[/color]
[COLOR=#804040][b]set[/b][/color] xmlDoc [COLOR=#804040][b]=[/b][/color] [COLOR=#008080]CreateObject[/color][COLOR=#804040][b]([/b][/color][COLOR=#ff00ff]"Microsoft.XMLDOM"[/color][COLOR=#804040][b])[/b][/color]
xmlDoc[COLOR=#804040][b].[/b][/color][COLOR=#804040][b]load[/b][/color][COLOR=#804040][b]([/b][/color][COLOR=#ff00ff]"mancdad.xml"[/color][COLOR=#804040][b])[/b][/color]
[COLOR=#804040][b]set[/b][/color] objNodeList [COLOR=#804040][b]=[/b][/color] xmlDoc[COLOR=#804040][b].[/b][/color]getElementsByTagName[COLOR=#804040][b]([/b][/color][COLOR=#ff00ff]"NamedDV"[/color][COLOR=#804040][b])[/b][/color]

wscript[COLOR=#804040][b].[/b][/color]echo[COLOR=#804040][b]([/b][/color][COLOR=#ff00ff]"<Document>"[/color][COLOR=#804040][b])[/b][/color]
wscript[COLOR=#804040][b].[/b][/color]echo[COLOR=#804040][b]([/b][/color][COLOR=#ff00ff]vbTab[/color] [COLOR=#804040][b]+[/b][/color] [COLOR=#ff00ff]"<Attributes>"[/color][COLOR=#804040][b])[/b][/color]
[COLOR=#804040][b]for[/b][/color] [COLOR=#804040][b]each[/b][/color] named_dv [COLOR=#804040][b]in[/b][/color] objNodeList
  named_dv_name [COLOR=#804040][b]=[/b][/color] named_dv[COLOR=#804040][b].[/b][/color]getAttribute[COLOR=#804040][b]([/b][/color][COLOR=#ff00ff]"Name"[/color][COLOR=#804040][b])[/b][/color]
  [COLOR=#804040][b]for[/b][/color] [COLOR=#804040][b]each[/b][/color] child [COLOR=#804040][b]in[/b][/color] named_dv[COLOR=#804040][b].[/b][/color]ChildNodes
    [COLOR=#804040][b]if[/b][/color] child[COLOR=#804040][b].[/b][/color]NodeName [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]"Value"[/color] [COLOR=#804040][b]then[/b][/color]
      named_dv_value [COLOR=#804040][b]=[/b][/color] child[COLOR=#804040][b].[/b][/color]Text
    [COLOR=#804040][b]end[/b][/color] [COLOR=#804040][b]if[/b][/color]
  [COLOR=#804040][b]next[/b][/color]
 [COLOR=#0000ff] 'wscript.echo(named_dv_name)[/color]
 [COLOR=#0000ff] 'wscript.echo(named_dv_value)[/color]
  wscript[COLOR=#804040][b].[/b][/color]echo[COLOR=#804040][b]([/b][/color][COLOR=#ff00ff]vbTab[/color] [COLOR=#804040][b]+[/b][/color] [COLOR=#ff00ff]vbTab[/color] [COLOR=#804040][b]+[/b][/color] xml_element[COLOR=#804040][b]([/b][/color]named_dv_name[COLOR=#804040][b],[/b][/color] named_dv_value[COLOR=#804040][b]))[/b][/color]
[COLOR=#804040][b]next[/b][/color]
wscript[COLOR=#804040][b].[/b][/color]echo[COLOR=#804040][b]([/b][/color][COLOR=#ff00ff]vbTab[/color] [COLOR=#804040][b]+[/b][/color] [COLOR=#ff00ff]"</Attributes>"[/color][COLOR=#804040][b])[/b][/color]
wscript[COLOR=#804040][b].[/b][/color]echo[COLOR=#804040][b]([/b][/color][COLOR=#ff00ff]"</Document>"[/color][COLOR=#804040][b])[/b][/color]
[COLOR=#0000ff]'at end release XMLDOM object from memory[/color]
[COLOR=#804040][b]set[/b][/color] xml_doc [COLOR=#804040][b]=[/b][/color] [COLOR=#804040][b]nothing[/b][/color] 

[COLOR=#804040][b]function[/b][/color] xml_element[COLOR=#804040][b]([/b][/color]element_name[COLOR=#804040][b],[/b][/color] element_value[COLOR=#804040][b])[/b][/color]
  begin_element [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]"<"[/color] [COLOR=#804040][b]+[/b][/color] element_name [COLOR=#804040][b]+[/b][/color] [COLOR=#ff00ff]">"[/color]
  end_element [COLOR=#804040][b]=[/b][/color] [COLOR=#ff00ff]"</"[/color] [COLOR=#804040][b]+[/b][/color] element_name [COLOR=#804040][b]+[/b][/color] [COLOR=#ff00ff]">"[/color]
  xml_element [COLOR=#804040][b]=[/b][/color] begin_element [COLOR=#804040][b]+[/b][/color] element_value [COLOR=#804040][b]+[/b][/color] end_element
[COLOR=#804040][b]end[/b][/color] [COLOR=#804040][b]function[/b][/color]

Running the script on your input XML (I named it mancdad.xml) produces this output:

c:\mikrom\Work>cscript /NoLogo mancdad.vbs
<Document>
        <Attributes>
                <PIRRef>42103</PIRRef>
                <InvoiceDate>07/01/2010</InvoiceDate>
                <NetAmount>150</NetAmount>
        </Attributes>
</Document>

Using redirection to get a resulting xml file mancdad_result.xml:

c:\mikrom\Work>cscript /NoLogo mancdad.vbs > mancdad_result.xml