Community Pick: Many members of our community have endorsed this article.

Remove XML Empty Nodes with MSSQL Recursive Stored Procedure

Published:
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data.

I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from the XML document. I have used XPath queries and functions to do that in combination with dynamic code. The names of the variables are in Spanish so maybe you will suffer a little to understand the code so I will try to explain.

Because in a given XML we don't know how many elements and attributes we have or what their names are, I use the XPath local-name function to find out the name of the nodes and use the modify function to delete the current empty element or the current empty attribute. I also use dynamic code in SQL so the database engine allows me to execute the XPath sentences even if I don't know the names of the elements. The code is a little bit complex because I have used recursion to pass through the entire xml document.

The following stored procedure deletes a given empty attribute from a given element in a given XML. Note that the XML must be passed in xml type.

An empty attribute means: <Node emptyattribute=""/>

 
/******************************* spXMLDeleteEmptyAttribute *************************************************/
                      if exists (select * from sysobjects where id = object_id('dbo.spXMLDeleteEmptyAttribute') and type = 'P') drop procedure dbo.spXMLDeleteEmptyAttribute
                      GO             
                      CREATE PROCEDURE spXMLDeleteEmptyAttribute
                      		@Path		varchar(max),
                      		@Atributo	varchar(max),
                      		@XML		xml OUTPUT
                      		
                      
                      
                      --//WITH ENCRYPTION
                      AS BEGIN
                        -- SET nocount ON
                        DECLARE
                          @SQL			nvarchar(max)
                      
                          SET @SQL = N'SET ANSI_NULLS ON ' +
                                     N'SET ANSI_WARNINGS ON ' +
                                     N'SET QUOTED_IDENTIFIER ON ' +             
                                     'SET @XML.modify(' + CHAR(39) + 'delete ' + @Path + '[@' + @Atributo + '=""]/@' + @Atributo + CHAR(39) + ') '
                      
                          EXEC sp_executesql @SQL, N'@Path varchar(max), @Atributo varchar(max), @XML xml OUTPUT', @Path = @Path, @Atributo = @Atributo, @XML = @XML OUTPUT
                      END
                      GO

Open in new window


The following stored procedure receives the path from the element that you want to remove from the given xml and checks if it is empty. if so, it removes the element.

An empty element means the following: <EmptyNode></EmptyNode>
 
/******************************* spXMLDeleteEmptyElement *************************************************/
                      if exists (select * from sysobjects where id = object_id('dbo.spXMLDeleteEmptyElement') and type = 'P') drop procedure dbo.spXMLDeleteEmptyElement
                      GO             
                      CREATE PROCEDURE spXMLDeleteEmptyElement
                      		@Path		varchar(max),
                      		@XML		xml OUTPUT
                      		
                      
                      
                      --//WITH ENCRYPTION
                      AS BEGIN
                        -- SET nocount ON
                        DECLARE
                          @SQL			nvarchar(max)
                          
                          IF NULLIF(@Path,'') IS NULL RETURN
                          SET @SQL = N'SET ANSI_NULLS ON ' +
                                     N'SET ANSI_WARNINGS ON ' +
                                     N'SET QUOTED_IDENTIFIER ON ' +             
                                     'SET @XML.modify(' + CHAR(39) + 'delete ' + @Path + '[not(node())][count(' + @Path+ '/@*)=0]' + CHAR(39) + ') '
                          
                          EXEC sp_executesql @SQL, N'@Path varchar(max), @XML xml OUTPUT', @Path = @Path, @XML = @XML OUTPUT
                          
                      END
                      GO

Open in new window


The following code uses the spXMLDeleteEmptyAttribute stored procedure to delete all the empty attributes from a given element in a given XML document.

/******************************* spXMLDeleteAllEmptyAttributes *************************************************/
                      if exists (select * from sysobjects where id = object_id('dbo.spXMLDeleteAllEmptyAttributes') and type = 'P') drop procedure dbo.spXMLDeleteAllEmptyAttributes
                      GO             
                      CREATE PROCEDURE spXMLDeleteAllEmptyAttributes
                      		@XML		xml OUTPUT,
                      		@Path		varchar(max)
                      
                      
                      --//WITH ENCRYPTION
                      AS BEGIN
                        -- SET nocount ON
                      
                      DECLARE 
                        @Contador				int, 
                        @CantidadElementos	int,
                        @SQL					nvarchar(max)
                      
                        
                        SET @SQL = N'SET ANSI_NULLS ON ' +
                                   N'SET ANSI_WARNINGS ON ' +
                                   N'SET QUOTED_IDENTIFIER ON ' +             
                                   N'DECLARE @Contador			int, ' + 
                                   N'        @CantidadElementos	int, ' +
                                   N'        @NuevoPath			varchar(max),  ' +                          
                                   N'        @PathElemento		varchar(max),  ' +                                       
                                   N'        @PathAtributo		varchar(max)   ' +                                                    
                                   N'SET @CantidadElementos = @xml.query(' + CHAR(39) + 'count(' + ISNULL(@Path,'') + '@*)' + CHAR(39) + ').value(' + CHAR(39) + '.' + CHAR(39) + ',' + CHAR(39) + 'int' + CHAR(39) + ') ' + 
                                   N'SELECT @Contador = @CantidadElementos ' +             
                                   N'WHILE @Contador > 0 ' + 
                                   N'BEGIN ' +
                                   N'  SELECT  @PathAtributo = @xml.value(' + CHAR(39) + 'local-name((' + ISNULL(@Path,'') + '@*[sql:variable("@Contador")])[1])' + CHAR(39) + ',' + CHAR(39) + 'varchar(max)' + CHAR(39) + ') ' +              
                                   N'  SELECT @PathElemento = SUBSTRING(@Path,1,LEN(@PATH)-1) ' +             
                                   N'  EXEC spXMLDeleteEmptyAttribute @PathElemento, @PathAtributo, @XML OUTPUT ' +
                                   N'  SELECT  @NuevoPath = @Path + @PathAtributo ' + 
                                   N'  SET @Contador = @Contador - 1 ' +             
                                   N'END ' 
                                   
                        EXEC sp_executesql @SQL, N'@Path varchar(max), @XML xml OUTPUT', @Path = @Path, @XML = @XML OUTPUT
                      END
                      GO

Open in new window


The last stored procedure calls the previous ones and itself to check and delete all the empty elements or attributes of the XML Document that are child nodes of the given path.

/******************************* spXMLAutoClean *************************************************/
                      if exists (select * from sysobjects where id = object_id('dbo.spXMLAutoClean') and type = 'P') drop procedure dbo.spXMLAutoClean
                      GO             
                      CREATE PROCEDURE spXMLAutoClean
                      		@XML		xml OUTPUT,
                      		@Path		varchar(max)
                      
                      
                      --//WITH ENCRYPTION
                      AS BEGIN
                        -- SET nocount ON
                      
                      DECLARE 
                        @Contador				int, 
                        @CantidadElementos	int,
                        @SQL					nvarchar(max)
                      
                        
                        SET @SQL = N'SET ANSI_NULLS ON ' +
                                   N'SET ANSI_WARNINGS ON ' +
                                   N'SET QUOTED_IDENTIFIER ON ' +             
                                   N'DECLARE @Contador			int, ' + 
                                   N'        @CantidadElementos	int, ' +
                                   N'        @NuevoPath			varchar(max),  ' +             
                                   N'        @PathElemento		varchar(max)   ' +                          
                                   N'SET @CantidadElementos = @xml.query(' + CHAR(39) + 'count(' + ISNULL(@Path,'') + '*)' + CHAR(39) + ').value(' + CHAR(39) + '.' + CHAR(39) + ',' + CHAR(39) + 'int' + CHAR(39) + ') ' + 
                                   N'SELECT @Contador = @CantidadElementos ' +             
                                   N'WHILE @Contador > 0 ' + 
                                   N'BEGIN ' +
                                   N'  SELECT  @NuevoPath = @Path + @xml.value(' + CHAR(39) + 'local-name((' + ISNULL(@Path,'') + '*[sql:variable("@Contador")])[1])' + CHAR(39) + ',' + CHAR(39) + 'varchar(max)' + CHAR(39) + ') + ' + CHAR(39) + '/' + CHAR(39) + ' ' + 
                                   --N'  SELECT @NuevoPath ' +
                                   N'  EXEC spXMLDeleteAllEmptyAttributes @XML OUTPUT, @NuevoPath ' +                           
                                   N'  EXEC spXMLAutoClean @XML OUTPUT, @NuevoPath ' +
                                   N'  SELECT @PathElemento = SUBSTRING(@NuevoPath,1,LEN(@NuevoPath)-1) ' +        
                                   N'  EXEC spXMLDeleteEmptyElement @PathElemento, @XML OUTPUT ' +                               
                                   N'  SET @Contador = @Contador - 1 ' +             
                                   N'END ' 
                                   
                        EXEC sp_executesql @SQL, N'@Path varchar(max), @XML xml OUTPUT', @Path = @Path, @XML = @XML OUTPUT
                      END
                      GO

Open in new window


To use the stored procedures, you can execute the following code in a TSQL Session.

DECLARE @xml as xml
                      DECLARE @path as varchar(max)
                      
                      SET @xml = 
                      '<doc>
                       <a>1</a>
                       <b ba="" bb="2" bc="3" />
                       <c bd="3"/>
                       <d><d1>2</d1><d2></d2></d>
                       <e ea="1" eb=""></e>
                       <f><f1><f2><f3></f3></f2></f1></f>
                      </doc>'
                      SET @Path = '/'
                      
                      EXEC spXMLAutoClean @XML OUTPUT, @Path
                      SELECT @XML

Open in new window


The resulting XML will be the following:

<doc>
                        <a>1</a>
                        <b bb="2" bc="3" />
                        <c bd="3" />
                        <d>
                          <d1>2</d1>
                        </d>
                        <e ea="1" />
                      </doc>

Open in new window


I hope that this code will be useful to you when using MSSQL with XML.
1
6,266 Views

Comments (0)

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.