Wednesday, November 18, 2009

SQL Server : Query XML

create function dbo.myAppDateTest(@ReqID nvarchar(30))
RETURNS varchar(30)
AS
BEGIN
DECLARE @FileContents XML
BEGIN
SELECT @FileContents = flags from tblapptrequest WHERE REQUESTID=@ReqID
END
return(
SELECT
x.item.value('appdate[1]','CHAR(10)')
FROM
@FileContents.nodes('SampleXML') AS x(item))
end

--------------------

select cast(flags as xml).query('appdate').value('appdate[1]','char(10)') as appdate from tblapptrequest where requestid='439'