Select * from dbo.fn_ReadURL('types=utility&yearfrom=2002&locations=wellington%2cchristchurch%2cdunedin&pricefrom=5000&priceto=15000') where Field = 'types'
create or alter function dbo.fn_ReadURL (@FieldBase varchar(max))
Returns @tbReturn Table
(
Field Varchar(200),
[Value] Varchar(Max)
)
AS
Begin
Declare @Str varchar(max) = @FieldBase,
@StrStart varchar(max) = '=',
@StrEnd varchar(Max) = '&'
Set @Str = '<Main><Field>' + @Str + '</Value></Main>'
Set @Str = replace(@Str,'=','</Field><Value>')
Set @Str = replace(@Str,'&','</Value></Main><Main><Field>')
Set @Str = replace(@Str,'+',' ')
Set @Str = replace(@Str,'%2',',')
Declare @XMLStr xml = @str
Insert @tbReturn (Field, [Value])
Select
Main.value('(Field)[1]', 'Varchar(200)') AS 'Field',
Main.value('(Value)[1]', 'Varchar(Max)') AS 'Value'
From
@XMLStr.nodes('/Main') AS AOC(Main)
Return
End
Select * from dbo.fn_ReadURL('types=utility&yearfrom=2002&locations=wellington%2cchristchurch%2cdunedin&pricefrom=5000&priceto=15000') where Field = 'types'
create or alter function dbo.fn_ReadURL (@FieldBase varchar(max))
Returns @tbReturn Table
(
Field Varchar(200),
[Value] Varchar(Max)
)
AS
Begin
Declare @Str varchar(max) = @FieldBase,
@StrStart varchar(max) = '=',
@StrEnd varchar(Max) = '&'
Set @Str = '<Main><Field>' + @Str + '</Value></Main>'
Set @Str = replace(@Str,'=','</Field><Value>')
Set @Str = replace(@Str,'&','</Value></Main><Main><Field>')
Set @Str = replace(@Str,'+',' ')
Set @Str = replace(@Str,'%2',',')
Declare @XMLStr xml = @str
Insert @tbReturn (Field, [Value])
Select
Main.value('(Field)[1]', 'Varchar(200)') AS 'Field',
Main.value('(Value)[1]', 'Varchar(Max)') AS 'Value'
From
@XMLStr.nodes('/Main') AS AOC(Main)
Return
End