Insert XML expressions into a working XML string using Modified JavaScript Value step in PDI

Question

I'm building a XML string for a SOAP request in Pentaho Data Integration using a Modified JavaScript Value step. The request must execute for each row (order) and each row will include data of a variable number of items. I need to bring the variable code for the items (in XML format) into the order row.

My goal is for the XML output to look like this:

<BO>
<Level1A_Header>
  <rowHeader>
    <Field1>Value</Field1>
    <Field2>Value</Field2>
    ...
  </rowHeader>
</Level1A_Header>
<Level1B_Items>
  <rowItem>
    <ItemCode>Product1</ItemCode>
    <Quantity>1</Quantity>
  </rowItem>
  <rowItem>
    <ItemCode>Product2</ItemCode>
    <Quantity>1</Quantity>
  </rowItem>
  ...
</Level1B_Items>
</BO>
;

Which I'll later use for the SOAP request (one request per order/row).

My transformation starts with data on orders and items:

order	item	qty
Order 1	Product 1	1
Order 1	Product 2	1
Order 2	Product 1	2

In a Modified JavaScript Value step, I build this line for each product:

var line = new XML();
line = <row>
<ItemCode>{item}</ItemCode>
<Quantity>{qty}</Quantity>
</row>;
lineXml = line.toXMLString();

Then I group by order and concatenate the lineXml values (separated by nothing) so the output may look like:

<ItemCode>Product 1</ItemCode><Quantity>1</Quantity></row><ItemCode>Product 2</ItemCode><Quantity>1</Quantity></row>

So from that point on, I have one row per order (good), and one of its columns contains the XML line with the order's items (variable xml_lines).

Next comes the Modified JavaScript Value step where I try to use the Items data as follows:

var body = new XML();
body = <BO>
<Level1A_Header>
  <rowHeader>
    <Field1>{variable1}</Field1>
    <Field2>{variable2}</Field2>
    ...
  </rowHeader>
</Level1A_Header>
<Level1B_Items>
  {xml_lines}
</Level1B_Items>
</BO>
;

And here's the issue, the output is not in the right format because the step transforms the text of the "<" / ">" tags in the xml_lines value, into "<" and ">" text. So the output right now looks like this:

<BO>
<Level1A_Header>
  <rowHeader>
    <Field1>{variable1}</Field1>
    <Field2>{variable2}</Field2>
    ...
  </rowHeader>
</Level1A_Header>
<Level1B_Items>
  &lt;rowItem&gt;
  &lt;ItemCode&gt;Product 1&lt;/ItemCode&gt;
  &lt;Quantity&gt;1&lt;/Quantity&gt;
&lt;/row&gt;&lt;row&gt;
  &lt;ItemCode&gt;Product 2&lt;/ItemCode&gt;
  &lt;Quantity&gt;2&lt;/Quantity&gt;
&lt;/rowItem&gt;
</Level1B_Items>
</BO>

So my question is, how could I transform the Items XML string into actual XML code without the "<>" tags/characters switching to code?

I've looked at several examples in the documentation but none do quite exactly this. Some use "XML Join" steps and others like it, but since I've used before the Modified JavaScript Value to build XML requests, I wanted to first check if this is a feasible way of achieving the goal.

Answer 1

Using xmlbuider2. It makes it easy to build your XML creation. The detailed documentation is here.

Demo code save it with create-xml.js

const { create } = require('xmlbuilder2');

const headers = [
    {
        field: 1,
        value: "Value"
    },
    {
        field: 1,
        value: "Value"
    }
]

const items = [
    {
        order: "Order 1",
        item: "Product1",
        qty: 1
    },
    {
        order: "Order 1",
        item: "Product2",
        qty: 1
    },
    {
        order: "Order 2",
        item: "Product1",
        qty: 2
    }
]

const root = create({ version: '1.0' }).ele('BO');
const header = root.ele('Level1A_Header');
const rowHeader = header.ele('rowHeader');
headers.forEach((header) => {
    const field = rowHeader.ele(`Field${header.field}`).txt(`${header.value}`)
});

const level1B_items = root.ele('Level1B_Items')
items.forEach((item) => {
    const rowItem = level1B_items.ele('rowItem')
    rowItem.ele('ItemCode').txt(`${item.item}`)
    rowItem.ele('Quantity').txt(`${item.qty}`)
});

const xml = root.end({ prettyPrint: true });
console.log(xml)

Install dependency

npm install xmlbuilder2

Run it

node create-xml.js

Result

<?xml version="1.0"?>
<BO>
  <Level1A_Header>
    <rowHeader>
      <Field1>Value</Field1>
      <Field1>Value</Field1>
    </rowHeader>
  </Level1A_Header>
  <Level1B_Items>
    <rowItem>
      <ItemCode>Product1</ItemCode>
      <Quantity>1</Quantity>
    </rowItem>
    <rowItem>
      <ItemCode>Product2</ItemCode>
      <Quantity>1</Quantity>
    </rowItem>
    <rowItem>
      <ItemCode>Product1</ItemCode>
      <Quantity>2</Quantity>
    </rowItem>
  </Level1B_Items>
</BO>