XML parsing to transform json using dataweave 2

Question

I am trying to parse an XML recursively to create JSON array using dataweave 2 but I am not able to do so.

My Input XML is given below -

<?xml version="1.0" encoding="utf-16"?>
<MessageParts xmlns="http://schemas.microsoft.com/dynamics/2011/01/documents/Message">
  <BillsOfMaterials xmlns="http://schemas.microsoft.com/dynamics/2008/01/documents/BillsOfMaterials">
    <SenderId>121</SenderId>
    <BOMVersion class="entity">
      <BOMId>BOM0012605</BOMId>
      <ItemId>9650084</ItemId>
      <ItemIdCommercial></ItemIdCommercial>
      <Name>SmartRip for Folding Carton</Name>
      <RecId>5637161103</RecId>
      <BOMTable class="entity">
        <BOMId>BOM0012605</BOMId>
        <Name>SmartRip for Folding Carton</Name>
        <RecId>5637160354</RecId>
        <BOM class="entity">
          <BOMId>BOM0012605</BOMId>
          <BOMQty>1.0000</BOMQty>
          <BOMType>Item</BOMType>
          <ItemId>9650092</ItemId>
          <RecId>5637307757</RecId>
          <UnitId>PCS</UnitId>
          <InventDimBOM class="entity">
            <RecId>5637213290</RecId>
          </InventDimBOM>
          <BOMTable>
            <BOMId>BOM0012613</BOMId>
            <Name>FlexRip Classic Intel kernel and tools</Name>
            <RecId>5637161355</RecId>
            <BOM>
              <BOMType>Item</BOMType>
              <ItemId>9650079</ItemId>
              <BOMQty>1.00</BOMQty>
              <UnitId>PCS</UnitId>
              <BOMId>BOM0012613</BOMId>
              <RecId>5637307799</RecId>
            </BOM>
            <BOM>
              <BOMType>Item</BOMType>
              <ItemId>9644919T</ItemId>
              <BOMQty>1.00</BOMQty>
              <UnitId>PCS</UnitId>
              <BOMId>BOM0012613</BOMId>
              <RecId>5637307800</RecId>
            </BOM>
            <BOM>
              <BOMType>Item</BOMType>
              <ItemId>9644920</ItemId>
              <BOMQty>1.00</BOMQty>
              <UnitId>PCS</UnitId>
              <BOMId>BOM0012613</BOMId>
              <RecId>5637307801</RecId>
            </BOM>
            <BOM>
              <BOMType>Item</BOMType>
              <ItemId>9700720Z</ItemId>
              <BOMQty>1.00</BOMQty>
              <UnitId>PCS</UnitId>
              <BOMId>BOM0012613</BOMId>
              <RecId>5637307802</RecId>
              <BOMTable>
                <BOMId>BOM012173</BOMId>
                <Name></Name>
                <RecId>5637157832</RecId>
                <BOM>
                  <BOMType>Item</BOMType>
                  <ItemId>9700595</ItemId>
                  <BOMQty>1.00</BOMQty>
                  <UnitId>PCS</UnitId>
                  <BOMId>BOM012173</BOMId>
                  <RecId>5637333626</RecId>
                </BOM>
              </BOMTable>
              <BOMTable>
                <BOMId>BOM012173</BOMId>
                <Name></Name>
                <RecId>5637157832</RecId>
                <BOM>
                  <BOMType>Item</BOMType>
                  <ItemId>9700595</ItemId>
                  <BOMQty>1.00</BOMQty>
                  <UnitId>PCS</UnitId>
                  <BOMId>BOM012173</BOMId>
                  <RecId>5637333626</RecId>
                </BOM>
                <BOM>
                  <BOMType>Item</BOMType>
                  <ItemId>9608224Z</ItemId>
                  <BOMQty>1.00</BOMQty>
                  <UnitId>PCS</UnitId>
                  <BOMId>BOM012173</BOMId>
                  <RecId>5637333627</RecId>
                </BOM>       
              </BOMTable>
            </BOM>
            <BOM>
              <BOMType>Item</BOMType>
              <ItemId>9683336L</ItemId>
              <BOMQty>1.00</BOMQty>
              <UnitId>PCS</UnitId>
              <BOMId>BOM0012613</BOMId>
              <RecId>5637307803</RecId>
              <BOMTable>
                <BOMId>BOM0012781</BOMId>
                <Name>Esko Suite 12.1 A5 Software box</Name>
                <RecId>5637165603</RecId>
                <BOM>
                  <BOMType>Item</BOMType>
                  <ItemId>G211249</ItemId>
                  <BOMQty>1.00</BOMQty>
                  <UnitId>PCS</UnitId>
                  <BOMId>BOM0012781</BOMId>
                  <RecId>5637309242</RecId>
                </BOM>
              </BOMTable>
              <BOMTable>
                <BOMId>BOM0012781</BOMId>
                <Name>Esko Suite 12.1 A5 Software box</Name>
                <RecId>5637165603</RecId>
                <BOM>
                  <BOMType>Item</BOMType>
                  <ItemId>G211249</ItemId>
                  <BOMQty>1.00</BOMQty>
                  <UnitId>PCS</UnitId>
                  <BOMId>BOM0012781</BOMId>
                  <RecId>5637309242</RecId>
                </BOM>
                <BOM>
                  <BOMType>Item</BOMType>
                  <ItemId>G25583961_24</ItemId>
                  <BOMQty>1.00</BOMQty>
                  <UnitId>PCS</UnitId>
                  <BOMId>BOM0012781</BOMId>
                  <RecId>5637309243</RecId>
                </BOM>
              </BOMTable>
            </BOM>
          </BOMTable>
        </BOM>
        <BOM class="entity">
          <BOMId>BOM0012605</BOMId>
          <BOMQty>1.0000</BOMQty>
          <BOMType>Item</BOMType>
          <ItemId>9608221Z</ItemId>
          <RecId>5637307758</RecId>
          <UnitId>PCS</UnitId>
          <InventDimBOM class="entity">
            <RecId>5637213290</RecId>
          </InventDimBOM>
        </BOM>
        <BOM class="entity">
          <BOMId>BOM0012605</BOMId>
          <BOMQty>1.0000</BOMQty>
          <BOMType>Item</BOMType>
          <ItemId>9700624Y</ItemId>
          <RecId>5637307759</RecId>
          <UnitId>PCS</UnitId>
          <InventDimBOM class="entity">
            <RecId>5637213291</RecId>
          </InventDimBOM>
          <BOMTable>
            <BOMId>BOM012172</BOMId>
            <Name></Name>
            <RecId>5637157831</RecId>
            <BOM>
              <BOMType>Item</BOMType>
              <ItemId>9644921</ItemId>
              <BOMQty>1.00</BOMQty>
              <UnitId>PCS</UnitId>
              <BOMId>BOM012172</BOMId>
              <RecId>5637333624</RecId>
            </BOM>
          </BOMTable>
        </BOM>
        <BOM class="entity">
          <BOMId>BOM0012605</BOMId>
          <BOMQty>1.0000</BOMQty>
          <BOMType>Item</BOMType>
          <ItemId>9700815</ItemId>
          <RecId>5637307760</RecId>
          <UnitId>PCS</UnitId>
          <InventDimBOM class="entity">
            <RecId>5637213290</RecId>
          </InventDimBOM>
        </BOM>
      </BOMTable>
      <InventDim class="entity">
        <RecId>5637199988</RecId>
      </InventDim>
    </BOMVersion>
  </BillsOfMaterials>
</MessageParts>

I want to create JSON array of all the BOMs (a flat structure with no hierarchy) with parentId as RecId of parent BOM as shown below -

Output -

[
    {
        "id": "5637307757",
        "productId": "9650092",                                                                                                                                 
        "parentId": null
    },
    {
        "id": "5637307799",
        "productId": "9650079",
        "parentId": "5637307757"
    },
    {
        "id": "5637307800",
        "productId": "9644919T",
        "parentId": "5637307757"
    },
    {
        "id": "5637307801",
        "productId": "96AR060W",
        "parentId": "5637307757"
    },
    
    {
        "id": "5637307802",
        "productId": "9700720Z",
        "parentId": "5637307757"
    },
    {
        "id": "5637333626",
        "productId": "9700595",
        "parentId": "5637307802"
    },  
    
    {
        "id": "5637333626",
        "productId": "9700595",
        "parentId": "5637307802"
    },
    {
        "id": "5637333627",
        "productId": "9608224Z",
        "parentId": "5637307802"
    },  
    {
        "id": "5637307803",
        "productId": "9683336L",
        "parentId": "5637307757"
    },
    {
        "id": "5637309242",
        "productId": "G211249",
        "parentId": "5637307803"
    },
    {
        "id": "5637309242",
        "productId": "G211249",
        "parentId": "5637307803"
    },
    {
        "id": "5637309243",
        "productId": "G25583961_24",
        "parentId": "5637307803"
    }
]

Mapping Rules -

id : BOM -> RecId
productId : BOM -> ItemId
parentId : the RecId of the parent BOM

The difference with the suggested answer is that I need to set parentid with the immediate recid of the BOM and not of the BOM table.

"I am not able to do so" is not a problem description... What does the quoted code do? Why is that wrong or insufficient? Explain clearly. — underscore_d
– underscore_d, Commented Sep 29, 2020 at 13:53
Does this answer your question? Recursively parse XML to create JSON array using dataweave 2 — aled
– aled, Commented Sep 29, 2020 at 14:51
Hi Aled, no that does not answer the the question, the issue with this that I need to assign RecId of the parent BOM to the child BOM. — user12445682
– user12445682, Commented Sep 29, 2020 at 15:26
Hi underscore_d, I need to do transformation from given Input to given output using DWL 2. The problem I am facing is to implement recursion to parse XML. — user12445682
– user12445682, Commented Sep 30, 2020 at 9:59
%dw 2.0 output application/json fun getLines(bom, parent) = if (bom.BOMTable.BOM?) { "id": bom.RecId, "productId": bom.ItemId, "parent": parent } ++ getLines(bom.BOMTable.BOM, bom.RecId) else { "id": bom.RecId, "productId": bom.ItemId, "parent": parent } --- payload.MessageParts.BillsOfMaterials.BOMVersion.BOMTable.*BOM map (val) -> { (getLines(val,null)) } — user12445682
– user12445682, Commented Sep 30, 2020 at 14:42

oim · Accepted Answer · 2020-10-01 19:39:39Z

Seems like your output is still wrong given the input (for example, product id 96AR060W is not in the input xml). To answer your question is instead of collecting all BOM object, you can traverse individual BOM objects to save parent id and create a function that you can call recursively. Please see below dataweave:

%dw 2.0
output application/json

fun parseBOM(bomData, parentId) = 
    if (bomData.BOMTable.BOM?) 
        [{
            id: bomData.RecId,
            productId: bomData.ItemId,
            parentId: parentId  
        }] ++ (bomData.BOMTable.*BOM flatMap parseBOM($, bomData.RecId))
    else {
        id: bomData.RecId,
        productId: bomData.ItemId,
        parentId: parentId
    }

---
using (firstBOMTable =  payload.MessageParts.BillsOfMaterials.BOMVersion.BOMTable)
firstBOMTable.*BOM flatMap parseBOM($, null)

Given your input xml, this will result to

[
  {
    "id": "5637307757",
    "productId": "9650092",
    "parentId": null
  },
  {
    "id": "5637307799",
    "productId": "9650079",
    "parentId": "5637307757"
  },
  {
    "id": "5637307800",
    "productId": "9644919T",
    "parentId": "5637307757"
  },
  {
    "id": "5637307801",
    "productId": "9644920",
    "parentId": "5637307757"
  },
  {
    "id": "5637307802",
    "productId": "9700720Z",
    "parentId": "5637307757"
  },
  {
    "id": "5637333626",
    "productId": "9700595",
    "parentId": "5637307802"
  },
  {
    "id": "5637333627",
    "productId": "9608224Z",
    "parentId": "5637307802"
  },
  {
    "id": "5637307803",
    "productId": "9683336L",
    "parentId": "5637307757"
  },
  {
    "id": "5637309242",
    "productId": "G211249",
    "parentId": "5637307803"
  },
  {
    "id": "5637309243",
    "productId": "G25583961_24",
    "parentId": "5637307803"
  },
  {
    "id": "5637307758",
    "productId": "9608221Z",
    "parentId": null
  },
  {
    "id": "5637307759",
    "productId": "9700624Y",
    "parentId": null
  },
  {
    "id": "5637333624",
    "productId": "9644921",
    "parentId": "5637307759"
  },
  {
    "id": "5637307760",
    "productId": "9700815",
    "parentId": null
  }
]

What the function do is that if the provided object (bomData) contains BOMTable.BOM then create the needed object and call the same function again using all BOM objects under it. The function as well accepts the parentId as a parameter to preserve it.

Collectives™ on Stack Overflow

XML parsing to transform json using dataweave 2

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related