Xml2Sql

				
--**************************************
-- Name: Xml2Sql
-- Description:Converts an xml document to a sql where clause.
XML structured so that easy for front end to 
manuipulate without needing to see data structure.
In front end filter can be built up from list of table.field names
-- By: Keith Henry
--
-- Inputs:filter xml text document
output variable
--
-- Returns:SQL where string
--
-- Side Effects:assumes that result SQL string will be shorter 
than 8000 chars, while XML document can be 
massive.
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=725&lngWId=5--for details.--**************************************

/*	This proc converts an XML structured file following the following DTD:
		<?xml version="1.0" encoding="UTF-8"?>
		<!ELEMENT criteria EMPTY>
		<!ATTLIST criteria
			field CDATA #REQUIRED
			logic (equals | selection | greaterthan | lessthan | greaterthanorequalto | lessthanorequalto | notequals | startwith | endwith | contains | like | notselection | notstartwith | notendwith | notcontains | notlike | isblank | isnotblank) #REQUIRED
			value CDATA #IMPLIED
		>
		<!ELEMENT filter (criteria*, filter*)>
		<!ATTLIST filter
			logic (and | or) #REQUIRED
			name CDATA #IMPLIED
		>
	
	The filter element contains criteria, a logic and other filters.
	All the elements in a filter are seperated by the logic of that filter.
	To mix logics nest elements
	Outputs @SQLwhereclause with contents of where					*/
create proc Xml2Sql (
	@filterxml ntext,
	@SQLwhereclause varchar(8000) output
) as
begin
	set nocount on
	declare @hDoc int, @tstr varchar(3000), @tstr2 varchar(200), @rows int, @filterid int, @logic varchar(3), @criteria varchar(3000), @lastfilterid int, @name varchar(50) 
	declare @xmlfilter table (filterid int primary key, logic varchar(3), parentid int, criteria varchar(7000), uplist int, [name] varchar(50))
	declare @xmlcriteria table (filterid int, field varchar(255), logic varchar(100), value varchar(1000))
	select 	@rows = 1, @tstr = '/filter' --set up some initial values
	exec master.dbo.sp_xml_preparedocument @hDoc OUTPUT, @filterxml --parse XML doc
	while @rows > 0 --while filter contains anything
	begin
		set @tstr2 = @tstr
	
		--get filter logic and id
		insert 	@xmlfilter (filterid,logic,parentid,[name])
		select 	filterid, logic, parentid,(case [name] when '' then null else [name] end)
		from	openxml(@hDoc,@tstr2 ,2) 
		with	(filterid int '@mp:id', logic varchar(3) '@logic', parentid int '@mp:parentid', [name] varchar(50) '@name')
	
		--get rows for check and set up Xpath
		select	@rows = @@rowcount, @tstr = @tstr + '/filter', @tstr2 = @tstr2 + '/criteria'
		if @rows > 0 --check we got a filter before finding criterias
			insert 	@xmlcriteria (filterid,field,logic,value)
			select 	filterid , field,logic,	case 
					when value like '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]T[0-9][0-9]:[0-9][0-9]:[0-9][0-9].[0-9][0-9][0-9]' then 'convert(datetime,''' + value + ''',126)' --handle XML date format
					when logic in ('startwith','endwith','contains','like','notstartwith','notendwith','notcontains','notlike') then '''' + replace(value,'''','''''') + ''''
					when value like '%[a-zA-Z]%' or value like '%[[]%' or value like '%[]]%' or value like '%[%]%' then --add single quotes ' around strings
						case logic
							when 'selection' then '''' + replace(replace(value,'''',''''''),',',''',''') + ''''
							else '''' + replace(value,'''','''''') + ''''
						end
					else value end
			from	openxml(@hDoc,@tstr2 ,2) 
			with	(filterid int '@mp:parentid', field varchar(255) '@field', logic varchar(100) '@logic', value varchar(2000) '@value')	
	end
	exec master.dbo.sp_xml_removedocument @hDoc --chuck XML doc
	select	@tstr = null, @tstr2 = null, @rows = 1	
	/*	--Uncomment to debug XML parse
		select * from @xmlcriteria
		select * from @xmlfilter 		*/
	declare critcurs cursor for --use cursor to loop through records building up criteria
		select	c.filterid,
			'(' + c.field + 
				case c.logic
					when	'equals'		then ' = ' + c.value
					when	'selection'		then ' in (' + c.value + ')'
					when	'greaterthan'		then ' > ' + c.value 
					when	'lessthan'		then ' < ' + c.value 
					when	'greaterthanorequalto'	then ' >= ' + c.value 
					when	'lessthanorequalto'	then ' <= ' + c.value 
					when	'notequals'		then ' <> ' + c.value 
					when	'startwith'		then ' like ' + c.value + '+''%'''
					when	'endwith'		then ' like ''%''+' + c.value 
					when	'contains'		then ' like ''%''+' + c.value + '+''%'''
					when	'like'			then ' like ' + c.value
					when	'notselection'		then ' not in (' + c.value + ')'
					when	'notstartwith'		then ' not like ' + c.value + '+''%'''
					when	'notendwith'		then ' not like ''%''+' + c.value 
					when	'notcontains'		then ' not like ''%''+' + c.value + '+''%'''
					when	'notlike'		then ' not like ' + c.value
					when	'isblank'		then ' is null '
					when	'isnotblank'		then ' is not null '
				end 
			+ ')' as criteria,
			f.logic
		from	@xmlcriteria as c inner merge join @xmlfilter as f on c.filterid = f.filterid
		order by c.filterid --order by filter and then we know
		open critcurs
		fetch next from critcurs into @filterid, @criteria, @logic
		while (@@fetch_status = 0)
		begin
			if @filterid <> @lastfilterid
			begin
				if @tstr is not null
					update @xmlfilter set criteria = @tstr where filterid = @lastfilterid
				select	@tstr = @criteria, @lastfilterid = @filterid
			end
			else
				select	@tstr = isnull(@tstr + @logic,'') + @criteria, @lastfilterid = @filterid
			fetch next from critcurs into @filterid, @criteria, @logic
		end
		update @xmlfilter set criteria = @tstr where filterid = @lastfilterid
	close critcurs
	deallocate critcurs
	while @rows > 0 --now loop through starting with those with no children and deleting as we go
	begin
		update p set p.criteria = isnull(p.criteria + p.logic,'')+'('+ f.criteria +')', p.uplist = f.filterid --+ isnull('/*' + f.[name] + '-->*/','')
		from @xmlfilter as p inner join	(select top 1 f.filterid, f.parentid, f.criteria, f.[name] from @xmlfilter as f left outer join @xmlfilter as c on f.filterid = c.parentid where c.filterid is null)as f on p.filterid = f.parentid
		set	@rows = @@rowcount
		delete from @xmlfilter where filterid in (select uplist from @xmlfilter)
	end
	select @SQLwhereclause = '(' + criteria + ')', @name = isnull([name],convert(varchar,getdate(),120)) from @xmlfilter --sql clause is now in last remaining filter, name of our top filter will be taken as name of filter --isnull('/*' + [name] + '-->*/','') +
	set nocount off
end
GO