Generate Database Schema information as XML

Question

I am trying to generate XML based on an SQL Server Schema. I have followed a few references including: (How to convert database table structure to XML file in sql server?)

After some trial and error, I have gotten as far as:

SELECT TABLE_NAME as '@tablename',        
(
    SELECT
            DATA_TYPE as 'Column/@datatype',
            case data_type 
                when 'nvarchar' 
                then CHARACTER_MAXIMUM_LENGTH 
                when 'varchar'  
                then CHARACTER_MAXIMUM_LENGTH
                else null 
            end  as 'Column/@Length',
            case IS_NULLABLE 
                 when 'NO'   --caseinsensitive by default
                 then 'False'
                 when 'YES'
                 then 'True'
                 else null
            end         
            AS 'Column/@is_nullable',
             Column_Name as 'Column'
    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
    For XML Path, type
)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='dbo'
ORDER BY TABLE_NAME ASC  
For XML PATH ('Table'),Root('Tables')

What I get is:

<Tables>
  <Table tablename="Table1">
    <row>
      <Column datatype="int" is_nullable="False">AA</Column>
    </row>
    <row>
      <Column datatype="nvarchar" Length="50" is_nullable="True">Name</Column>
    </row>
    <row>
      <Column datatype="bit" is_nullable="False">Active</Column>
    </row>
    <row>
      <Column datatype="timestamp" is_nullable="False">CreatedDate</Column>
    </row>
  </Table>
    <Table tablename="Table2">
    <row>
      <Column datatype="int" is_nullable="False">AA</Column>
    </row>
    <row>
      <Column datatype="datetime2" is_nullable="True">CreatedDate</Column>
    </row>
  </Table>
</Tables>

The expected output would have the following structure.

<Tables>
  <Table tablename="Table1">
     <Columns>
          <Column datatype="int" is_nullable="False">AA</Column>
          <Column datatype="nvarchar" Length="50" is_nullable="True">Name</Column>
          <Column datatype="bit" is_nullable="False">Active</Column>
          <Column datatype="timestamp" is_nullable="False">CreatedDate</Column>
     </Columns>
      </Table>
    <Table tablename="Table2">
     <Columns>
          <Column datatype="int" is_nullable="False">AA</Column>
          <Column datatype="datetime" is_nullable="True">CreatedDate</Column>
     <Columns>
      </Table>
</Tables>

Can someone shed some light on

1. How to remove the individual <row></row> tags and replace with a single set of outer <columns></columns>
2. how to change datatype= datetime2 to datatype= datetime

Thank you for your help.

Answer 1

Your were close. Just take note of (...) as columns and the Path('') in the subquery

Updated... I missed the datetime requirement

Select TABLE_NAME as '@tablename'
      ,(
        Select case when DATA_TYPE like 'datetime%' then 'datetime' else DATA_TYPE end  as 'Column/@datatype',
               case data_type 
                    when 'nvarchar' 
                    then CHARACTER_MAXIMUM_LENGTH 
                    when 'varchar'  
                    then CHARACTER_MAXIMUM_LENGTH
                    else null 
                end  as 'Column/@Length',
                case IS_NULLABLE 
                    when 'NO'   --caseinsensitive by default
                    then 'False'
                    when 'YES'
                    then 'True'
                    else null
                end  AS 'Column/@is_nullable',
                Column_Name as 'Column'
        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(''), type
      ) AS Columns
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='dbo'
ORDER BY TABLE_NAME ASC  
For XML Path('Table'),Root('Tables')

Sample Results

<Tables>
  <Table tablename="OD">
    <Columns>
      <Column datatype="int" is_nullable="False">OD-Nr</Column>
      <Column datatype="int" is_nullable="True">OD-Pt</Column>
      <Column datatype="int" is_nullable="True">OD-PS</Column>
      <Column datatype="varchar" Length="50" is_nullable="False">OD-Class</Column>
      <Column datatype="varchar" Length="250" is_nullable="False">OD-Title</Column>
      <Column datatype="int" is_nullable="False">OD-LM-Usr</Column>
      <Column datatype="datetime" is_nullable="False">OD-LM-UTC</Column>
      <Column datatype="int" is_nullable="False">OD-Deleted</Column>
    </Columns>
  </Table>
  <Table tablename="OD-Map">
    <Columns>
      <Column datatype="int" is_nullable="False">Map-Nr</Column>
      <Column datatype="varchar" Length="50" is_nullable="True">Map-Grp</Column>
      <Column datatype="varchar" Length="500" is_nullable="True">Map-Val1</Column>
      <Column datatype="varchar" Length="500" is_nullable="True">Map-Val2</Column>
      <Column datatype="varchar" Length="500" is_nullable="True">Map-Val3</Column>
      <Column datatype="varchar" Length="500" is_nullable="True">Map-Val4</Column>
      <Column datatype="int" is_nullable="True">Map-LM-Usr</Column>
      <Column datatype="datetime" is_nullable="True">Map-LM-UTC</Column>
      <Column datatype="bit" is_nullable="True">Map-Deleted</Column>
    </Columns>
  </Table>
</Tables>