Solved

Mapping XML to JSON

  • 9 February 2024
  • 1 reply
  • 52 views
R
Userlevel 5
Badge +1

Hi

I’m trying to convert this file to JSON.

<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

[
    {
        "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 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

 

But The closest I can get is

{
  "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

 

icon

Best answer by James B 9 February 2024, 21:47

View original

1 reply

James B
Userlevel 6
Badge

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:

 

 

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:

 

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 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?

Reply


Virtual Event: Data Virtualization, Reimagined Ma”arch 14, 2024 style=
Terms and ConditionsCookie settings