Export to XML

Hi,

I have been researching the new features of Server 2005 and its handling of XML; there are some impressive features.

I am hoping to design a reusable process for extracting data as XML and am having trouble, I am hoping that someone can help me brainstorm some of my ideas.

Conceptually I would like a procedure or service that I can pass a Stored Procedure and any necessary information needed to execute it as well as an XML Schema and have returned an XML stream containing the results of executing the Stored Procedure formatted according to the provided schema.

The closest feature to this type of functionality that I have found is the HTML/SOAP Endpoints. But with these I see no way to format the out put to a provided schema.

The motivation for this seems like it should be a common one. We have multiple sources of data and many clients who want regular data extracts. We already have Stored Procedures that extract the same data for application use and we also know the schema of the XML we would like to produce. So a reusable tool to produce these XML extracts would be ideal.

Does anyone have experience with this type of thing?

Thanks,

Denis

Denis,

I'm not aware of any SQL Server built-in capability to auto generate an arbitrary XML result based on a XML schema. You can create a stored procedure (potentially using in-proc CLR SP) that will take a XML schema and generate the XML format you need, but this will be code that you will need to write. That same SP can then be exposed through SOAP endpoints if web services is of interest in your scenario.

Alternatively, you can write an ASP.net function to retrieve the data from SQL Server and then generate the XML format you need.

Jimmy Wu