Sql-server : proc.gestion xml avec xpath

Commenter

Contenu du snippet

Permet de gérer toutes les actions courantes sur un document XML (count, exist, value, modify, add, delete et query).
La procédure est "sur-commenté" et un exemple complet est donné dans la conclusion.
Cette procédure peut permettre d'éviter des redondances si le type XML est beaucoup utilisé.

Source / Exemple : 


USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Yannick Laussanne
-- Create date: 07/2012
-- Description:	Gestion XML (count, exist, value, query, modify, add, delete)
-- Params:
-- @action peut prendre les différentes valeurs suivantes : (forme abrégée ou longue)
--		'1' ou 'COUNT_NODE'			renvoi valeur scalaire int
--		'2'	ou 'NODE_EXIST'			renvoi valeur scalaire bit
--		'3' ou 'NODE_VALUE'			renvoi valeur scalaire variant
--		'4' ou 'MODIFY'				modifie valeur d'un noeud ou attribut puis renvoi valeur succès/echec (1/0)
--		'5' ou 'XML_ADD'			ajoute un noeud ou attribut puis renvoi valeur succès/echec (1/0)
--		'6' ou 'XML_DELETE'			supprime un noeud ou attribut puis renvoi valeur succès/echec (1/0)
--		'7' ou 'XML_QUERY'			renvoi fragment xml
-- =============================================
CREATE PROCEDURE [dbo].[util_XML]
	@xml xml output,				/*doc xml OU un bloc nvarchar(max) qui représente le document xml*/
	@action varchar(64),			/*Quelle action appliquer sur le doc xml*/
	@nodeXPATH nvarchar(2000),		/*xPath pour xml.value(), xml.query() ou simplement le chemin d'un noeud ou d'un attribut*/
	@vResult sql_variant output,	/*Résultat de type variant*/
	@typeResult nvarchar(50)=null,	/*De quel type sera le résultat d'une action NODE_VALUE*/
	@ModifyVal nvarchar(4000)=null, /*Si action XML_MODIFY ou XML_ADD, la valeur de remplacement ou d'ajout*/
	@bSupprBlanksIfStr bit = 0		/*trim si résultat chaine*/
AS
BEGIN
	SET NOCOUNT ON;
	
	DECLARE 
		@xquery nvarchar(max),
		@retProc int
	DECLARE 
		@CR char(1), 
		@LF char(1), 
		@TAB char(1)
		
	SELECT 
		@retProc = 0,
		@vResult = null	
	SELECT 
		@CR = CHAR(10), 
		@LF = CHAR(13), 
		@TAB = 0x09
	
	IF @xml is null
	BEGIN
		print 'Document xml null !'
		RETURN 1;
	END
	
	IF	@action not in('1', '2', '3', '4', '5', '6', '7') and
		@action not in ('COUNT_NODE', 'NODE_EXIST', 'NODE_VALUE', 'MODIFY', 'XML_ADD', 'XML_DELETE', 'XML_QUERY')
	BEGIN
		print 'Action invalide !'
		RETURN 2;
	END
	
	/* Pour trace */
	SET @action = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@action, 
		'1', 'COUNT_NODE'), '2', 'NODE_EXIST'), '3', 'NODE_VALUE'), '4', 'MODIFY'), '5', 'XML_ADD'), 
		'6', 'XML_DELETE'), '7', 'XML_QUERY')
	print '@action = ' + @action
	
	
	
	-- ----------------------------------------------------> COUNT_NODE
	IF @action in('1', 'COUNT_NODE')
	BEGIN
		SET @xquery = N'
			SELECT @vOut = 
				cast(convert(nvarchar, 
					@xmlIn.query(''count(' + replace(@nodeXPATH,N'''',N'''''') + N')''))
				as int)'
		
		EXEC sp_executesql
			@xquery,
			N'@vOut sql_variant output, @xmlIn xml output',
			@vOut = @vResult output,
			@xmlIn = @xml output
	END
	
	-- ----------------------------------------------------> NODE_EXIST
	ELSE IF @action in('2', 'NODE_EXIST')
	BEGIN
		SET @xquery = N'
			IF @xmlIn.exist('''+replace(@nodeXPATH,N'''',N'''''')+N''') = 1
				SELECT @vOut = convert(bit,1)
			ELSE
				SELECT @vOut = convert(bit,0)'

		EXEC sp_executesql
			@xquery,
			N'@xmlIn xml output, @vOut sql_variant output',
			@xmlIn = @xml output,
			@vOut = @vResult output
	END
	
	-- ----------------------------------------------------> NODE_VALUE
	ELSE IF @action in('3', 'NODE_VALUE')
	BEGIN
		/* Transforme en singleton si pas déjà fait */
		IF right(@nodeXPATH,4) != ')[1]'
			SET @nodeXPATH = '(' + @nodeXPATH + ')[1]'
	
		SET @xquery = N'
			SELECT @vOut = @xmlIn.value('''+@nodeXPATH+N''', '''+@typeResult+N''')'
		
		EXEC sp_executesql
			@xquery,
			N'@xmlIn xml output, @vOut sql_variant output',
			@xmlIn = @xml output,
			@vOut = @vResult output
		
		IF ISNULL(@bSupprBlanksIfStr,0) = 1 and @typeResult like '%char%'
		BEGIN
			SET @vResult = 
				LTRIM(RTRIM(
					REPLACE(
						REPLACE(
							REPLACE(
								CAST(@vResult as varchar(8000)), @CR, ''), @LF, ''), @TAB, '')
				));
		END
	END
	
	-- ----------------------------------------------------> XML_MODIFY
	ELSE IF @action in('4', 'MODIFY')
	BEGIN
		/* Transforme en singleton si pas déjà fait et si pas expression de modfification complete */
		IF right(@nodeXPATH,4) != ')[1]' and @nodeXPATH not like '%replace value of%'
			SET @nodeXPATH = '(' + @nodeXPATH + ')[1]'
		
		SET @xquery = N'
			SET @xmlIn.modify(''
				replace value of ' + @nodeXPATH + N'
				with "'+replace(@ModifyVal,N'''',N'''''')+N'"'')'

		/* Le xml modifié est renvoyé en param output de la procédure */
		EXEC @retProc = sp_executesql 
			@xquery,
			N'@xmlIn xml output',
			@xmlIn = @xml output
			
		SET @vResult = 'XML Modifié'
	END
	
	-- ----------------------------------------------------> XML_ADD
	ELSE IF @action in('5', 'XML_ADD')
	BEGIN
		/* Transforme en singleton si pas déjà fait */
		IF right(@nodeXPATH,4) != ')[1]'
			SET @nodeXPATH = '(' + @nodeXPATH + ')[1]'
	
		SET @xquery = N'
			SET @xmlIn.modify(''
				insert ' + @ModifyVal + N' as last
				into ' + @nodeXPATH + ''')'
			
		EXEC @retProc = sp_executesql 
			@xquery,
			N'@xmlIn xml output',
			@xmlIn = @xml output		
		
		SET @vResult = 'Noeud ajouté'
	END	
	
	-- ----------------------------------------------------> XML_DELETE
	ELSE IF @action in('6', 'XML_DELETE')
	BEGIN
		SET @xquery = N'
			SET @xmlIn.modify(''
				delete ' + @nodeXPATH + N''')'
				
		/* Le xml modifié est renvoyé en param output de la procédure */
		EXEC @retProc = sp_executesql 
			@xquery,
			N'@xmlIn xml output',
			@xmlIn = @xml output
			
		SET @vResult = 'Noeud(s) supprimé(s)'
	END	
	
	-- ----------------------------------------------------> XML_QUERY
	ELSE IF @action in('7', 'XML_QUERY')
	BEGIN
		/* Résultat direct pour ce cas */
		SET @xquery = N'
			SELECT ['+@action+'] = @xmlIn.query('''+@nodeXPATH+N''')'

		EXEC sp_executesql 
			@xquery,
			N'@xmlIn xml output',
			@xmlIn = @xml output
		
		RETURN 0;
	END	
	
	
	/* Si doit retourner une valeur scalaire, count ou exists par exemple */
	/* le nom du champ est le nom de l'action */
	SET @xquery = 'SELECT @vResultIn as ' + @action
	EXEC sp_executesql 
		@xquery,
		N'@vResultIn sql_variant output',
		@vResultIn =  @vResult output

	RETURN @retProc;
END

Conclusion :


Procédure outil à créer dans master pour utilisation depuis d'autres bases.
Ci-dessous un exemple par "action"

/* ============================================= */
/* XML de test */
/* ============================================= */
DECLARE @xml xml
SET @xml = N'
<test attrib1="toto" attrib2="3.1415">
<node id="1" attribnode="titi">
valeur du noeud ligne 1
valeur du noeud ligne 2.. tab fin
</node>
<node id="2" attribnode="tutu">
la valeur du noeud
</node>
</test>'

/* Retour procédure et résultat output */
DECLARE @retProc int
DECLARE @variantRes sql_variant

/* ============================================= */
/* Teste un COUNT_NODE */
/* ============================================= */
EXEC master..util_XML
@xml = @xml output,
@action = 'COUNT_NODE',
@nodeXPATH = N'/test/node',
@vResult = @variantRes output

/* ============================================= */
/* Teste un NODE_EXIST avec attrib id = 2 */
/* ============================================= */
EXEC master..util_XML
@xml = @xml output,
@action = 'NODE_EXIST',
@nodeXPATH = N'/test/node[@id eq "2"]',
@vResult = @variantRes output

/* ============================================= */
/* Teste un NODE_VALUE */
/* ============================================= */
EXEC master..util_XML
@xml = @xml output,
@action = 'NODE_VALUE',
@nodeXPATH = N'/test/node[@id eq "2"]',
@vResult = @variantRes output,
@typeResult = N'varchar(100)'

/* ============================================= */
/* Teste un NODE_VALUE pour un attribut */
/* ============================================= */
EXEC master..util_XML
@xml = @xml output,
@action = 'NODE_VALUE',
@nodeXPATH = N'/test/node[@id eq "1"]/@attribnode',
@vResult = @variantRes output,
@typeResult = N'varchar(100)'

/* ============================================= */
/* Teste un MODIFY (modifie 1 attribut) */
/* ============================================= */
EXEC master..util_XML
@xml = @xml output,
@action = 'MODIFY',
@nodeXPATH = N'/test/node[@id eq "2"]/@attribnode',
@vResult = @variantRes output,
@typeResult = null,
@ModifyVal = N'la nouvelle valeur'

/* Vérifie le xml modifié */
SELECT @xml.query('/')

/* ============================================= */
/* Teste un XML_ADD, ajoute 1 fragment CDATA dans node 2 */
/* ============================================= */
DECLARE @CDATA nvarchar(500)
SET @CDATA = N'<sectionCDATA><![CDATA[ if( var == TRUE ){::Action("Ceci est un test !");}]]></sectionCDATA>'

EXEC @retProc = master..util_XML
@xml = @xml output,
@action = 'XML_ADD',
@nodeXPATH = N'/test/node[@id eq "2"]',
@vResult = @variantRes output,
@typeResult = N'',
@ModifyVal = @CDATA,
@bSupprBlanksIfStr = 0

/* les résultats */
SELECT '@retProc' = @retProc, '@variantRes' = @variantRes, '@xml' = @xml

/* ============================================= */
/* Teste un XML_DELETE : supprime l'attribut du node 1 */
/* ============================================= */
EXEC master..util_XML
@xml = @xml output,
@action = 'XML_DELETE',
@nodeXPATH = N'/test/node[@id eq "1"]/@attribnode',
@vResult = @variantRes output

/* puis supprime le node 2 */
EXEC master..util_XML
@xml = @xml output,
@action = 'XML_DELETE',
@nodeXPATH = N'/test/node[@id eq "2"]',
@vResult = @variantRes output

/* Vérifie le xml modifié */
SELECT @xml.query('/')

/* ============================================= */
/* Teste un XML_QUERY, renvoi fragment XML du node 1 */
/* ============================================= */
EXEC master..util_XML
@xml = @xml output,
@action = 'XML_QUERY',
@nodeXPATH = N'/test/node[1]',
@vResult = @variantRes output

A voir également

S'inscrire maintenant

Vous n'êtes pas encore membre ?

inscrivez-vous, c'est gratuit et ça prend moins d'une minute !

Les membres obtiennent plus de réponses que les utilisateurs anonymes.

Le fait d'être membre vous permet d'avoir un suivi détaillé de vos demandes et codes sources.

Le fait d'être membre vous permet d'avoir des options supplémentaires.

Du même auteur (yann_lo_san)