將資料庫架構資訊生成為XML

我正在嘗試基于 SQL Server 架構生成 XML。我遵循了一些參考資料，包括：（如何在 sql server 中將資料庫表結構轉換為 XML 檔案？）

經過一些反復試驗，我得到了：

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

我得到的是：

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

預期的輸出將具有以下結構。

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

有人可以解釋一下嗎

1. 如何洗掉單個<row></row>標簽并替換為一組外部標簽<columns></columns>
2. 如何更改datatype= datetime2為datatype= datetime

謝謝您的幫助。

uj5u.com熱心網友回復：

你很接近。只需要注意(...) as columns和Path('')在子查詢

更新......我錯過了datetime要求

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

樣本結果

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

標籤：sql-server 查询语句 模式 for-xml-path

上一篇：T-SQL動態資料透視查詢和SQL注入保護

下一篇：如何獲取最新狀態為True的用戶串列？