We help IT Professionals succeed at work.

How to map SQL query results to a provided XSD file in SQL Server 2008 R2

Hi Experts,

CASE:

I am running MS SQL SERVER 2008 R2.  I need to map my query results to a provided XSD file.  I did not create the XSD file and it does not look like the file was generated with my DB structure in mind.

QUESTIONS:

1. How can I map the results of my SQL query to this XSD ?
2. Are there any Visual Tools for accomplishing this task more easily?
3. Do I need to register/import this XSD file into my DB?
4. Do I have to do anything special in my SQL query (aside from actually composing it) to get the resulting XML to validate?
5. Can anyone point me to a small understandable example of this sort of process?


Any help is much appreciated.
Comment
Watch Question

Theo KouwenhovenApplication Consultant

Commented:
Hi EddieIT,

afaik, it's not possible to create an xml without mapping it.
Automatic mapping is no option, because te structure of Xml/xsd files are (mostly) quite complex, while sql output is flat.
To map this data, you need to import the xsd into a tool that makes it possible to create an object from the xsd an the sub structures.
Now a sql recordset can be mapped....
e.g. for an order item, it could look like:
order.lines.line[1].item.number=recordset.itemNumber
order.lines.line[1].item.price=recordset.salesPrice

we use for this kind of mapping XBintegrator.... (check www.invenso.com)
But i'm sure there are more tools like this...
Senior Developer
Commented:
Just output your XML and use an appropriate XSLT stylsheet to create the desired output. This can be done in SSIS with a XML Task. E.g. XML Source - Making things easier with XSLT.