SQL 2000在恢复格式正确的XML时遇到问题

Question

在格式良好的XML中返回以下查询时遇到问题。我能想到的最好的方法是使用AUTO，元素，但这不会返回任何根。任何帮助都是非常感谢的。我在SQL 2005或更高版本中没有问题，只是SQL 2000有问题(即将升级:)

SELECT 
 dbid AS DatabaseID
,NAME AS DatabaseName
,CASE 
    WHEN NAME IN ('master','mb','model','tempdb','distribution') THEN 'S'
    ELSE 'U'
 END AS SysUserType 
,cmptlevel AS CompatibilityLevel
,databasepropertyex(NAME, 'Collation') AS CollationName
,CASE databasepropertyex(NAME, 'Status') 
    WHEN 'ONLINE'              THEN 0
    WHEN 'RESTORING'           THEN 1
    WHEN 'RECOVERING'          THEN 2
    WHEN 'RECOVERY_PENDING'    THEN 3
    WHEN 'SUSPECT'             THEN 4
    WHEN 'EMERGENCY'           THEN 5
    WHEN 'OFFLINE'             THEN 6
    WHEN 'COPYING'             THEN 7
 END AS State
,databasepropertyex(NAME, 'Status') AS StateDesc
,CASE databasepropertyex(NAME, 'Recovery')
    WHEN 'FULL'                THEN 1
    WHEN 'BULK_LOGGED'         THEN 2
    WHEN 'SIMPLE'              THEN 3
 END AS RecoveryModel
,databasepropertyex(NAME, 'Recovery') AS RecoveryModelDesc
,crdate AS DatabaseCreationDate
,(SELECT MAX(bs.backup_finish_date) FROM msdb.dbo.backupset AS bs WHERE bs.database_name=name) AS LastBackupDate  
,(SELECT (SUM(saf.size) * 8 / 1024) FROM sysaltfiles AS saf WHERE saf.dbid=dbid) AS  SizeMB
,(SELECT COUNT(sp.dbid) FROM sysprocesses AS sp WHERE dbid > 0 AND spid >= 51 AND sp.dbid=dbid) AS ActiveDBConnections 
,SERVERPROPERTY('Machinename') AS SQLServerName
,CASE 
    WHEN SERVERPROPERTY('Instancename') IS NULL
        THEN 'Default'
    ELSE SERVERPROPERTY('Instancename')
    END AS SQLServerInstanceName
,SERVERPROPERTY('ProductVersion') AS SQLServerVersion
,SERVERPROPERTY('Edition') AS SQLServerEdition
FROM sysdatabases  
ORDER BY DatabaseID
FOR XML AUTO, ELEMENTS

Answer 1

由于SQL-Server2000不知道真正的XML数据类型，并且只返回一个字符串，因此您可以选择最简单的方法：

SELECT N'<root>' + (Your FOR XML AUTO,ELEMENTS query here) + N'</root>';

但是我没有一台2000服务器来测试...

祝好运!