<

Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x

Remove XML Empty Nodes with MSSQL Recursive Stored Procedure

Published on
11,055 Points
4,955 Views
1 Endorsement
Last Modified:
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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
0 Comments

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Join & Write a Comment

In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month