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
Ïà¹ØÎĵµ£º
ÔÚSQL*PlusÖÐĬÈϵÄ"&"±íʾÌæ´ú±äÁ¿£¬SQL*PlusÈÏΪ&·ûºóÃæµÄ×Ö·û´®Îª±äÁ¿£»Ö»ÒªÔÚÃüÁîÖгöÏָ÷ûºÅ£¬SQL*Plus¾Í»áÒªÄãÊäÈëÌæ´úÖµ¡£Õâ¾ÍÒâζ×ÅÄãÎÞ·¨½«Ò»¸öº¬Óи÷ûºÅµÄ×Ö·û´®²åÈë»ò¸üе½Êý¾Ý¿â
set define off ¹Ø±ÕÌæ´ú±äÁ¿¹¦ÄÜ£¬“&”½«×÷ΪÆÕͨ×Ö·û
set def ......
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Collections;
using System.Data.SqlClient;
namespace DAL
{
/// <summary>
/// Êý¾Ý¿âµÄͨÓ÷ÃÎÊ´úÂë
/// ´ËÀàΪ³éÏóÀ࣬²»ÔÊÐíʵÀý»¯£¬ÔÚÓ¦Óà ......
Êý¾Ý¿âÀàÐÍ
================================
1¡¢ ²ã´ÎÐͺÍÍø×´ÐÍ£º
´ú±í²úÆ·ÊÇ1969ÄêIBM¹«Ë¾ÑÐÖƵIJã´ÎÄ£ÐÍÊý¾Ý¿â¹ÜÀíϵͳIMS¡£
2¡¢ ¹ØϵÐÍÊý¾ÝÐͿ⣺
Ä¿Ç°´ó²¿·ÖÊý¾Ý¿â²ÉÓõÄÊǹØϵÐÍÊý¾Ý¿â¡£1970ÄêIBM¹«Ë¾µÄÑо¿Ô±E£®F£®CoddÌá³öÁ˹ØϵģÐÍ¡£Æä´ú±í²úƷΪsysem RºÍInges¡£
3¡¢ µÚÈý´úÊý¾Ý¿â½«Îª¸ü¼Ó·á¸»µÄÊý¾ ......
±¸·Ý£º
A. ±¸·ÝÕû¸ö MyNwind Êý¾Ý¿â
˵Ã÷ MyNwind Êý¾Ý¿â½öÓÃÓÚÑÝʾ¡£
ÏÂÀý´´½¨ÓÃÓÚ´æ·Å MyNwind Êý¾Ý¿âÍêÕû±¸·ÝµÄÂß¼±¸·ÝÉ豸¡£
......
ÔÚSQL Server 2005Êý¾Ý¿âÖÐʵÏÖ×Ô¶¯±¸·ÝµÄ¾ßÌå²½Öè:
1¡¢´ò¿ªSQL Server Management Studio
2¡¢Æô¶¯SQL Server´úÀí
3¡¢µã»÷×÷Òµ->н¨×÷Òµ
4¡¢"³£¹æ"ÖÐÊäÈë×÷ÒµµÄÃû³Æ
5¡¢Ð½¨²½Ö裬ÀàÐÍÑ¡T-SQL£¬ÔÚÏÂÃæµÄÃüÁîÖÐÊäÈëÏÂÃæÓï¾ä£¨ºìÉ«²¿·ÖÒª¸ù¾Ý×Ô¼º ......