如何将数据库表结构转换为sql server中的XML文件？

Question

我将如何将表格模式转换为XML格式？格式如下。

<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> 

我能够分别生成列和表的结构。但我想巩固两者。这怎么可能？ 我的SQL脚本

为表：

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') 

为列：

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

Answer 1

你有没有考虑将架构成一个单一的表，然后产生XML？

Answer 2

您需要使用的TYPE参数FOR XML PATH ..
事情是这样的：

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') 

Answer 3

同样的查询，但看起来更好：

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')