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
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
Select allOpen 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
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
Select allOpen 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
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
Select allOpen 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
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
Select allOpen 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
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
Select allOpen 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>
1:
2:
3:
4:
5:
6:
7:
8:
9:
Select allOpen in new window
I hope that this code will be useful to you when using MSSQL with XML.
