convert json to xml

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

Page 1 / 1

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.

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

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;

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

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;

eUpdate 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.

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;

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

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;

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

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;

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

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.

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

Reply

Community Stats

Reply

Community Stats

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