Hi,
I have a website which is designed to search for employee information. I have the search system working which does exactly what I want to, but as an added feature I want there to be a button which, when someone clicks on it, it takes whatever the previous search was and generates a set of data in XML format which is based on the results. For example:
User searches for all entries with Forename = John; Results are listed in a gridview as per expected.
User then presses button with XML on it, and page pops up with just the XML output on it, i.e. whatever results are on the gridview but in a nested XML format
<records>
<record>
<Forename>John</Forename>
<Surname>Smith</Surname>
<Email>j.smith@blah.com</Email>
<Ext>1234</Ext>
<DeptList>History</DeptList>
</record>
</records>
I have created a stored procedure which will take the parameters from the search boxes and return the above information, but I don't know if this is the best way. Here it is for those interested:
CREATE
PROCEDURE ps_record_SELECT_NameSurnameEmailExtDeptasXML
@Forename
varchar(50),
@Surname
varchar(50),
@Email
varchar(50),
@Ext
varchar(4),
@DeptList
varchar(50)
AS
SELECT
Forename, Surname, Email, Ext, DeptList
FROM
dbo.record
WHERE
Forename LIKE COALESCE(@Forename,Forename) AND
Surname
LIKE COALESCE(@Surname,Surname) AND
Email
LIKE COALESCE(@Email,Email) AND
Ext
LIKE COALESCE(@Ext,Ext) AND
DeptList
LIKE COALESCE(@DeptList,DeptList)
FOR
XML AUTO, ELEMENTS
If someone could be kind enough to help me out with this, I'd be really grateful.
Many thanks,
Tom