How to retrieve data as XML from SQL database

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

ForenameLIKECOALESCE(@.Forename,Forename)AND

Surname

LIKECOALESCE(@.Surname,Surname)AND

Email

LIKECOALESCE(@.Email,Email)AND

Ext

LIKECOALESCE(@.Ext,Ext)AND

DeptList

LIKECOALESCE(@.DeptList,DeptList)

FOR

XMLAUTO,ELEMENTS

If someone could be kind enough to help me out with this, I'd be really grateful.

Many thanks,

Tom

Hi tomosap,

Yes, this is a good way to get that.

Another way is to fill everything to run the query and fill data into a DataSet. You will be able to get the DataSet's data in XML format by calling DataSet.WriteXml() method.

Both these methods are good.

HTH. If this does not answer you question, please feel free to mark it as Not Answered and post your reply. Thanks!

|||

Hi,

Thanks for your reply - I'm afraid that my needs have slightly changed since this was posted. I now have an XML file which I transform using XSL so it displays all the data in a gridview. What I want to do is to have a search form so that I can search the data that's in the gridview. I.e.

Name: [INPUT]

Results in Gridview...

User enters name in input, clicks search, then all matches in Name column on the gridview is displayed. I've successfully managed to do this with a basic SQL database using the object source control and passing paramaters, but I don't know how to do this when the data source is XML.

All help would be appreciated.

Thanks,

Tom