Oracle : Générez du XML directement dans vos SELECT

J’ai eu, pendant mon stage, à extraire des données depuis un schéma avec 80 tables vers des fichiers XML. Et je peux vous l’assurer, quand on a l’habitude de travailler sous MySQL avec une dixaine de tables au maximum, ça fait un grand changement.

Bref, aujourd’hui je vais vous apprendre à générer du XML directement via vos SELECT.

Ne vous attendez pas à pouvoir reproduire ça avec MySQL, il ne sait pas faire. Cet article est à destination des utilisateurs ORACLE à partir de la 10GR2.

XMLELEMENT

XMLELEMENT permet de générer une balise XML.

Cette fonction s’utilise de la façon suivante :

SELECT
	XMLELEMENT(
		NAME "car"
		[, complements]
	)
FROM cars c;
Résultat
<car></car>
<car></car>
<car></car>
<car></car>

[complements] peut être un appel à XMLATTRIBUTES ou XMLFOREST, qui sont défini ci-dessous, ou bien une sous requête pour générer des balises filles, par exemple la liste des pièces d’un voiture.

XMLATTRIBUTES

La fonction XMLATTRIBUTES permet d’ajouter des attributs à une balise. Elle s’utilise donc à l’intérieur de la fonction XMLELEMENT :

SELECT
	XMLELEMENT(
		NAME "car",
		XMLATTRIBUTES(
			c.car_model "model",
			c.car_color "color"
		)
	)
FROM cars c;
Résultat
<car model="Twingo" color="Gold"></car>
<car model="206" color="Black"></car>
<car model="105" color="Red"></car>
<car model="A4" color="White"></car>

Dans cette fonction il faut 2 valeurs pour chaque attributs : la valeur de la table (ici : c.car_model) et le nom de l’attribut (ici : « model »).

Il faut bien utiliser des guillemets et non des apostrophes pour les alias.

XMLFOREST

La fonction XMLFOREST permet d’ajouter des balises filles en y mettant les attributs. Elle s’utilise elle aussi à l’intérieur de la fonction XMLELEMENT :

SELECT
	XMLELEMENT(
		NAME "car",
		XMLFOREST(
			c.car_description "description"
		)
	)
FROM cars c;
Résultat
<car>
<description>Very small car</description>
</car>
<car>
<description>Nice</description>
</car>
<car>
<description>Is it a car ?</description>
</car>
<car>
<description>My car (may be)</description>
</car>

Elle est utilisée lorsque l’attribut à insérer s’écrit sur plusieurs ligne (description, adresse, etc.).

XMLSERIALIZE

Toutes ces fonctions retourne des objets de type XMLType et sont donc pas très intéressant tels-quels.

La fonction XMLSERIALIZE nous permet donc d’extraire un CLOB à partir d’un XMLype :

SELECT
	XMLSERIALIZE(CONTENT XMLELEMENT(
		NAME "car",
		XMLATTRIBUTES(
			c.car_model "model",
			c.car_color "color"
		),
		XMLFOREST(
			c.car_description "description"
		)
	))
FROM cars c;
Résultat
<car model="Twingo" color="Gold"> <description>Very small car</description> </car>
<car model="206" color="Black"> <description>Nice</description> </car>
<car model="105" color="Red"> <description>Is it a car ?</description> </car>
<car model="A4" color="White"> <description>My car (may be)</description> </car>

Avec des balises filles

Imaginons avoir besoin d’extraire la liste des voitures avec chacune des pièces utilisées à la constructions (C’est pas évident à imaginer, j’en convient).

Nous pourrions arriver au résultat suivant :

SELECT
	XMLSERIALIZE(CONTENT XMLELEMENT(
		NAME "car",
		XMLATTRIBUTES(
			c.car_model "model",
			c.car_color "color"
		),
		XMLFOREST(
			c.car_description "description"
		),
		(
			SELECT XMLAGG(
				XMLELEMENT(
					NAME "item",
					XMLATTRIBUTES(
						i.item_ref "reference",
						i.item_label "label",
						i.item_price "price"
					)
				)
			)
			FROM items i
			WHERE i.car_id = c.car_id
		)
	))
FROM cars c;
Résultat
<car model="Twingo" color="Gold"> <description>Very small car</description> <item reference="EZ332D" label="door" price="54€"></item> <item reference="RE132M" label="Nothing" price="1€"></item> </car>
<car model="206" color="Black"> <description>Nice</description> <item reference="EZ332D" label="door" price="54€"></item> </car>
<car model="105" color="Red"> <description>Is it a car ?</description> </car>
<car model="A4" color="White"> <description>My car (may be)</description> <item reference="EZ332Z" label="door" price="432€"></item></car>

Vous avez sûrement remarqué l’apparition de la fonction XMLAGG. Elle est à utiliser dans les requêtes qui génèrent les balises filles. Elle permet de concaténer toutes les filles.

Tagués avec : , , , , , , , , ,
Publié dans Oracle

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

*