×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Change XML tags/fields across entire file

thread426-1811737

Change XML tags/fields across entire file

Change XML tags/fields across entire file

(OP)
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:

CODE --> xml

<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:

CODE --> xml

<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

RE: Change XML tags/fields across entire file

MancDad,

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

CODE --> XSLT

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    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>

RE: Change XML tags/fields across entire file

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

CODE

dim xmlDoc, objNodeList

'set xmlDoc = CreateObject("MSXML2.DOMDocument")
set xmlDoc = CreateObject("Microsoft.XMLDOM")
xmlDoc.load("mancdad.xml")
set objNodeList = xmlDoc.getElementsByTagName("NamedDV")

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

function xml_element(element_name, element_value)
  begin_element = "<" + element_name + ">"
  end_element = "</" + element_name + ">"
  xml_element = begin_element + element_value + end_element
end function

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

CODE

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:

CODE

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close