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>
<client_id>1</client_id>
<client_name>steve</client_name>
<contacts>
<contact>
<contact_id>1</contact_id>
<contact_type>phone</contact_type>
<contact_value>01234 567890</contact_value>
</contact>
<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).
<client>
<body>
<root>
<client>
<client_id>1</client_id>
<client_name>steve</client_name>
<contacts>
<contact>
<contact_id>2</contact_id>
<contact_type>email</contact_type>
<contact_value>steve@testmail.com</contact_value>
</contact>
</contacts>
</client>
</root>
</body>
</client>
