How to convert database table structure to XML file in sql server?

Question

How will I convert table Schema to XML format? Format is given below.

<Tables>
  <Table>
    <Name>courses</Name>
    <Schema>dbo</Schema>
    <Columns>
      <Column>
        <Name>id</Name>
        <DataType>int</DataType>
     </Column>
     <Column>
       <Name>page_name</Name>
       <DataType>nvarchar</DataType>
       <Length>50</Length>
     </Column>
    </Columns>
  </Table>
  <Table>
    <Name>course_details</Name>
    <Schema>dbo</Schema>
    .....
    .....
  </Table>
</Tables>

I am able to generate the structure for columns and tables separately. But I want to consolidated both. How is it possible? My SQL scripts

For Tables:

SELECT
Distinct
TABLE_NAME as Name,
TABLE_SCHEMA as [Schema]
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='dbo'
ORDER BY TABLE_NAME ASC
For XML PATH ('Table'),
Root('Tables')

For Columns:

SELECT
Column_Name as Name,
DATA_TYPE as DataType,
CHARACTER_MAXIMUM_LENGTH as [Length]
FROM INFORMATION_SCHEMA.COLUMNS
For XML PATH ('Column'),
Root('Columns')

Solution 1

The same query, but looks better:

SELECT TABLE_NAME as '@Name',        
(
    SELECT Column_Name as '@Name',
            DATA_TYPE as '@DataType',
            case data_type 
                when 'nvarchar' 
                then CHARACTER_MAXIMUM_LENGTH 
                when 'varchar'  
                then CHARACTER_MAXIMUM_LENGTH
                else null 
            end  as '@Length',
            IS_NULLABLE AS '@IsNullable'

    FROM INFORMATION_SCHEMA.COLUMNS 
    where INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = 
        INFORMATION_SCHEMA.TABLES.TABLE_NAME
    order by INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION
    For XML PATH ('Column'), type
)

FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='dbo'
ORDER BY TABLE_NAME ASC  
For XML PATH ('Table'),Root('Tables')

Solution 2

You need to use the TYPE parameter of FOR XML PATH ..
Something like this:

SELECT  TABLE_NAME      as name,
        TABLE_SCHEMA    as [schema],
        (
            SELECT Column_Name as Name,
                    DATA_TYPE as DataType,
                    CHARACTER_MAXIMUM_LENGTH as [Length]
            FROM INFORMATION_SCHEMA.COLUMNS
            For XML PATH ('Column'),root('columns'), type
        )
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='dbo'
ORDER BY TABLE_NAME ASC
For XML PATH ('Table'),Root('Tables')