<

Go Premium for a chance to win a PS4. Enter to Win

x

Remove XML Empty Nodes with MSSQL Recursive Stored Procedure

Published on
11,121 Points
5,021 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
0 Comments

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Join & Write a Comment

this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased riskā€¦

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month