SQL - Etienne ZINZINDOHOUE

5
août
2011

SQL SERVER 2005 // SQL SERVER 2008

Un article de zinzineti Comments

Mon aide mémoire pour les manipulations des fichiers XML dans la base de données SQL Server.

Mon exemple de fichier XML est le suivant :

<?xml version="1.0" encoding="utf-8"?>

<AllContacts>

<Contacts_Group GroupType="Personnel" ID="1">

<contact>

<Nom>Gnêtoho</Nom>

<Prenom>Kossi</Prenom>

<Téléphone1>0101010101</Téléphone1>

<Téléphone2>0101010102</Tlphone2>

<Email>G.kossi@kossi.com</Email>

<Date_Naissance>19800101</Date_Naissance>

</contact>

<contact>

<Nom>édokoubèdjègbè</Nom>

<Prenom>wêkê</Prenom>

<Téléphone1>0202020201</Téléphone1>

<Téléphone2>0202020202</Téléphone2>

<Email>e.weke@weke.com</Email>

<Date_Naissance>19811202</Date_Naissance>

</contact>

</Contacts_Group>

<Contacts_Group GroupType="Professionnel" ID="2">

<contact>

<Nom>Dupond</Nom>

<Prenom>Eric</Prenom>

<Téléphone1>0606060601</Téléphone1>

<Téléphone2>0606060602</Téléphone2>

<Email>d.eric@pro.com</Email>

<Date_Naissance>19750107</Date_Naissance>

</contact>

<contact>

<Nom>Dubois</Nom>

<Prenom>Alice</Prenom>

<Téléphone1>0609090901</Téléphone1>

<Téléphone2>0609090902</Téléphone2>

<Email>e.weke@weke.com</Email>

<Date_Naissance>19761231</Date_Naissance>

</contact>

</Contacts_Group>

</AllContacts>

–Création de la table XMLTab pour importer le fichier XML

DROP TABLE XMLTab
CREATE TABLE XMLTab (xmlCol xml)

— Chargement du fichier de contacts XML dans la table XMLTab.
— Utilisons la procedure stockee P_ImportXMLFileToTable disponible sur mon blog

EXEC P_ImportXMLFileToTable @NomSchema = 'dbo',
@NomTable = N'XMLTab',
@XmlColonne = N'xmlCol',
@XmlFilenameWithDirectory=N'C:\temp\Contacts.xml'

–>>Recherche dans les données XML stockées : query et OPENXML
–>0.0 Liste de tous les contacts : sortie XML

SELECT xmlCol.query('/AllContacts/Contacts_Group/contact') FROM XMLTab

–>0.1 Liste de tous les contacts : sortie tabulaire

DECLARE @docHandle INT;
DECLARE @xmlDocument XML
SELECT @xmlDocument = xmlCol FROM XMLTab
EXECUTE sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument;
SELECT Nom ,Prenom,Téléphone1,Téléphone2,Email,Date_Naissance
FROM
OPENXML(@docHandle,'/AllContacts/Contacts_Group/contact',2)
WITH(Nom varchar(20),Prenom varchar(20),Téléphone1 varchar(20),Téléphone2 varchar(20),Email varchar(20),Date_Naissance varchar(20));
EXECUTE sp_xml_removedocument @docHandle;

–>1.1 Liste des contacts personnel : Sortie XML avec query

SELECT xmlCol.query('/AllContacts/Contacts_Group[@GroupType="Personnel"]/contact') FROM XMLTab

–/!\ Attention la methode « query » est sensible à la casse ! il doit être en minuscule.
— l’execution de la même requête avec le mot clé « query » en majuscule par exemple (QUERY) renvoie une erreur :

SELECT xmlCol.QUERY('/AllContacts/Contacts_Group[@GroupType="Personnel"]/contact') FROM XMLTab

/*
Msg 227, Niveau 15, État 1, Ligne 1
« QUERY » n’est pas une fonction, une propriété ou un champ valide.
*/

–>1.2 Liste des contacts personnel : Sortie Tabulaire avec OPENXML

DECLARE @docHandle INT;
DECLARE @xmlDocument XML
SELECT @xmlDocument = xmlCol FROM XMLTab
EXECUTE sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument;
SELECT Nom ,Prenom,Téléphone1,Téléphone2,Email,Date_Naissance
FROM
OPENXML(@docHandle,'/AllContacts/Contacts_Group[@GroupType="Personnel"]/contact',2)
WITH(Nom varchar(20),Prenom varchar(20),Téléphone1 varchar(20),Téléphone2 varchar(20),Email varchar(20),Date_Naissance varchar(20));
EXECUTE sp_xml_removedocument @docHandle;
GO

–>2.1 Liste des contacts Profesionnel : Sortie XML avec query

SELECT xmlCol.query('/AllContacts/Contacts_Group[@GroupType="Professionnel"]/contact') FROM XMLTab

–>2.2 Liste des contacts Profesionnel : Sortie Tabulaire

DECLARE @docHandle INT;
DECLARE @xmlDocument XML
SELECT @xmlDocument = xmlCol FROM XMLTab
EXECUTE sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument;
SELECT Nom ,Prenom,Téléphone1,Téléphone2,Email,Date_Naissance
FROM
OPENXML(@docHandle,'/AllContacts/Contacts_Group[@GroupType="Professionnel"]/contact',2)
WITH(Nom varchar(20),Prenom varchar(20),Téléphone1 varchar(20),Téléphone2 varchar(20),Email varchar(20),Date_Naissance varchar(20));
EXECUTE sp_xml_removedocument @docHandle;
GO

–>3.1 Coordonnées du contact professionel « Dupond » : Sortie XML

SELECT xmlCol.query('/AllContacts/Contacts_Group[@GroupType="Professionnel"]/contact[Nom ="Dupond"]') FROM XMLTab

–>3.2 Coordonnées du contact professionel « Dupond » : Sortie Tabulaire

DECLARE @docHandle INT;
DECLARE @xmlDocument XML
SELECT @xmlDocument = xmlCol FROM XMLTab
EXECUTE sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument;
SELECT Nom ,Prenom,Téléphone1,Téléphone2,Email,Date_Naissance
FROM
OPENXML(@docHandle,'/AllContacts/Contacts_Group[@GroupType="Professionnel"]/contact[Nom ="Dupond"]',2)
WITH(Nom varchar(20),Prenom varchar(20),Téléphone1 varchar(20),Téléphone2 varchar(20),Email varchar(20),Date_Naissance varchar(20));
EXECUTE sp_xml_removedocument @docHandle;
GO

–>3.3 Liste des contacts nés en Décembre : Sortie Tabulaire

–>> insert, update et delete : DML XML
–> insert
— Insertion d’un nouveau contact professionnel : utilisation de sql:variable

DECLARE @docHandle INT;
DECLARE @xmlDocument XML
DECLARE @NewContact XML
SET @NewContact = '<contact>
<Nom>New Nom contact professionel</Nom>
<Prenom>New Prenom New contact pro</Prenom>
<Téléphone1>0000000001</Téléphone1>
<Téléphone2>0000000002</Téléphone2>
<Email>NewContact.@pro.com</Email>
<Date_Naissance>00000000</Date_Naissance>
</contact>'
SELECT @xmlDocument = xmlCol FROM XMLTab
EXECUTE sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument;
SET @xmlDocument.modify('insert sql:variable ("@NewContact") as last
into (/AllContacts/Contacts_Group[@GroupType="Professionnel"])[1]')
UPDATE XMLTab SET xmlCol = @xmlDocument
EXECUTE sp_xml_removedocument @docHandle;

–/!\ Attention
–> 1. la methode « modify » est sensible à la casse ! il doit être en minuscule
— l’execution de la même requête avec le mot clé « modify » en majuscule par exemple (MODIFY) renvoie une erreur :
/*
Msg 227, Niveau 15, État 1, Ligne 14
« MODIFY » n’est pas une fonction, une propriété ou un champ valide.
*/

–> 2. Idem pour le mot clé « insert ». En majuscule on a cette erreur
/*
Msg 2209, Niveau 16, État 1, Ligne 14
XQuery [modify()]: Erreur de syntaxe près de ‘INSERT’
*/

— Insertion d’un contact professionnel : autre methode

DECLARE @docHandle INT;
DECLARE @xmlDocument XML
SELECT @xmlDocument = xmlCol FROM XMLTab
EXECUTE sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument;
SET @xmlDocument.modify('insert <contact>
<Nom> New Nom contact professionel</Nom>
<Prenom>New Prenom New contact pro</Prenom>
<Téléphone1>0000000001</Téléphone1>
<Téléphone2>0000000002</Téléphone2>
<Email>NewContact.@pro.com</Email>
<Date_Naissance>00000000</Date_Naissance>
</contact> as last
into (/AllContacts/Contacts_Group[@GroupType="Professionnel"])[1]')
UPDATE XMLTab SET xmlCol = @xmlDocument
EXECUTE sp_xml_removedocument @docHandle;
GO

–>> update
— Mise à jour du Telephone du nouveau contact profesionnel

DECLARE @docHandle INT;
DECLARE @xmlDocument XML
SELECT @xmlDocument = xmlCol FROM XMLTab
EXECUTE sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument;
SET @xmlDocument.modify('
replace value of (/AllContacts/Contacts_Group[@GroupType="Professionnel"]/contact[Nom="New Nom contact professionel"]/Téléphone1/text())[1]
with( "XXXXXXXXX")
')
SELECT @xmlDocument
UPDATE XMLTab SET xmlCol = @xmlDocument
EXECUTE sp_xml_removedocument @docHandle;
GO

— /!\ Attention. « replace value of » et « with » doivent être en minuscule !

–>> delete
— Supprimer le nouveau contact

DECLARE @docHandle INT;
DECLARE @xmlDocument XML
SELECT @xmlDocument = xmlCol FROM XMLTab
EXECUTE sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument;
SET @xmlDocument.modify('delete /AllContacts/Contacts_Group[@GroupType="Professionnel"]/contact[Nom ="New Nom contact professionel"]')
SELECT @xmlDocument
UPDATE XMLTab SET xmlCol = @xmlDocument
EXECUTE sp_xml_removedocument @docHandle;
GO

— /!\ Attention le mot clé « delete » doit être en miniscule ! Autrement on a cette erreur :
/*
Msg 6305, Niveau 16, État 1, Ligne 5
L’expression de manipulation de données XQuery est nécessaire dans la méthode de type de données XML.
*/

——————————–
Etienne ZINZINDOHOUE
——————————-

Laisser un commentaire

Vous devez être connecté pour poster un commentaire.

Connexion

Identifiez-vous

Créer un compte

SQL – Etienne ZINZINDOHOUE

Laisser un commentaire

Articles récents

Commentaires récents

Archives

Catégories

Méta

Identifiant
Mot de passe

Mot de passe oublié ?