Solved

convert json to xml

Forum|Forum|8 years ago
February 15, 2017
8 replies
212 views

witos

Hello,

I execute query on my postgresdb to get result as a json: description is here http://rextester.com/ZULI42144

It looks nice but now I would like to convert it to xml. I decided to use fme to query postgres just like in example and now I have noe idea how to convert the result to xml and write it to file.

Also talking about json which I also need (I need result in JSON and XML format) what I achieved is that I simply executed postgres query end then wrote results using simple text writer so I suppose that fme doesnt really know that the query result is a JSON :)

Structure and content of the database is as described here:http://rextester.com/ZULI42144

and there is sql that is used in below sqlExecutor

This is prototype of my workspace that I have right now and the question is how to format nice xml from this list exploder (if its possible).

And here is output of the logs. Of course in the result xml I would like to have structure ike <person>...<car>...<wheel>...</wheel></car></person>

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Feature Type: `Logger_LOGGED'

Attribute(64 bit integer) : `cars.carid' has value `1'

Attribute(encoded: utf-8) : `cars.comment' has value `nice car'

Attribute(encoded: utf-8) : `cars.type' has value `Toyota'

Attribute(64 bit integer) : `cars.wheels{0}.serial number' has value `11'

Attribute(encoded: utf-8) : `cars.wheels{0}.which' has value `front'

Attribute(64 bit integer) : `cars.wheels{1}.serial number' has value `12'

Attribute(encoded: utf-8) : `cars.wheels{1}.which' has value `back'

Attribute(string) : `fme_geometry' has value `fme_undefined'

Attribute(entangled: string): `fme_type' has value `fme_no_geom'

entangled to [postgres_type]

Attribute(encoded: utf-8) : `person_name' has value `Johny'

Attribute(string) : `postgres_type' has value `postgres_none'

Coordinate System: `'

Geometry Type: IFMENull

===========================================================================

Logger: Feature is:

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Feature Type: `Logger_LOGGED'

Attribute(64 bit integer) : `cars.carid' has value `2'

Attribute(encoded: utf-8) : `cars.comment' has value `nice car'

Attribute(encoded: utf-8) : `cars.type' has value `Fiat'

Attribute(64 bit integer) : `cars.wheels{0}.serial number' has value `21'

Attribute(encoded: utf-8) : `cars.wheels{0}.which' has value `front'

Attribute(64 bit integer) : `cars.wheels{1}.serial number' has value `22'

Attribute(encoded: utf-8) : `cars.wheels{1}.which' has value `back'

Attribute(string) : `fme_geometry' has value `fme_undefined'

Attribute(entangled: string): `fme_type' has value `fme_no_geom'

entangled to [postgres_type]

Attribute(encoded: utf-8) : `person_name' has value `Freddy'

Attribute(string) : `postgres_type' has value `postgres_none'

Coordinate System: `'

Geometry Type: IFMENull

===========================================================================

Logger: Feature is:

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Feature Type: `Logger_LOGGED'

Attribute(string) : `fme_geometry' has value `fme_undefined'

Attribute(entangled: string): `fme_type' has value `fme_no_geom'

entangled to [postgres_type]

Attribute(encoded: utf-8) : `person_name' has value `Billy'

Attribute(string) : `postgres_type' has value `postgres_none'

Coordinate System: `'

Geometry Type: IFMENull

===========================================================================

Thanks for any hints

Best answer by takashi

Hi @witos, I would use the JSONFragmenter to fragment and flatten the JSON doc, and then use the XMLTemplater to construct an XML document.

JSON Attribute: persons
JSON Query: json["persons"][*]
Flatten Query Result into Attributes: Yes
Recursively Flatten Objects/Arrays : Yes

Here, you will have to design the destination XML schema and then set appropriate XML template expression(s) for the XMLtemplater. There is no way to generate XML schema (template) automatically from a JSON document unfortunately.

BTW, why not use the PostgreSQL XML functions? For example, execute this SQL script on an existing database with a SQLCreator,

drop table if exists wheel;
drop table if exists car;
drop table if exists person;

create table person
(
  id integer not null,
  name character varying(30),
  constraint person_pk primary key (id)
);
create table car
(
  id integer not null,
  type character varying(30),
  personid integer,
  constraint car_pk primary key (id)
);
create table wheel
(
  id integer not null,
  whichone character varying(30),
  serialnumber integer,
  carid integer,
  constraint "wheel_pk" primary key (id)
);

insert into person(id, name) values 
    (1, 'Johny'), 
    (2, 'Freddy'),
    (3, 'Billy');
insert into car(id, type, personid) values 
    (1, 'Toyota', 1),
    (2, 'Fiat', 2);
insert into wheel(id, whichone, serialnumber, carid) values 
    (1, 'front', '11', 1),
    (2, 'back', '12', 1),
    (3, 'front', '21', 2),
    (4, 'back', '22', 2),
    (5, 'front', '3', 3);
    
select
    xmlelement(name persons,
        xmlagg(
            xmlelement(name person,
                xmlattributes(p.id as personid),
                xmlelement(name name, p.name),
                xmlelement(name cars,
                    (select
                        xmlagg(
                            xmlelement(name car,
                                xmlattributes(c.id as carid),
                                xmlelement(name type, c.type),
                                xmlelement(name comment, 'nice car'), --constant
                                xmlelement(name wheels,
                                    (select
                                        xmlagg(
                                            xmlelement(name wheel,
                                                xmlattributes(w.id as wheelid),
                                                xmlforest(whichone, serialnumber)
                                            )
                                        ) from wheel as w where w.carid = c.id
                                    )
                                )
                            )
                        ) from car as c where c.personid = p.id
                    )
                )
            )
        )
    ) as _xml_output
from person as p;

[Update 2017-02-17: This SELECT statement could be more efficient than the above one]

select
    xmlelement(name persons,
        xmlagg(
            xmlelement(name person, xmlattributes(p.id as personid),
                xmlelement(name name, p.name),
                case
                    when c.cars is not null then c.cars
                    else xmlelement(name cars)
                end
            )
        )
    ) _xml_output
from person as p
left outer join (select
        x.personid,
        xmlelement(name cars,
            xmlagg(
                xmlelement(name car, xmlattributes(x.id as carid),
                    xmlelement(name type, x.type),
                    xmlelement(name comment, 'nice car'),
                    y.wheels
                )
            )
        ) as cars
    from car as x
    left outer join (select
            carid,
            xmlelement(name wheels,
                xmlagg(
                    xmlelement(name wheel, xmlattributes(id as wheelid),
                        xmlforest(whichone, serialnumber)
                    )
                )
            ) as wheels
        from wheel group by carid) as y on y.carid = x.id
    group by personid) as c on c.personid = p.id
;

and then format and clean up the resulting XML document with an XMLFormatter.

Result:

<?xml version="1.0" encoding="UTF-8"?>
<persons>
	<person personid="1">
		<name>Johny</name>
		<cars>
			<car carid="1">
				<type>Toyota</type>
				<comment>nice car</comment>
				<wheels>
					<wheel wheelid="1">
						<whichone>front</whichone>
						<serialnumber>11</serialnumber>
					</wheel>
					<wheel wheelid="2">
						<whichone>back</whichone>
						<serialnumber>12</serialnumber>
					</wheel>
				</wheels>
			</car>
		</cars>
	</person>
	<person personid="2">
		<name>Freddy</name>
		<cars>
			<car carid="2">
				<type>Fiat</type>
				<comment>nice car</comment>
				<wheels>
					<wheel wheelid="3">
						<whichone>front</whichone>
						<serialnumber>21</serialnumber>
					</wheel>
					<wheel wheelid="4">
						<whichone>back</whichone>
						<serialnumber>22</serialnumber>
					</wheel>
				</wheels>
			</car>
		</cars>
	</person>
	<person personid="3">
		<name>Billy</name>
		<cars/>
	</person>
</persons>

This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

+62

redgeographics
Celebrity
Forum|Forum|8 years ago
February 15, 2017

If you use the JSON reader it will recognize objects in the JSON as such and will make it easier to write it to XML.

Alternatively, you can use a SQLCreator to pass the query directly to your database and get the result back as FME features, that way you don't have to worry about the JSON part at all.

FME rocks! \m/

Upvote

witos
Author
Forum|Forum|8 years ago
February 15, 2017

If you use the JSON reader it will recognize objects in the JSON as such and will make it easier to write it to XML.

Alternatively, you can use a SQLCreator to pass the query directly to your database and get the result back as FME features, that way you don't have to worry about the JSON part at all.

I updated my question so that it may be more clear what I actually need

Upvote

takashi
Best Answer
Forum|Forum|8 years ago
February 16, 2017

Hi @witos, I would use the JSONFragmenter to fragment and flatten the JSON doc, and then use the XMLTemplater to construct an XML document.

JSON Attribute: persons
JSON Query: json["persons"][*]
Flatten Query Result into Attributes: Yes
Recursively Flatten Objects/Arrays : Yes

BTW, why not use the PostgreSQL XML functions? For example, execute this SQL script on an existing database with a SQLCreator,

drop table if exists wheel;
drop table if exists car;
drop table if exists person;

create table person
(
  id integer not null,
  name character varying(30),
  constraint person_pk primary key (id)
);
create table car
(
  id integer not null,
  type character varying(30),
  personid integer,
  constraint car_pk primary key (id)
);
create table wheel
(
  id integer not null,
  whichone character varying(30),
  serialnumber integer,
  carid integer,
  constraint "wheel_pk" primary key (id)
);

insert into person(id, name) values 
    (1, 'Johny'), 
    (2, 'Freddy'),
    (3, 'Billy');
insert into car(id, type, personid) values 
    (1, 'Toyota', 1),
    (2, 'Fiat', 2);
insert into wheel(id, whichone, serialnumber, carid) values 
    (1, 'front', '11', 1),
    (2, 'back', '12', 1),
    (3, 'front', '21', 2),
    (4, 'back', '22', 2),
    (5, 'front', '3', 3);
    
select
    xmlelement(name persons,
        xmlagg(
            xmlelement(name person,
                xmlattributes(p.id as personid),
                xmlelement(name name, p.name),
                xmlelement(name cars,
                    (select
                        xmlagg(
                            xmlelement(name car,
                                xmlattributes(c.id as carid),
                                xmlelement(name type, c.type),
                                xmlelement(name comment, 'nice car'), --constant
                                xmlelement(name wheels,
                                    (select
                                        xmlagg(
                                            xmlelement(name wheel,
                                                xmlattributes(w.id as wheelid),
                                                xmlforest(whichone, serialnumber)
                                            )
                                        ) from wheel as w where w.carid = c.id
                                    )
                                )
                            )
                        ) from car as c where c.personid = p.id
                    )
                )
            )
        )
    ) as _xml_output
from person as p;

[Update 2017-02-17: This SELECT statement could be more efficient than the above one]

select
    xmlelement(name persons,
        xmlagg(
            xmlelement(name person, xmlattributes(p.id as personid),
                xmlelement(name name, p.name),
                case
                    when c.cars is not null then c.cars
                    else xmlelement(name cars)
                end
            )
        )
    ) _xml_output
from person as p
left outer join (select
        x.personid,
        xmlelement(name cars,
            xmlagg(
                xmlelement(name car, xmlattributes(x.id as carid),
                    xmlelement(name type, x.type),
                    xmlelement(name comment, 'nice car'),
                    y.wheels
                )
            )
        ) as cars
    from car as x
    left outer join (select
            carid,
            xmlelement(name wheels,
                xmlagg(
                    xmlelement(name wheel, xmlattributes(id as wheelid),
                        xmlforest(whichone, serialnumber)
                    )
                )
            ) as wheels
        from wheel group by carid) as y on y.carid = x.id
    group by personid) as c on c.personid = p.id
;

and then format and clean up the resulting XML document with an XMLFormatter.

Result:

<?xml version="1.0" encoding="UTF-8"?>
<persons>
	<person personid="1">
		<name>Johny</name>
		<cars>
			<car carid="1">
				<type>Toyota</type>
				<comment>nice car</comment>
				<wheels>
					<wheel wheelid="1">
						<whichone>front</whichone>
						<serialnumber>11</serialnumber>
					</wheel>
					<wheel wheelid="2">
						<whichone>back</whichone>
						<serialnumber>12</serialnumber>
					</wheel>
				</wheels>
			</car>
		</cars>
	</person>
	<person personid="2">
		<name>Freddy</name>
		<cars>
			<car carid="2">
				<type>Fiat</type>
				<comment>nice car</comment>
				<wheels>
					<wheel wheelid="3">
						<whichone>front</whichone>
						<serialnumber>21</serialnumber>
					</wheel>
					<wheel wheelid="4">
						<whichone>back</whichone>
						<serialnumber>22</serialnumber>
					</wheel>
				</wheels>
			</car>
		</cars>
	</person>
	<person personid="3">
		<name>Billy</name>
		<cars/>
	</person>
</persons>

Why not inspect features with Visual/Data Preview and Feature/Record Information before writing them into a destination dataset?

Upvote

witos
Author
Forum|Forum|8 years ago
February 16, 2017

Hi @witos, I would use the JSONFragmenter to fragment and flatten the JSON doc, and then use the XMLTemplater to construct an XML document.

JSON Attribute: persons
JSON Query: json["persons"][*]
Flatten Query Result into Attributes: Yes
Recursively Flatten Objects/Arrays : Yes

BTW, why not use the PostgreSQL XML functions? For example, execute this SQL script on an existing database with a SQLCreator,

drop table if exists wheel;
drop table if exists car;
drop table if exists person;

create table person
(
  id integer not null,
  name character varying(30),
  constraint person_pk primary key (id)
);
create table car
(
  id integer not null,
  type character varying(30),
  personid integer,
  constraint car_pk primary key (id)
);
create table wheel
(
  id integer not null,
  whichone character varying(30),
  serialnumber integer,
  carid integer,
  constraint "wheel_pk" primary key (id)
);

insert into person(id, name) values 
    (1, 'Johny'), 
    (2, 'Freddy'),
    (3, 'Billy');
insert into car(id, type, personid) values 
    (1, 'Toyota', 1),
    (2, 'Fiat', 2);
insert into wheel(id, whichone, serialnumber, carid) values 
    (1, 'front', '11', 1),
    (2, 'back', '12', 1),
    (3, 'front', '21', 2),
    (4, 'back', '22', 2),
    (5, 'front', '3', 3);
    
select
    xmlelement(name persons,
        xmlagg(
            xmlelement(name person,
                xmlattributes(p.id as personid),
                xmlelement(name name, p.name),
                xmlelement(name cars,
                    (select
                        xmlagg(
                            xmlelement(name car,
                                xmlattributes(c.id as carid),
                                xmlelement(name type, c.type),
                                xmlelement(name comment, 'nice car'), --constant
                                xmlelement(name wheels,
                                    (select
                                        xmlagg(
                                            xmlelement(name wheel,
                                                xmlattributes(w.id as wheelid),
                                                xmlforest(whichone, serialnumber)
                                            )
                                        ) from wheel as w where w.carid = c.id
                                    )
                                )
                            )
                        ) from car as c where c.personid = p.id
                    )
                )
            )
        )
    ) as _xml_output
from person as p;

[Update 2017-02-17: This SELECT statement could be more efficient than the above one]

select
    xmlelement(name persons,
        xmlagg(
            xmlelement(name person, xmlattributes(p.id as personid),
                xmlelement(name name, p.name),
                case
                    when c.cars is not null then c.cars
                    else xmlelement(name cars)
                end
            )
        )
    ) _xml_output
from person as p
left outer join (select
        x.personid,
        xmlelement(name cars,
            xmlagg(
                xmlelement(name car, xmlattributes(x.id as carid),
                    xmlelement(name type, x.type),
                    xmlelement(name comment, 'nice car'),
                    y.wheels
                )
            )
        ) as cars
    from car as x
    left outer join (select
            carid,
            xmlelement(name wheels,
                xmlagg(
                    xmlelement(name wheel, xmlattributes(id as wheelid),
                        xmlforest(whichone, serialnumber)
                    )
                )
            ) as wheels
        from wheel group by carid) as y on y.carid = x.id
    group by personid) as c on c.personid = p.id
;

and then format and clean up the resulting XML document with an XMLFormatter.

Result:

<?xml version="1.0" encoding="UTF-8"?>
<persons>
	<person personid="1">
		<name>Johny</name>
		<cars>
			<car carid="1">
				<type>Toyota</type>
				<comment>nice car</comment>
				<wheels>
					<wheel wheelid="1">
						<whichone>front</whichone>
						<serialnumber>11</serialnumber>
					</wheel>
					<wheel wheelid="2">
						<whichone>back</whichone>
						<serialnumber>12</serialnumber>
					</wheel>
				</wheels>
			</car>
		</cars>
	</person>
	<person personid="2">
		<name>Freddy</name>
		<cars>
			<car carid="2">
				<type>Fiat</type>
				<comment>nice car</comment>
				<wheels>
					<wheel wheelid="3">
						<whichone>front</whichone>
						<serialnumber>21</serialnumber>
					</wheel>
					<wheel wheelid="4">
						<whichone>back</whichone>
						<serialnumber>22</serialnumber>
					</wheel>
				</wheels>
			</car>
		</cars>
	</person>
	<person personid="3">
		<name>Billy</name>
		<cars/>
	</person>
</persons>

Thanks for this great explanation. I completely focused on solution with fme json to xml conversion that I forgot about built in xml features in postgres.

Upvote

takashi
Forum|Forum|8 years ago
February 16, 2017

Hi @witos, I would use the JSONFragmenter to fragment and flatten the JSON doc, and then use the XMLTemplater to construct an XML document.

JSON Attribute: persons
JSON Query: json["persons"][*]
Flatten Query Result into Attributes: Yes
Recursively Flatten Objects/Arrays : Yes

BTW, why not use the PostgreSQL XML functions? For example, execute this SQL script on an existing database with a SQLCreator,

drop table if exists wheel;
drop table if exists car;
drop table if exists person;

create table person
(
  id integer not null,
  name character varying(30),
  constraint person_pk primary key (id)
);
create table car
(
  id integer not null,
  type character varying(30),
  personid integer,
  constraint car_pk primary key (id)
);
create table wheel
(
  id integer not null,
  whichone character varying(30),
  serialnumber integer,
  carid integer,
  constraint "wheel_pk" primary key (id)
);

insert into person(id, name) values 
    (1, 'Johny'), 
    (2, 'Freddy'),
    (3, 'Billy');
insert into car(id, type, personid) values 
    (1, 'Toyota', 1),
    (2, 'Fiat', 2);
insert into wheel(id, whichone, serialnumber, carid) values 
    (1, 'front', '11', 1),
    (2, 'back', '12', 1),
    (3, 'front', '21', 2),
    (4, 'back', '22', 2),
    (5, 'front', '3', 3);
    
select
    xmlelement(name persons,
        xmlagg(
            xmlelement(name person,
                xmlattributes(p.id as personid),
                xmlelement(name name, p.name),
                xmlelement(name cars,
                    (select
                        xmlagg(
                            xmlelement(name car,
                                xmlattributes(c.id as carid),
                                xmlelement(name type, c.type),
                                xmlelement(name comment, 'nice car'), --constant
                                xmlelement(name wheels,
                                    (select
                                        xmlagg(
                                            xmlelement(name wheel,
                                                xmlattributes(w.id as wheelid),
                                                xmlforest(whichone, serialnumber)
                                            )
                                        ) from wheel as w where w.carid = c.id
                                    )
                                )
                            )
                        ) from car as c where c.personid = p.id
                    )
                )
            )
        )
    ) as _xml_output
from person as p;

[Update 2017-02-17: This SELECT statement could be more efficient than the above one]

select
    xmlelement(name persons,
        xmlagg(
            xmlelement(name person, xmlattributes(p.id as personid),
                xmlelement(name name, p.name),
                case
                    when c.cars is not null then c.cars
                    else xmlelement(name cars)
                end
            )
        )
    ) _xml_output
from person as p
left outer join (select
        x.personid,
        xmlelement(name cars,
            xmlagg(
                xmlelement(name car, xmlattributes(x.id as carid),
                    xmlelement(name type, x.type),
                    xmlelement(name comment, 'nice car'),
                    y.wheels
                )
            )
        ) as cars
    from car as x
    left outer join (select
            carid,
            xmlelement(name wheels,
                xmlagg(
                    xmlelement(name wheel, xmlattributes(id as wheelid),
                        xmlforest(whichone, serialnumber)
                    )
                )
            ) as wheels
        from wheel group by carid) as y on y.carid = x.id
    group by personid) as c on c.personid = p.id
;

and then format and clean up the resulting XML document with an XMLFormatter.

Result:

<?xml version="1.0" encoding="UTF-8"?>
<persons>
	<person personid="1">
		<name>Johny</name>
		<cars>
			<car carid="1">
				<type>Toyota</type>
				<comment>nice car</comment>
				<wheels>
					<wheel wheelid="1">
						<whichone>front</whichone>
						<serialnumber>11</serialnumber>
					</wheel>
					<wheel wheelid="2">
						<whichone>back</whichone>
						<serialnumber>12</serialnumber>
					</wheel>
				</wheels>
			</car>
		</cars>
	</person>
	<person personid="2">
		<name>Freddy</name>
		<cars>
			<car carid="2">
				<type>Fiat</type>
				<comment>nice car</comment>
				<wheels>
					<wheel wheelid="3">
						<whichone>front</whichone>
						<serialnumber>21</serialnumber>
					</wheel>
					<wheel wheelid="4">
						<whichone>back</whichone>
						<serialnumber>22</serialnumber>
					</wheel>
				</wheels>
			</car>
		</cars>
	</person>
	<person personid="3">
		<name>Billy</name>
		<cars/>
	</person>
</persons>

As my own exercises, I tested conversion from JSON to XML using the XMLTemplater.

Basic

XMLTemplater_1: ROOT Expression

<person>
    <name>{fme:get-attribute("person_name")}</name>
{
    if (fme:has-attribute("cars.carid"))
    then
        <cars>
            <car carid="{fme:get-attribute("cars.carid")}">
                <type>{fme:get-attribute("cars.type")}</type>
                <comment>{fme:get-attribute("cars.comment")}</comment>
                <wheels>{fme:process-features("SUB")}</wheels>
            </car>
        </cars>
    else
        <cars/>
}
</person>

XMLTemplater_1: SUB Expression

<wheel>
    <which>{fme:get-attribute("which")}</which>
    <serialNumber>{fme:get-attribute("serial number")}</serialNumber>
</wheel>

XMLTemplater_2: ROOT Expression

<persons>{fme:process-features("SUB")}</persons>

XMLTemplater_2: SUB Expression

{fme:get-xml-attribute("_result")}

Why not inspect features with Visual/Data Preview and Feature/Record Information before writing them into a destination dataset?

Upvote

takashi
Forum|Forum|8 years ago
February 16, 2017

Hi @witos, I would use the JSONFragmenter to fragment and flatten the JSON doc, and then use the XMLTemplater to construct an XML document.

JSON Attribute: persons
JSON Query: json["persons"][*]
Flatten Query Result into Attributes: Yes
Recursively Flatten Objects/Arrays : Yes

BTW, why not use the PostgreSQL XML functions? For example, execute this SQL script on an existing database with a SQLCreator,

drop table if exists wheel;
drop table if exists car;
drop table if exists person;

create table person
(
  id integer not null,
  name character varying(30),
  constraint person_pk primary key (id)
);
create table car
(
  id integer not null,
  type character varying(30),
  personid integer,
  constraint car_pk primary key (id)
);
create table wheel
(
  id integer not null,
  whichone character varying(30),
  serialnumber integer,
  carid integer,
  constraint "wheel_pk" primary key (id)
);

insert into person(id, name) values 
    (1, 'Johny'), 
    (2, 'Freddy'),
    (3, 'Billy');
insert into car(id, type, personid) values 
    (1, 'Toyota', 1),
    (2, 'Fiat', 2);
insert into wheel(id, whichone, serialnumber, carid) values 
    (1, 'front', '11', 1),
    (2, 'back', '12', 1),
    (3, 'front', '21', 2),
    (4, 'back', '22', 2),
    (5, 'front', '3', 3);
    
select
    xmlelement(name persons,
        xmlagg(
            xmlelement(name person,
                xmlattributes(p.id as personid),
                xmlelement(name name, p.name),
                xmlelement(name cars,
                    (select
                        xmlagg(
                            xmlelement(name car,
                                xmlattributes(c.id as carid),
                                xmlelement(name type, c.type),
                                xmlelement(name comment, 'nice car'), --constant
                                xmlelement(name wheels,
                                    (select
                                        xmlagg(
                                            xmlelement(name wheel,
                                                xmlattributes(w.id as wheelid),
                                                xmlforest(whichone, serialnumber)
                                            )
                                        ) from wheel as w where w.carid = c.id
                                    )
                                )
                            )
                        ) from car as c where c.personid = p.id
                    )
                )
            )
        )
    ) as _xml_output
from person as p;

[Update 2017-02-17: This SELECT statement could be more efficient than the above one]

select
    xmlelement(name persons,
        xmlagg(
            xmlelement(name person, xmlattributes(p.id as personid),
                xmlelement(name name, p.name),
                case
                    when c.cars is not null then c.cars
                    else xmlelement(name cars)
                end
            )
        )
    ) _xml_output
from person as p
left outer join (select
        x.personid,
        xmlelement(name cars,
            xmlagg(
                xmlelement(name car, xmlattributes(x.id as carid),
                    xmlelement(name type, x.type),
                    xmlelement(name comment, 'nice car'),
                    y.wheels
                )
            )
        ) as cars
    from car as x
    left outer join (select
            carid,
            xmlelement(name wheels,
                xmlagg(
                    xmlelement(name wheel, xmlattributes(id as wheelid),
                        xmlforest(whichone, serialnumber)
                    )
                )
            ) as wheels
        from wheel group by carid) as y on y.carid = x.id
    group by personid) as c on c.personid = p.id
;

and then format and clean up the resulting XML document with an XMLFormatter.

Result:

<?xml version="1.0" encoding="UTF-8"?>
<persons>
	<person personid="1">
		<name>Johny</name>
		<cars>
			<car carid="1">
				<type>Toyota</type>
				<comment>nice car</comment>
				<wheels>
					<wheel wheelid="1">
						<whichone>front</whichone>
						<serialnumber>11</serialnumber>
					</wheel>
					<wheel wheelid="2">
						<whichone>back</whichone>
						<serialnumber>12</serialnumber>
					</wheel>
				</wheels>
			</car>
		</cars>
	</person>
	<person personid="2">
		<name>Freddy</name>
		<cars>
			<car carid="2">
				<type>Fiat</type>
				<comment>nice car</comment>
				<wheels>
					<wheel wheelid="3">
						<whichone>front</whichone>
						<serialnumber>21</serialnumber>
					</wheel>
					<wheel wheelid="4">
						<whichone>back</whichone>
						<serialnumber>22</serialnumber>
					</wheel>
				</wheels>
			</car>
		</cars>
	</person>
	<person personid="3">
		<name>Billy</name>
		<cars/>
	</person>
</persons>

Advanced

XMLTemplater: ROOT Expression

<persons>{fme:process-features("SUB")}</persons>

XMLTemplater: SUB Expression

<person>
    <name>{fme:get-attribute("person_name")}</name>
{
    if (fme:has-attribute("cars.carid"))
    then
        <cars>
            <car carid="{fme:get-attribute("cars.carid")}">
                <type>{fme:get-attribute("cars.type")}</type>
                <comment>{fme:get-attribute("cars.comment")}</comment>
                <wheels>
        {
            for $i in (0 to xs:integer(fme:get-attribute("_element_count"))-1)
            let $w := fme:get-attribute(fn:concat("cars.wheels{", $i, "}.which"))
            let $s := fme:get-attribute(fn:concat("cars.wheels{", $i, "}.serial number"))
            return <wheel>
                       <which>{$w}</which>
                       <serialNumber>{$s}</serialNumber>
                   </wheel>
        }
                </wheels>
            </car>
        </cars>
    else
        <cars />
}
</person>

Why not inspect features with Visual/Data Preview and Feature/Record Information before writing them into a destination dataset?

Upvote

witos
Author
Forum|Forum|8 years ago
February 16, 2017

Hi @witos, I would use the JSONFragmenter to fragment and flatten the JSON doc, and then use the XMLTemplater to construct an XML document.

JSON Attribute: persons
JSON Query: json["persons"][*]
Flatten Query Result into Attributes: Yes
Recursively Flatten Objects/Arrays : Yes

BTW, why not use the PostgreSQL XML functions? For example, execute this SQL script on an existing database with a SQLCreator,

drop table if exists wheel;
drop table if exists car;
drop table if exists person;

create table person
(
  id integer not null,
  name character varying(30),
  constraint person_pk primary key (id)
);
create table car
(
  id integer not null,
  type character varying(30),
  personid integer,
  constraint car_pk primary key (id)
);
create table wheel
(
  id integer not null,
  whichone character varying(30),
  serialnumber integer,
  carid integer,
  constraint "wheel_pk" primary key (id)
);

insert into person(id, name) values 
    (1, 'Johny'), 
    (2, 'Freddy'),
    (3, 'Billy');
insert into car(id, type, personid) values 
    (1, 'Toyota', 1),
    (2, 'Fiat', 2);
insert into wheel(id, whichone, serialnumber, carid) values 
    (1, 'front', '11', 1),
    (2, 'back', '12', 1),
    (3, 'front', '21', 2),
    (4, 'back', '22', 2),
    (5, 'front', '3', 3);
    
select
    xmlelement(name persons,
        xmlagg(
            xmlelement(name person,
                xmlattributes(p.id as personid),
                xmlelement(name name, p.name),
                xmlelement(name cars,
                    (select
                        xmlagg(
                            xmlelement(name car,
                                xmlattributes(c.id as carid),
                                xmlelement(name type, c.type),
                                xmlelement(name comment, 'nice car'), --constant
                                xmlelement(name wheels,
                                    (select
                                        xmlagg(
                                            xmlelement(name wheel,
                                                xmlattributes(w.id as wheelid),
                                                xmlforest(whichone, serialnumber)
                                            )
                                        ) from wheel as w where w.carid = c.id
                                    )
                                )
                            )
                        ) from car as c where c.personid = p.id
                    )
                )
            )
        )
    ) as _xml_output
from person as p;

[Update 2017-02-17: This SELECT statement could be more efficient than the above one]

select
    xmlelement(name persons,
        xmlagg(
            xmlelement(name person, xmlattributes(p.id as personid),
                xmlelement(name name, p.name),
                case
                    when c.cars is not null then c.cars
                    else xmlelement(name cars)
                end
            )
        )
    ) _xml_output
from person as p
left outer join (select
        x.personid,
        xmlelement(name cars,
            xmlagg(
                xmlelement(name car, xmlattributes(x.id as carid),
                    xmlelement(name type, x.type),
                    xmlelement(name comment, 'nice car'),
                    y.wheels
                )
            )
        ) as cars
    from car as x
    left outer join (select
            carid,
            xmlelement(name wheels,
                xmlagg(
                    xmlelement(name wheel, xmlattributes(id as wheelid),
                        xmlforest(whichone, serialnumber)
                    )
                )
            ) as wheels
        from wheel group by carid) as y on y.carid = x.id
    group by personid) as c on c.personid = p.id
;

and then format and clean up the resulting XML document with an XMLFormatter.

Result:

<?xml version="1.0" encoding="UTF-8"?>
<persons>
	<person personid="1">
		<name>Johny</name>
		<cars>
			<car carid="1">
				<type>Toyota</type>
				<comment>nice car</comment>
				<wheels>
					<wheel wheelid="1">
						<whichone>front</whichone>
						<serialnumber>11</serialnumber>
					</wheel>
					<wheel wheelid="2">
						<whichone>back</whichone>
						<serialnumber>12</serialnumber>
					</wheel>
				</wheels>
			</car>
		</cars>
	</person>
	<person personid="2">
		<name>Freddy</name>
		<cars>
			<car carid="2">
				<type>Fiat</type>
				<comment>nice car</comment>
				<wheels>
					<wheel wheelid="3">
						<whichone>front</whichone>
						<serialnumber>21</serialnumber>
					</wheel>
					<wheel wheelid="4">
						<whichone>back</whichone>
						<serialnumber>22</serialnumber>
					</wheel>
				</wheels>
			</car>
		</cars>
	</person>
	<person personid="3">
		<name>Billy</name>
		<cars/>
	</person>
</persons>

I tried to generate xml directly from sql query but it takes like 1k times longer then generating JSON. Did you know about this performance issue regarding xmlelement and xmlagg compared to json_build_object?

Upvote

takashi
Forum|Forum|8 years ago
February 17, 2017

I don't know exact reason why the SQL query with XML functions took longer time than the query with JSON functions, but the SQL statement with XML functions has nested SELECT statement, so the number of sub query executions could be very large if the number of "person" records was large. I think it's one of possible reasons.

If so, this statement may be better than the previous one.

[Moved 2017-02-17: See the update in my first post]

Why not inspect features with Visual/Data Preview and Feature/Record Information before writing them into a destination dataset?

Upvote

convert json to xml

8 replies

Community Stats

Latest FME

Community Stats

Latest FME

Sign up

An FME Account is required to contribute

Login to the community

An FME Account is required to contribute

Scanning file for viruses.

This file cannot be downloaded