Conversion implicite du type de données XML en Nvarchar

Comment éviter l’erreur : la conversion implicite du type de données XML en NVARCHAR n’est pas autorisée dans les scripts T-SQL ?

Comment convertir une variable XML en VARCHAR et éviter les erreurs de conversion comme : Conversion implicite du type de données XML, en T-SQL ? Avec SQL Server il est parfois intéressant de mixer des colonnes numériques avec des colonnes en XML pour une utilisation spéciale.

1. Gérer l’erreur SQL Server de conversion implicite du type de données XML

En effet, pour faire une requête plus facilement il est pratique de convertir la colonne de type XML en NVARCHAR pour pouvoir la filtrer avec les fonctions classiques. Cette requête fonctionne aussi avec des colonnes XML basées sur un schéma, qui est un format d’échange de données. Par exemple avec un bout de document XML basique, comme dans l’exemple ci-dessous.

DECLARE @mon_xml XML;
DECLARE @ma_chaine NVARCHAR(500);

SET @mon_xml =
'<entreprise>
	<employes>Hitachi</employes>
	<employes>Durant</employes>
	<employes>Marwani</employes>
</entreprise>';

SET @ma_chaine  = @mon_xml;

SELECT @ma_chaine;

 Un erreur est retournée par SQL Server : La conversion implicite du type de données XML en NVARCHAR n’est pas autorisée. Utilisez la fonction CONVERT pour exécuter cette requête.

La même erreur en Anglais :  Implicit conversion from data type XML to NVARCHAR is not allowed. Use the CONVERT function to run this query.

Noter que ce message est générique et qu’il concerne toutes les conversions entre types de données non compatibles. 

2. Convertir une colonne XML en VARCHAR avec la fonction SQL CONVERT

La solution est d’utiliser la fonction SQL Server CONVERT du type XML vers le type NVARCHAR(). Avec cette solution simple on peut effectuer une requête sur des données de type XML et même faire une recherche avec les fonctions de filtre comme LIKE par exemple.

DECLARE @mon_xml XML;
DECLARE @ma_chaine VARCHAR(500);

SET @mon_xml =
'<entreprise>
	<employes>Hitachi</employes>
	<employes>Durant</employes>
	<employes>Marwani</employes>
</entreprise>';

SET @ma_chaine = CONVERT(VARCHAR(500), @mon_xml);

SELECT @ma_chaine as RESULTAT;

3. Exemple de code T-SQL avec plusieurs balises XML

Voici le même exemple avec deux niveaux de balises XML imbriquées et les mêmes noms que votre exemple précédent :

DECLARE @mon_xml XML;
DECLARE @ma_chaine VARCHAR(500);

SET @mon_xml =
'<entreprise>
    <employes>
        <nom>Hitachi</nom>
        <age>35</age>
    </employes>
    <employes>
        <nom>Durant</nom>
        <age>28</age>
    </employes>
    <employes>
        <nom>Marwani</nom>
        <age>42</age>
    </employes>
</entreprise>';

SET @ma_chaine = CONVERT(VARCHAR(500), @mon_xml);

SELECT @ma_chaine as RESULTAT;

Ce code définit une variable @mon_xml contenant des données XML avec une balise racine et trois balises imbriquées, chacune contenant une balise et une balise . Le code convertit ensuite cette variable XML en une chaîne de caractères en utilisant la fonction CONVERT. Enfin, il sélectionne la chaîne résultante dans une colonne nommée RESULTAT.

4. Code pour convertir le XML avec chaque balise dans une colonne différente

Ce dernier exemple de code est plus complexe car il permet de convertir le format XML en VARCHAR mais aussi de séparer chaque niveau de balise XML dans une colonne dédiée. Voici donc l’explication étape par étape de la requête T-SQL ci-dessous :

1. La première ligne déclare une variable nommée « @mon_xml » avec le type « XML ». Cette variable est utilisée pour stocker le contenu XML.
2. La deuxième ligne déclare une variable nommée « @ma_chaine » avec le type « VARCHAR(MAX) ». Cette variable est utilisée pour stocker une chaîne de caractères qui représente l’objet XML stocké dans la variable « @mon_xml ».
3. La troisième ligne contient le contenu XML qui doit être stocké. Il s’agit d’un exemple d’une entreprise avec deux départements et plusieurs employés. Chaque employé a un nom, un prénom et un poste.
4. La quatrième ligne utilise la fonction « CONVERT » pour convertir l’objet XML stocké dans la variable « @mon_xml » en une chaîne de caractères stockée dans la variable « @ma_chaine ».
5. La cinquième ligne utilise la commande « SELECT » pour afficher la variable « @ma_chaine » qui contient le contenu XML sous forme de chaîne de caractères.

DECLARE @mon_xml XML;
DECLARE @ma_chaine VARCHAR(MAX);

SET @mon_xml = '
<entreprise>
	<nom>Expert-Only Inc.</nom>
	<departements>
		<departement>
			<nom>Marketing</nom>
			<employes>
				<employe>
					<nom>Hitachi</nom>
					<prenom>Paul</prenom>
					<poste>Chargé de communication</poste>
				</employe>
				<employe>
					<nom>Durant</nom>
					<prenom>Marie</prenom>
					<poste>Assistante marketing</poste>
				</employe>
			</employes>
		</departement>
		<departement>
			<nom>Comptabilité</nom>
			<employes>
				<employe>
					<nom>Marwani</nom>
					<prenom>John</prenom>
					<poste>Comptable</poste>
				</employe>
				<employe>
					<nom>Ahmedi</nom>
					<prenom>Emily</prenom>
					<poste>Assistante comptable</poste>
				</employe>
			</employes>
		</departement>
	</departements>
</entreprise>';

SELECT
    T.c.value('nom[1]', 'VARCHAR(MAX)') AS Nom,
    D.c.value('nom[1]', 'VARCHAR(MAX)') AS NomDepartement,
    E.c.value('nom[1]', 'VARCHAR(MAX)') AS NomEmploye,
    E.c.value('prenom[1]', 'VARCHAR(MAX)') AS PrenomEmploye,
    E.c.value('poste[1]', 'VARCHAR(MAX)') AS PosteEmploye
FROM @mon_xml.nodes('entreprise') T(c)
	CROSS APPLY T.c.nodes('departements/departement') D(c)
	CROSS APPLY D.c.nodes('employes/employe') E(c);

Le résultat de la requête est donc le suivant:

Entreprise	Département	Nom	Prénom	Poste
Expert-Only Inc.	Marketing	Hitachi	Paul	Chargé de communication
Expert-Only Inc.	Marketing	Durant	Marie	Assistante marketing
Expert-Only Inc.	Comptabilité	Marwani	John	Comptable
Expert-Only Inc.	Comptabilité	Ahmedi	Emily	Assistante comptable

5. Conclusion sur la conversion XML en NVARCHAR avec T-SQL

En conclusion, ce tutoriel explique comment convertir une variable T-SQL de type XML en chaîne de caractères et éviter les erreurs de conversion implicite de type de données.

En effet, cet article explique comment transformer facilement une colonne ou une variable stockée au format XML. Vers une variable en NVARCHAR. Le XML est utilisé en T-SQL pour séparer des parties de texte avec séparateurs. Par exemple, cet exemple de script Transact-SQL permet de découper une chaîne de caractère avec séparateurs en plusieurs lignes.

La conversion d’une colonne de type XML en NVARCHAR avec la fonction CONVERT permet de requêter facilement des données XML en utilisant des fonctions de filtre comme LIKE, par exemple. Le tutoriel fournit également des exemples de code T-SQL avec plusieurs niveaux de balises XML imbriquées et l’affichage des balises dans des colonnes différentes.

Enfin, le XML est utilisé en T-SQL pour séparer des parties de texte avec des séparateurs et permet de découper une chaîne de caractère en plusieurs lignes en utilisant une fonction XML disponible avec SQL Server.