Procédure pour générer des jeux de données (cf :instance) avec Excel et SQLServer 2005/2008

Nous avons le modèle objet suivant :

<Category>
<Id/>
<Nom/>
</Category>

<Product>
<Id/>
<Nom/>
<Price/>
<Category typeName="Category"/>
</Product>

Nous voulons instancier le Modèle avec les données suivantes

Produit1 Category 2
Produit2 Category 1
Produit3 Category 1

Etape 1 : Utilisation d’Excel pour remplir le jeux de donnée

Créer un classeur « GenererInstancesCF.xls » dans Excel avec 2 feuilles : Category et Product

Dans la feuille Category remplir les données comme suit :
A B
1 Id Name
2 '1 Category 1
3 '2 Category 2

Il est important pour la suite de préfixer avec une simple quote les données,
(sinon les id sont géré comme cela 1.00000000000)

Dans feuille Product remplir les données comme suit :

A B C D E
1 Id Name Category Price CategoryId
2 '1 Produit1 Category 2 30 =""&EQUIV(C2;Category!B:B;0)-1
3 '2 Produit2 Category 1 40 =""&EQUIV(C3;Category!B:B;0)-1
4 '3 Produit3 Category 1 50 =""&EQUIV(C4;Category!B:B;0)-1

Remarque : la feuille peut provenir d’un existant, dans la colonne C on a la relation produit-catégories, pour déduire l’id on utilise une fonction matricielle d’Excel (EQUIV) qui permet à partir d’une chaine de rechercher le numéro de ligne de la matrice (on retire 1 pour déduire l’ID).
Dans le cas présent les ID sont générés ordonnées de façon croissante avec les fonctions utilisateurs d’auto-incrémentation (glisser-déplacer).

Le jeu de données est prêt.

Etape 2 : Utilisation de SQLServer

On utilise SQLServer (2005 ou supérieur) avec SQLManagment Studio pour requêter le fichier Excel créé précédemment avec les fonctions de génération XML natives fourni par le moteur de base de données SQL.
Pour cela le moteur de base de données doit être configuré de la façon suivante :

"Outils de configuration /Configuration de la surface d’exposition des fonctionnalités"
- Cliquer sur Configuration de la surface d’exposition des fonctionnalités
-- Sélectionner (treeview à gauche) "Requete distantes appropriées"
-- Cocher : Activer la prise en charge des fonctions OPENROWSET et OPENDATASOURCE

Dans SQLManagment Studio, ouvrir n’importe quel base, puis exécuter les requêtes suivantes :

WITH XMLNAMESPACES (
'http://www.softfluent.com/codefluent/2005/1' as "cf"
)
select Id,Name
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\dev\CodeFluent\GenererInstancesCF.xls;HDR=YES;IMEX=1', [Category$])
for xml path ('cf:instance'),root('cf:instances')

GO

Résultat :

<cf:instances xmlns:cf="http://www.softfluent.com/codefluent/2005/1">
<cf:instance>
<Id>1</Id>
<Name>Category 1</Name>
</cf:instance>
<cf:instance>
<Id>2</Id>
<Name>Category 2</Name>
</cf:instance>
</cf:instances>

WITH XMLNAMESPACES (
'http://www.softfluent.com/codefluent/2005/1' as "cf"
)
select Id,Name,CategoryId as "Category/Id"
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\dev\CodeFluent\GenererInstancesCF.xls;HDR=YES;IMEX=1', [Product$])
for xml path ('cf:instance'),root('cf:instances')

GO

Résultat :

<cf:instances xmlns:cf="http://www.softfluent.com/codefluent/2005/1">
<cf:instance>
<Id>1</Id>
<Name>Produit1</Name>
<Category>
<Id>2</Id>
</Category>
</cf:instance>
<cf:instance>
<Id>2</Id>
<Name>Produit2</Name>
<Category>
<Id>1</Id>
</Category>
</cf:instance>
<cf:instance>
<Id>3</Id>
<Name>Produit3</Name>
<Category>
<Id>1</Id>
</Category>
</cf:instance>
</cf:instances>

FIN