From one XML into another XML

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!

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

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>