We have a requirement to generate an XML output from an XSD file (a schema) and from a group of relational database tables (SQL Server 2008 R2). Ideally, we would be able to run a query that joins multiple tables (say dbo.customer and dbo.order where order is joined to customer by customer_id) in SQL Server that returns XML that conforms to the XSD.
sample query that would run nightly:
Select c.customer_id, c.customer_fname, c.customer_lname, o.order_id from dbo.customer c, dbo.order o where c.customer_id = o.customer_id
Ideally, we could run a SQL job that runs a query each night to generate this XML. If this is not feasible, we could run a ASP.NET console app that accomplishes the requirement.
We have evaluated Annotated XSD Schemas but have been unsuccessful thus far and have ead that these do not perform well. If you have used these successfully and could provide a sample of how to generate the query above that would be appreciated.