SQL 2005 xml 处理的一些sample

USE Test
--Create 2 tables as an example
CREATE TABLE ExampleTable
(
[ID] int PRIMARY KEY
,[Name] nvarchar(256)
)
CREATE TABLE ExampleTable2
(
[ID] int PRIMARY KEY
,[Name] nvarchar(256)
)
----way1
SELECT *
from sys.objects [table]
WHERE
[Name] LIKE 'ExampleTable%'
FOR XML AUTO, ROOT('tables')
--way2
SELECT[name] AS [@name], object_id AS [@object_id],
* from sys.objects
WHERE [Name] LIKE 'ExampleTable%'
FOR XML PATH('table'), ROOT('tables')
 
--SELECT * from sys.objects WHERE [Name] LIKE 'ExampleTable%'
--Way3
-------------------------------------
--Nested Results using FOR XML PATH--
-------------------------------------
-------------------------------------
--Nested Results using FOR XML PATH--
-------------------------------------
SELECT
t.[name] AS [@name]
,t.object_id AS [@object_id]
,t.[create_date] AS [date/@cdate]
,t.[modify_date] AS [date/mdate]
,CAST('<mydata>some random xml</mydata>' AS xml) [*]
,(
     SELECT
     c.[name] AS [@name]
     ,c.[column_id] AS [@column_id]
     ,c.[is_nullable] AS [@allows_nulls]
     ,ty.[name] AS [@datatype]
     from sys.columns c
     INNER JOIN sys.types ty ON c.[user_type_id] = ty.[user_type_id]
     WHERE   t.[object_id] = c.[object_id]
     FOR XML PATH('columns'), TYPE
)
from sys.objects t
WHERE [Name] LIKE 'ExampleTable%'
FOR XML PATH('table'), ROOT('tables')
SELECT  cdate, * from syscolumns WHERE id=1077578877
SELECT * from sysobjects WHERE id=1077578877
SELECT * from sys.objects WHERE object_id=1077578877
--------------------
--Set Xml Variable--
--------------------
/*
In 2000, it wasn't possible to set any variable to the result of a query
*/
DECLARE @xml xml
SELECT @xml =
(
SELECT TOP 3
[Name]
from sys.objects
FOR XML AUTO
)
SELECT @x