locked
SQL - XSD - XML RRS feed

  • Question

  • Hello,

    I have data in SQL tables.

    I would like to:

    Get this data and convert it to xml file using xsd schema (xsd has different schema than sql tables).

    How do I do that? Any free tools I can use for it? Any easy way to do it?

    Thank you.


    V. A.
    Monday, May 17, 2010 7:37 PM

Answers

  • Hi,

    You can't just use a random XSD to "map" data from one chape to another. The XSD just describes the shape of the data you want, you also need to provide the "mapping" from the shape you have your data today.

    That can be done either by loading data from the DB  into some XML and then using XSLT transforming that XML into another XML which fits the XSD.

    Or you could retrieve the data from DB in the desired format directly using the FOR XML abilities of the SQL Server (see here http://msdn.microsoft.com/en-us/library/ms345117(SQL.90).aspx) which might allow you to shape the data directly.

    Or you could use a C#/VB program to load the data from the DB and produce the desired XML using through some XML library like XmlWrite, LINQ to XML or XmlDocument.

    In either case the XSD is not directly used because it doesn't provide the information of how to map the data, you would basically just use it for you to understand how the data should look like in the end. At the end you could use the XSD in your program to validate that the XML you produced is valid against the XSD, but that's just a check.

    Thanks,


    Vitek Karas [MSFT]
    Thursday, May 27, 2010 8:50 AM
    Moderator

All replies

  • Hello,

    I think we can easily retrieve the data from SQL and fill in a dataset. After that, it is easy to export the data as XML file from the dataset via DataSet.WriteXML()

    Please refer to the following KB article for some detailed codes.

    http://support.microsoft.com/kb/301271

     

    Is this what you are looking for?

     

    Best regards,

    Ji Zhou


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Wednesday, May 19, 2010 11:56 AM
    Moderator
  • my sql table has absolutely different schema than xsd schema that I have (we got it from another company and we need to send them data from us using their format).

    How would I map my fields to the fields in that schema?

    And from the article above, I do not see it using xsd file.

    Thank you.


    V. A.
    Wednesday, May 19, 2010 5:29 PM
  • Hi,

    You can't just use a random XSD to "map" data from one chape to another. The XSD just describes the shape of the data you want, you also need to provide the "mapping" from the shape you have your data today.

    That can be done either by loading data from the DB  into some XML and then using XSLT transforming that XML into another XML which fits the XSD.

    Or you could retrieve the data from DB in the desired format directly using the FOR XML abilities of the SQL Server (see here http://msdn.microsoft.com/en-us/library/ms345117(SQL.90).aspx) which might allow you to shape the data directly.

    Or you could use a C#/VB program to load the data from the DB and produce the desired XML using through some XML library like XmlWrite, LINQ to XML or XmlDocument.

    In either case the XSD is not directly used because it doesn't provide the information of how to map the data, you would basically just use it for you to understand how the data should look like in the end. At the end you could use the XSD in your program to validate that the XML you produced is valid against the XSD, but that's just a check.

    Thanks,


    Vitek Karas [MSFT]
    Thursday, May 27, 2010 8:50 AM
    Moderator