# Mapping XML to JSON

**URL:** <https://community.cdata.com/t/mapping-xml-to-json/625>\
**Category:** CData Arc\
**Tags:** question, json\
**Created:** [February 9, 2024, 5:13pm UTC](https://community.cdata.com/t/mapping-xml-to-json/625 "2024-02-09T17:13:30Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![russell-jerseypost](https://avatars.discourse-cdn.com/v4/letter/r/439d5e/32.png) [@russell-jerseypost](https://community.cdata.com/u/russell-jerseypost)\
**Post date:** [February 9, 2024, 5:13pm UTC](https://community.cdata.com/t/mapping-xml-to-json/625/1 "2024-02-09T17:13:30Z")

</div>

Hi

I’m trying to convert this file to JSON.

```xml
<Items xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">	<Sheet1> <Country_code>AC</Country_code> <field_1 xsi:nil="true" /> <field_2 xsi:nil="true" /> <Year>2013</Year> <field_4 xsi:nil="true" /> <Month>1</Month> <field_6 xsi:nil="true" /> <Days>1</Days> <Description>New Year's Day</Description>	</Sheet1>	<Sheet1> <Country_code>AC</Country_code> <field_1 xsi:nil="true" /> <field_2 xsi:nil="true" /> <Year>2013</Year> <field_4 xsi:nil="true" /> <Month>3</Month> <field_6 xsi:nil="true" /> <Days>29</Days> <Description>Good Friday</Description>	</Sheet1>	<Sheet1> <Country_code>AC</Country_code> <field_1 xsi:nil="true" /> <field_2 xsi:nil="true" /> <Year>2013</Year> <field_4 xsi:nil="true" /> <Month>4</Month> <field_6 xsi:nil="true" /> <Days>1</Days> <Description>Easter Monday</Description>	</Sheet1>...</Items>

```

to JSON. I’d like the JSON to be like this

```json
[{ "date": "2024-02-08", "localName": "Prešernov dan", "name": "Prešeren Day", "countryCode": "SI", "fixed": true, "global": true, "counties": null, "launchYear": null, "types": [ "Public"] }, { "date": "2024-02-09", "localName": "설날", "name": "Lunar New Year", "countryCode": "KR", "fixed": false, "global": true, "counties": null, "launchYear": null, "types": ["Public"] }, { "date": "2024-02-10", "localName": "春节", "name": "Chinese New Year (Spring Festival)", "countryCode": "CN", "fixed": false, "global": true, "counties": null, "launchYear": null, "types": ["Public"] },...]

```

I am using an XMLMap to produce

```xml
<?xml version="1.0" encoding="UTF-8"?><top xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <public_holiday> <date>2024-02-15</date> <localName>Defeat of former USSR in Afghanistan</localName> <name>Defeat of former USSR in Afghanistan</name> <countryCode>AF</countryCode> <fixed>true</fixed> <global>true</global> <counties></counties> <launchYear></launchYear> <types json:array="true">Public</types> </public_holiday> <public_holiday> <date>2024-03-11</date> <localName>First day of Ramadan holy month</localName> <name>First day of Ramadan holy month</name> <countryCode>AF</countryCode> <fixed>true</fixed> <global>true</global> <counties></counties> <launchYear></launchYear> <types json:array="true">Public</types> </public_holiday>...</top>

```

And then a JSON Connector with

![fc19a1e6-57ac-432a-8d40-464ed6cf800c.png](https://us1.discourse-cdn.com/cdata/original/2X/1/1cfa8ffc51290bcbf25df5b68e328718e8ae7be8.png)

But The closest I can get is

```json
{ "public_holiday": [{ "date": "2024-02-15", "localName": "Defeat of former USSR in Afghanistan", "name": "Defeat of former USSR in Afghanistan", "countryCode": "AF", "fixed": "true", "global": "true", "counties": "", "launchYear": "", "types": [ "Public"] }, { "date": "2024-03-11", "localName": "First day of Ramadan holy month", "name": "First day of Ramadan holy month", "countryCode": "AF", "fixed": "true", "global": "true", "counties": "", "launchYear": "", "types": ["Public"] }, ... ]}

```

Any tips on how I get rid of { “public\_holiday”: ….. } ? I basically want to just return the whole array **value** of the public\_holiday property

---

<div class="post-metadata">

**Author:** ![James\_B](https://sea3.discourse-cdn.com/cdata/user_avatar/community.cdata.com/james_b/32/1688_2.png) [@James\_B](https://community.cdata.com/u/James_B)\
**Post date:** [February 9, 2024, 8:47pm UTC](https://community.cdata.com/t/mapping-xml-to-json/625/2 "2024-02-09T20:47:35Z")

</div>

There’s a trick that I recommend if you’re trying to map to a specific format in the JSON connector. First line up two JSON connectors in a row:

 ![80d866d0-dc7a-479c-8180-7d0ff7759c26.png](https://us1.discourse-cdn.com/cdata/original/2X/2/2f70c39acec4d1fcfb24e3f0b68105b7b773e618.png)

Take the JSON that you want to map to, and pass it through both connectors; if you get the same value out at the end:

 ![e3e0de06-e47b-447d-82f3-52eae03a4468.png](https://us1.discourse-cdn.com/cdata/original/2X/9/91eaa333224b47f1a01979325041bcb1b1592044.png)

you’ve hit upon the reversible XML model for the JSON connector, and you can map to that (and if they’re different (aside from minifying), please let us know because that may need to be addressed.

From that, the middle XML step becomes this format:

```xml
<?xml version="1.0" encoding="utf-8" ?><Items json:array="true" xmlns:json="http://arc.cdata.com/ns/jsonconnector"> <Item> <date>2024-02-08</date> <localName>Prešernov dan</localName> <name>Prešeren Day</name> <countryCode>SI</countryCode> <fixed json:type="boolean">true</fixed> <global json:type="boolean">true</global> <counties xsi:nil="true" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" /> <launchYear xsi:nil="true" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" /> <types json:array="true">Public</types> </Item>...</Items>

```

**Item** here can be used as the default unnamed item in the JSON root. Does this help?