<

Remove XML Empty Nodes with MSSQL Recursive Stored Procedure

Published on
11,381 Points
5,281 Views
1 Endorsement
Last Modified:
Approved
Community Pick
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
Comment
0 Comments

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Join & Write a Comment

SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month