Solved

convert json to xml

8 years ago
February 15, 2017
8 replies
204 views

witos
45 replies

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>

View original

Did this help you find an answer to your question?

+50

redgeographics
Celebrity
3642 replies
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.

witos
Author
45 replies
8 years ago
February 15, 2017

redgeographics wrote:

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

takashi
7703 replies
Best Answer
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>

witos
Author
45 replies
8 years ago
February 16, 2017

takashi wrote:

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.

takashi
7703 replies
8 years ago
February 16, 2017

takashi wrote:

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")}

takashi
7703 replies
8 years ago
February 16, 2017

takashi wrote:

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>

witos
Author
45 replies
8 years ago
February 16, 2017

takashi wrote:

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?

takashi
7703 replies
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]

Reply

Rich Text Editor, editor1

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

Cookie settings

We use 3 different kinds of cookies. You can choose which cookies you want to accept. We need basic cookies to make this site work, therefore these are the minimum you can select. Learn more about our cookies.

Basic
Functional

Normal
Functional + analytics

Complete
Functional + analytics + social media + embedded videos + marketing

convert json to xml

8 replies

Reply

Helpful Members This Week

Recently Solved Questions

How to restart a REST Server in ArcGIS Server?

Remove last CR/LF from a CSV

1019 error with change detector and polygons

Where is the "Show Bookmark Navigator" option in FME 2024.2?

How to dynamically write new or update existing ArcGIS Online Feature Layers.

Community Stats

Latest FME

Cookie policy

Cookie settings

Reply

Related Topics

Razer cortex fps overlay not workingicon

Razer Cortex: In-Game Overlay not working

Razer Cortex Overlay not workingicon

Razer Cortex Overlay Issuesicon

In-Game Overlay won't work some games

Helpful Members This Week

Recently Solved Questions

How to restart a REST Server in ArcGIS Server?

Remove last CR/LF from a CSV

1019 error with change detector and polygons

Where is the "Show Bookmark Navigator" option in FME 2024.2?

How to dynamically write new or update existing ArcGIS Online Feature Layers.

Popular Tags

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

Cookie policy

Cookie settings