755
技術社區[雲棲]
SQL FOR XML子句的各種用法
FOR XML子句有四種最基本的模式1、AUTO模式：返回數據表為起表名的元素，每一列的值返回為屬性；
2、RAW模式：返回數據行為元素，每一列的值作為元素的屬性；
3、PATH模式：通過簡單的XPath語法來允許用戶自定義嵌套的XML結構、元素、屬性值
4、EXPLICIT模式：通過SELECT語法定義輸出XML的結構
第1步：構造測試數據表及填充數據：
--1.建表語句
CREATE TABLE [dbo].[Users](
[UserID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL
)
--2.產生隨機數SQL函數
CREATE FUNCTION [GenerateRandomItem] ( @LENGTH INT )
RETURNS NVARCHAR(255)
AS
BEGIN
--申明變量
DECLARE @RandomNumber NVARCHAR(255)
DECLARE @I SMALLINT
DECLARE @RandNumber FLOAT
DECLARE @Position TINYINT
DECLARE @ExtractedCharacter VARCHAR(1)
DECLARE @ValidCharacters VARCHAR(255)
DECLARE @VCLength INT
--給變量賦值
SET @ValidCharacters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
SET @VCLength = LEN(@ValidCharacters)
SET @ExtractedCharacter = ''
SET @RandNumber = 0
SET @Position = 0
SET @RandomNumber = ''
SET @I = 1
WHILE @I < ( @Length + 1 )
BEGIN
SET @RandNumber = (SELECT RandNumber FROM [RandNumberView])
SET @Position = CONVERT(TINYINT, ( ( @VCLength - 1 )* @RandNumber + 1 ))
SELECT @ExtractedCharacter = SUBSTRING(@ValidCharacters,@Position, 1)
SET @I = @I + 1
SET @RandomNumber = @RandomNumber + @ExtractedCharacter
END
RETURN @RandomNumber
END
GO
CREATE VIEW [RandNumberView]
AS
SELECT RAND() AS [RandNumber]--3.添加測試數據INSERT INTO Users
(
FirstName ,
LastName
)
SELECT
dbo.GenerateRandomItem(10),
dbo.GenerateRandomItem(10)
GO 10--4.測試查詢
1.AUTO模式：
語句： SELECT UserID, FirstName,LastName FROM users FOR XML AUTO, XMLSCHEMA
說明：加上XMLSCHEMA，輸出xml架構，不加則隻輸出數據。
輸出：
2.RAW模式：
語句：SELECT UserID, FirstName,LastName FROM users FOR XML RAW ('MyUsers')
說明：將元素命名為自定義的名稱
輸出：
3.PATH模式：
語句： SELECT UserID "@ID", FirstName "Name/FirstName",LastName "Name/LastName" FROM usersFOR XML PATH ('MyUsers')
說明：可以指定xml結構
輸出：
4.EXPLICIT模式：（研究中，暫略... ... ）
最後更新：2017-04-02 06:52:16