XML Map - multiple sub-element values to one main ... - Qlik Community

welshsteve · ‎2023-03-03

Hi. I'm completely new to XML mapping and need some help if possible.

I have two sets of data from a relationsional database which I need to join together in one XML file.

2 tables:

"client"

client_id | client_name

1 | steve

2 | joe

"contact"

contact_id | clientid | contact_type | contact_value

1 | 1 | phone | 01234 456789

2 | 1 | email | steve@testmail.com

3 | 2 | phone | 01111 222222

4 | 2 | email | joe@randomemail.com

So to join these together can either be done in one SQL statement

SELECT

client.client_id, client.client_name, contact_id, contact_type, contact_value

FROM client INNER JOIN contact ON client.client_id = contact.client_id

Or it can be done via two iDBInput components into a tMAP.

I need to build an xml that looks like this for each client.

<client_id>1</client_id>

<client_name>steve</client_name>

<contact_id>1</contact_id>

<contact_type>phone</contact_type>

<contact_value>01234 567890</contact_value>

</contact>

<contact_id>2</contact_id>

<contact_type>email</contact_type>

<contact_value>steve@testmail.com</contact_value>

</contact>

</contacts>

</client>

Can anybody help as I am struggling to get this to work. My attempt is just returning one <contact> element per client. It appears to be putting the last contact row for each client inside the contact element, but I need multip <contact> elements within the <contacts> element (one for each contact record against a client).

<body>

<root>

<client_id>1</client_id>

<client_name>steve</client_name>

<contact_id>2</contact_id>

<contact_type>email</contact_type>

<contact_value>steve@testmail.com</contact_value>

</contact>

</contacts>

</client>

</root>

</body>

</client>

welshsteve · ‎2023-03-03

This is my tXMLMap component

XML Map - multiple sub-element values to one main element

Studio

v8.x