From one XML into another XML

TimCon · January 24, 2023, 11:11am

We get XML files with orderinformation which we would like to import into our ERP system, but it seems the import module can’t handle tags with attributes. I would like to script a script (?) that simplifies it and leaves out a lot of information that is in the sourcefile.

This is a snippet of just one article from the source XML:

  	<docArticleItem aItemNo="2" aAction="C" aUUID="cb2f7cc0-9044-4856-9645-c4f54896db81">
  		<vOrderItemNumber>0001.1</vOrderItemNumber>
  		<vVendorArticleNo aStatus="C">DEX-168-HVS-..-M25</vVendorArticleNo>
  		<vOrderQuantity>1.000</vOrderQuantity>
  		<vOrderUnit>C62</vOrderUnit>
  		<itmOrgData>
  			<vOrgDataType>DIV</vOrgDataType>
  			<vOrgDataValue>UserArticle</vOrgDataValue>
  		</itmOrgData>
  		<itmOrgData>
  			<vOrgDataType>POS</vOrgDataType>
  			<vOrgDataValue>0001.1</vOrgDataValue>
  		</itmOrgData>
  		<itmOrgData>
  			<vOrgDataType>ITM</vOrgDataType>
  			<vOrgDataValue>DEX-168-HVS-..-M25</vOrgDataValue>
  		</itmOrgData>
  		<itmText>
  			<vTextType>ARTS</vTextType>
  			<vTextLanguage>nl</vTextLanguage>
  			<vTextContent aLineFormat="\" aTextLineNo="1">Verstelbaar bureau d.m.v. slinger</vTextContent>
  		</itmText>
  		<itmText>
  			<vTextType>ARTL</vTextType>
  			<vTextLanguage>nl</vTextLanguage>
  			<vTextContent aLineFormat="\" aTextLineNo="1">Afmetingen: 160 x 80 cm</vTextContent>
  			<vTextContent aLineFormat="\" aTextLineNo="2">Framekleur: zwart</vTextContent>
  			<vTextContent aLineFormat="\" aTextLineNo="3">Bladkleur: Lindberg Eiken (T560)</vTextContent>
  		</itmText>
  		<itmPricing aCondNo="1">
  			<vConditionType aCondArea="OP">SNET</vConditionType>
  			<vConditionValue>0.00</vConditionValue>
  			<vCondCurrency>XXX</vCondCurrency>
  		</itmPricing>
  		<itmPricing aCondNo="2">
  			<vConditionType aCondArea="P">SGRO</vConditionType>
  			<vConditionValue>0.00</vConditionValue>
  			<vCondCurrency>EUR</vCondCurrency>
  		</itmPricing>
  		<itmPricing aCondNo="3">
  			<vConditionType aCondArea="P">TGRO</vConditionType>
  			<vConditionValue>0.00</vConditionValue>
  			<vCondCurrency>EUR</vCondCurrency>
  		</itmPricing>
  		<itmPricing aCondNo="4">
  			<vConditionType aCondArea="OS">SNET</vConditionType>
  			<vConditionValue>0.00</vConditionValue>
  			<vCondCurrency>XXX</vCondCurrency>
  		</itmPricing>
  		<itmPricing aCondNo="5">
  			<vConditionType aCondArea="S">SGRO</vConditionType>
  			<vConditionValue>390.00</vConditionValue>
  			<vCondCurrency>EUR</vCondCurrency>
  		</itmPricing>
  		<itmPricing aCondNo="6">
  			<vConditionType aCondArea="S">TGRO</vConditionType>
  			<vConditionValue>390.00</vConditionValue>
  			<vCondCurrency>EUR</vCondCurrency>
  		</itmPricing>
  		<itmPricing aCondNo="7">
  			<vConditionType aCondArea="S">SNET</vConditionType>
  			<vConditionValue>362.73</vConditionValue>
  			<vCondCurrency>EUR</vCondCurrency>
  		</itmPricing>
  		<itmPricing aCondNo="8">
  			<vConditionType aCondArea="S">TNET</vConditionType>
  			<vConditionValue>362.73</vConditionValue>
  			<vCondCurrency>EUR</vCondCurrency>
  		</itmPricing>
  		<itmPricing aCondNo="9">
  			<vConditionType aCondArea="S">TTAX</vConditionType>
  			<vConditionValue>76.17</vConditionValue>
  			<vConditionRate>21.00</vConditionRate>
  			<vCondCurrency>EUR</vCondCurrency>
  			<vConditionText>BTW</vConditionText>
  		</itmPricing>
  	</docArticleItem>

I would like to simplify this to someting like this:

  <Article>
  	<VendorArtNo>DEX-168-HVS-..-M25</VendorArtNo>
  	<OrderQuantity>1.000</OrderQuantity>
  	<ArtDescrShort>Verstelbaar bureau d.m.v. slinger</ArtDescrShort>
  	<ArtDescrLong>
  		Afmetingen: 160 x 80 cm</vTextContent>
  		Framekleur: zwart</vTextContent>
  		Bladkleur: Lindberg Eiken
  		</ArtDescrLong>
  	<ArtPricePerUnit>390.00</ArtPricePerUnit>
  	<ArtPricePerSubTotal>390.00</ArtPricePerSubTotal>
  	<ArtPurchasePricePerUnit>362.73</ArtPricePerUnit>
  	<ArtPurchasePricePerSubTotal>362.73</ArtPricePerSubTotal>
  </Article>

Is this a feasible task for PowerShell? I have some experience with PS but not much with XML.
Maybe there are better tools? I open for suggestions.

Thanks!

rob-simmers · January 24, 2023, 8:01pm

XML is a painful method to store and get data. There are some old APIs and systems that leverage XML, but what is the purpose of “simplifying” a structured XML into another XML? Getting data out is straightforward with dot notation:

[xml]$xml = @"
<docArticleItem aItemNo="2" aAction="C" aUUID="cb2f7cc0-9044-4856-9645-c4f54896db81">
    <vOrderItemNumber>0001.1</vOrderItemNumber>
    <vVendorArticleNo aStatus="C">DEX-168-HVS-..-M25</vVendorArticleNo>
    <vOrderQuantity>1.000</vOrderQuantity>
    <vOrderUnit>C62</vOrderUnit>
    <itmOrgData>
        <vOrgDataType>DIV</vOrgDataType>
        <vOrgDataValue>UserArticle</vOrgDataValue>
    </itmOrgData>
    <itmOrgData>
        <vOrgDataType>POS</vOrgDataType>
        <vOrgDataValue>0001.1</vOrgDataValue>
    </itmOrgData>
    <itmOrgData>
        <vOrgDataType>ITM</vOrgDataType>
        <vOrgDataValue>DEX-168-HVS-..-M25</vOrgDataValue>
    </itmOrgData>
    <itmText>
        <vTextType>ARTS</vTextType>
        <vTextLanguage>nl</vTextLanguage>
        <vTextContent aLineFormat="\" aTextLineNo="1">Verstelbaar bureau d.m.v. slinger</vTextContent>
    </itmText>
    <itmText>
        <vTextType>ARTL</vTextType>
        <vTextLanguage>nl</vTextLanguage>
        <vTextContent aLineFormat="\" aTextLineNo="1">Afmetingen: 160 x 80 cm</vTextContent>
        <vTextContent aLineFormat="\" aTextLineNo="2">Framekleur: zwart</vTextContent>
        <vTextContent aLineFormat="\" aTextLineNo="3">Bladkleur: Lindberg Eiken (T560)</vTextContent>
    </itmText>
    <itmPricing aCondNo="1">
        <vConditionType aCondArea="OP">SNET</vConditionType>
        <vConditionValue>0.00</vConditionValue>
        <vCondCurrency>XXX</vCondCurrency>
    </itmPricing>
    <itmPricing aCondNo="2">
        <vConditionType aCondArea="P">SGRO</vConditionType>
        <vConditionValue>0.00</vConditionValue>
        <vCondCurrency>EUR</vCondCurrency>
    </itmPricing>
    <itmPricing aCondNo="3">
        <vConditionType aCondArea="P">TGRO</vConditionType>
        <vConditionValue>0.00</vConditionValue>
        <vCondCurrency>EUR</vCondCurrency>
    </itmPricing>
    <itmPricing aCondNo="4">
        <vConditionType aCondArea="OS">SNET</vConditionType>
        <vConditionValue>0.00</vConditionValue>
        <vCondCurrency>XXX</vCondCurrency>
    </itmPricing>
    <itmPricing aCondNo="5">
        <vConditionType aCondArea="S">SGRO</vConditionType>
        <vConditionValue>390.00</vConditionValue>
        <vCondCurrency>EUR</vCondCurrency>
    </itmPricing>
    <itmPricing aCondNo="6">
        <vConditionType aCondArea="S">TGRO</vConditionType>
        <vConditionValue>390.00</vConditionValue>
        <vCondCurrency>EUR</vCondCurrency>
    </itmPricing>
    <itmPricing aCondNo="7">
        <vConditionType aCondArea="S">SNET</vConditionType>
        <vConditionValue>362.73</vConditionValue>
        <vCondCurrency>EUR</vCondCurrency>
    </itmPricing>
    <itmPricing aCondNo="8">
        <vConditionType aCondArea="S">TNET</vConditionType>
        <vConditionValue>362.73</vConditionValue>
        <vCondCurrency>EUR</vCondCurrency>
    </itmPricing>
    <itmPricing aCondNo="9">
        <vConditionType aCondArea="S">TTAX</vConditionType>
        <vConditionValue>76.17</vConditionValue>
        <vConditionRate>21.00</vConditionRate>
        <vCondCurrency>EUR</vCondCurrency>
        <vConditionText>BTW</vConditionText>
    </itmPricing>
</docArticleItem>
"@

$xml.docArticleItem.aItemNo

Writing data back is comparable to a DOM object manipulation:

Updating and Writing XML Files with PowerShell – Virtual Engine Ltd

TimCon · January 25, 2023, 9:08am

Thanks for your reply!

I thought XML is THE way to transfer data between systems, as long as no API is in place. But that really is beyond my knowledge.

That is simple: the software we use to import the XML is not capable of understanding atrributes in the XML file. We need a flat XML with nodes, but without attributes, that can be imported in a 2 dimensional table.

For example:

<itmPricing aCondNo="5">
    <vConditionType aCondArea="S">SGRO</vConditionType>
    <vConditionValue>390.00</vConditionValue>
    <vCondCurrency>EUR</vCondCurrency>
</itmPricing>

Represents the salesprice, where

<itmPricing aCondNo="7">
    <vConditionType aCondArea="S">SNET</vConditionType>
    <vConditionValue>362.73</vConditionValue>
    <vCondCurrency>EUR</vCondCurrency>
</itmPricing>

represents the purchase price. They belong to the same article (record), but the current software puts it on the next record in a plain table. That’s why I want to output a “simplified” version of the XML, with the attributes translated to their own nodes. and only those we really need to import.

Like this for example:

<itmPricingSGRO>390.00<itmPricingSGRO>
<itmPricingSNET>362.73<itmPricingSNET>

TimCon · February 15, 2023, 1:58pm

bump

I think If I get some directions on how to approach this, I would be able to educate myself enough to be able to copy/paste a script to our liking. But if (preferably a Dutch speaking) person can help me with this we are quite willing to award the effort.

Topic		Replies	Views
Need help importing data from XML file PowerShell Help	2	177	June 14, 2023
Parsing XML data using import-clixml cmdlet PowerShell Help	8	180	May 31, 2016
XML PowerShell Help	1	125	August 28, 2016
XML parsing PowerShell Help	4	301	January 11, 2017
I need help with readin from an XML file PowerShell Help	0	129	December 31, 2011

From one XML into another XML

Related Topics