SET QUOTED_IDENTIFIER OFF
go

use wroxdb
go

--
--   Name:  get_empty_xml
--
--   Description: return an xml string representing an empty record
--
--
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'get_empty_xml')
BEGIN
    DROP PROCEDURE get_empty_xml
END
GO
 
PRINT 'get_empty_xml'
GO
CREATE PROCEDURE get_empty_xml @table varchar(30),
				@xml varchar(4000) OUTPUT,
				@alias varchar(30) = NULL
AS

DECLARE @colname varchar(30)
DECLARE @elem varchar(30)
DECLARE empty CURSOR
FOR 
	SELECT substring(C.COLUMN_NAME,1,30) AS Colname
	FROM INFORMATION_SCHEMA.COLUMNS C
	WHERE C.TABLE_NAME = @table
	ORDER BY C.ORDINAL_POSITION

	IF @alias IS NULL
		SELECT @elem = @table
	ELSE
		SELECT @elem = @alias
		
		SELECT @xml = "<" + @elem + " xact='I'"

OPEN empty
 
	WHILE 1=1
	BEGIN
	 
	   FETCH NEXT FROM empty INTO @colname
	   IF @@FETCH_STATUS <> 0 break
	 
		SELECT @xml = @xml + " " + @colname + "=''" 
	 
	END
	
	SELECT @xml = @xml + " />"
 
CLOSE empty
DEALLOCATE empty

GO

GRANT EXECUTE on get_empty_xml TO Public
go

-- test
/*
declare @xml varchar(4000)

exec get_empty_xml 'orders', @xml OUTPUT, 'order'

print @xml

*/

