title: How to convert database table structure to XML file in sql server?
body: <p>How will I convert table Schema to XML format? Format is given below.</p>
  
  <pre><code>&lt;Tables&gt;
    &lt;Table&gt;
      &lt;Name&gt;courses&lt;/Name&gt;
      &lt;Schema&gt;dbo&lt;/Schema&gt;
      &lt;Columns&gt;
        &lt;Column&gt;
          &lt;Name&gt;id&lt;/Name&gt;
          &lt;DataType&gt;int&lt;/DataType&gt;
       &lt;/Column&gt;
       &lt;Column&gt;
         &lt;Name&gt;page_name&lt;/Name&gt;
         &lt;DataType&gt;nvarchar&lt;/DataType&gt;
         &lt;Length&gt;50&lt;/Length&gt;
       &lt;/Column&gt;
      &lt;/Columns&gt;
    &lt;/Table&gt;
    &lt;Table&gt;
      &lt;Name&gt;course_details&lt;/Name&gt;
      &lt;Schema&gt;dbo&lt;/Schema&gt;
      .....
      .....
    &lt;/Table&gt;
  &lt;/Tables&gt;
  </code></pre>
  
  <p>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</p>
  
  <p><strong>For Tables:</strong></p>
  
  <pre><code>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')
  </code></pre>
  
  <p><strong>For Columns:</strong></p>
  
  <pre><code>SELECT
  Column_Name as Name,
  DATA_TYPE as DataType,
  CHARACTER_MAXIMUM_LENGTH as [Length]
  FROM INFORMATION_SCHEMA.COLUMNS
  For XML PATH ('Column'),
  Root('Columns')
  </code></pre>