[D] How to create complex XML structures? (hop)

["dave-csc (via GitHub)" <gi...@apache.org>]

GitHub user dave-csc created a discussion: How to create complex XML structures?

Hello,

I'm trying to create a series of XML files based on the contents of a table, in which some fields define how to group those contents in a single file (i.e. the same values in those fields imply that the other fields must be written in the same file).

I first used an **Add XML** transform to generate the XML fragment for the single rows, this step ran without issues.

What I haven't figured how to do is grouping those fragments by the "key" fields: I tried by concatenating them with a **Group by** transform, and then wrapping the joined tags with another **Add XML**, but in the resulting XML all the inner tags characters are replaced by `<` and `>`

I also gave a try to the **XML Join** transform, in this case you can somehow merge correctly the fragments, but only _all_ of them in a single row/file: you can't group them and create different files.

Is there another way to proceed?

Thanks for your support :)

GitHub link: https://github.com/apache/hop/discussions/5395

----
This is an automatically sent email for users@hop.apache.org.
To unsubscribe, please send an email to: users-unsubscribe@hop.apache.org

Re: [D] How to create complex XML structures? (hop)

["dave-csc (via GitHub)" <gi...@apache.org>]

GitHub user dave-csc added a comment to the discussion: How to create complex XML structures?

For those looking for a very clumsy and complex workaround, here's how to do it. You first need a sub-pipeline with this layout:
![immagine](https://github.com/user-attachments/assets/415c6e9a-5587-4631-b8ea-d9340150c5f4)
- **Get rows from result** contains the stream in the parent pipeline with the XML fragments for the single rows
- **Get variables** needs to read two parameters passed from the parent: the `GROUP_ID` and the `XML_TEMPLATE`
- **XML Join** should be configured as follow:
  - _Target XML transforms_ = Get variables
  - _Target XML field_ = the field in which you read `XML_TEMPLATE`
  - _Source XML transform_ = Get rows from result
  - _Source XML field_ = ideally, you should pass it through a variable in order to make the pipeline re-usable. In practice, this doesn't work and you need to specify the field name as it is passed from the parent pipeline (see #5396)
  - _XPath statement_ = this field also should be passed from above and doesn't work (same #5396). So specify directly the "merge point" in XPath here
  - _Result XML field_ = give a name of your choice, e.g. `xml_result`

The parent pipeline should process the data stream as follows:
- use a **Sort rows** to order data by the grouping keys you need
- link an output to an **Add XML** to generate the XML for each single row
- link another output from Sort rows to a **Group by** transform, and specify only the grouping fields. Then link this to an **Add sequence** in order to give each group a distinct number (give it a name, e.g. `group_id`)
- link Add XML and Add sequence to a **Merge join**, using the same grouping keys as merging keys
- link this Merge join to the **Pipeline Executor** of the sub-pipeline above, configure it as follows:
  - _Parameters_: pass the field `group_id` to `GROUP_ID`, and the `XML_TEMPLATE` (as a constant String, or from another transform before, etc.)
  - _Row grouping_: remove any value in _Number of rows to send to pipeline_ and specify _Field to group rows on_ = `group_id`
- link the _result rows_ output of Pipeline Executor, and the output of Add sequence above to another **Merge join**: this is needed to reconcile the original grouped data with the generated XML (the XML Join above strips any field that is not in the Get variables output)

Done... at the second Merge Join's output you should have the grouping keys and the XML related to them!

GitHub link: https://github.com/apache/hop/discussions/5395#discussioncomment-13410277

----
This is an automatically sent email for users@hop.apache.org.
To unsubscribe, please send an email to: users-unsubscribe@hop.apache.org