/******************************* 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
/******************************* 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
/******************************* 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
/******************************* 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
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
<doc>
<a>1</a>
<b bb="2" bc="3" />
<c bd="3" />
<d>
<d1>2</d1>
</d>
<e ea="1" />
</doc>
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.
Comments (0)