How to export data from database to xml according the XSD

Question

we have an XSD and we want a way to export data in xml format according the XSD. The data now is in a SQL database but we dont have problem to use mysql or other database format. We need to map XSD with table data in order to export them in xml format.

Answer 1

Got this from here

--Create test table relating to the breakfast menu

CREATE TABLE Menus
(name varchar(255)
,price numeric(12,4)
,description varchar(1500)
,calories int);

--insert some test data

Insert Into Menus Values ('eggs',22.22,'test description eggs',333);
Insert Into Menus Values ('steak',22.22,'test description steak',333);
Insert Into Menus Values ('big side of bacon',22.22,'test description bacon',333);
Insert Into Menus Values ('huge side of bacon',22.22,'test description more bacon',333);
Insert Into Menus Values ('OMG BACON!',22.22,'test description oh man, more bacon',333);

XSD schema used to define the export

<?xml version="1.0"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="breakfast_menu">
    <xs:complexType>
      <xs:sequence>
        <xs:element minOccurs="0" maxOccurs="unbounded" name="food">
          <xs:complexType>
            <xs:sequence>
              <xs:element minOccurs="0" name="name" type="xs:string" />
              <xs:element minOccurs="0" name="price" type="xs:string" />
              <xs:element minOccurs="0" name="description" type="xs:string" />
              <xs:element minOccurs="0" name="calories" type="xs:unsignedShort" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

-- SQL to generate file

DECLARE @MenuSchema XML
SET @MenuSchema =   (      
SELECT * FROM OPENROWSET        
    (BULK 'C:\Menu.xsd', SINGLE_CLOB)            
        AS xmlData)
--create the schema for using later in the XML type
CREATE XML SCHEMA COLLECTION MenuSchema AS @MenuSchema;

DECLARE @xmlData XML (MenuSchema)
SET @xmlData =
            (SELECT
                [name]
                ,price
                ,[description]
                ,calories
            FROM
            dbo.Menus [food]
            FOR XML AUTO, ROOT ('breakfast_menu'), ELEMENTS)

SELECT @xmlData