T-SQL——關(guān)于XML類型_SQL創(chuàng)建XML
T-SQL——關(guān)于XML類型_SQL創(chuàng)建XML
目錄
志銘-2021年10月23日 09:03:14
0. 將結(jié)果集轉(zhuǎn)化為XML格式
- 測試數(shù)據(jù)
IF OBJECT_ID('tempdb..#tempStu') IS NOT NULL
BEGIN
DROP TABLE #tempStu;
END;
CREATE TABLE #tempStu
(
[Name] VARCHAR(4),
[SubjectName] VARCHAR(4),
[Scores] INT
);
INSERT INTO #tempStu
(
Name,
SubjectName,
Scores
)
VALUES
('張三', '語文', 90),
('李四', '語文', 100),
('王五', '語文', 80);
- FOR XML AUTO
- 表名作為節(jié)點(diǎn)名稱
- 一行作為一個(gè)節(jié)點(diǎn)，字段列作為節(jié)點(diǎn)屬性
--AUTO則默認(rèn)節(jié)點(diǎn)的名稱為表名稱
SELECT * FROM #tempStu WHERE Name = '李四' FOR XML AUTO;
--結(jié)果：（這里使用的是臨時(shí)表，前面的_x0023_是數(shù)據(jù)庫默認(rèn)生成的）
--<_x0023_tempStu Name="李四" SubjectName="語文" Scores="100" />
- FOR XML RAW
- 通過RAW() 參數(shù)設(shè)置節(jié)點(diǎn)名稱，無參默認(rèn)節(jié)點(diǎn)名稱為row
- 一行作為一個(gè)節(jié)點(diǎn)，字段列作為節(jié)點(diǎn)屬性
--無參默認(rèn)的使用row作為節(jié)點(diǎn)名稱
SELECT * FROM #tempStu FOR XML RAW
--結(jié)果：
--<row Name="李四" SubjectName="語文" Scores="100" />
--使用參數(shù)設(shè)置節(jié)點(diǎn)名稱
SELECT * FROM #tempStu WHERE Name = '李四' FOR XML RAW('Stu');
--結(jié)果:
--<Stu Name="李四" SubjectName="語文" Scores="100" />
- ELEMENTS
- 一行作為一個(gè)父節(jié)點(diǎn)，每一個(gè)字段作為一個(gè)子節(jié)點(diǎn)
SELECT Name,
SubjectName,
Scores
FROM #tempStu
FOR XML AUTO, ELEMENTS;
-- --結(jié)果：
-- <_x0023_tempStu>
-- <Name>張三</Name>
-- <SubjectName>語文</SubjectName>
-- <Scores>90</Scores>
-- </_x0023_tempStu>
-- <_x0023_tempStu>
-- <Name>李四</Name>
-- <SubjectName>語文</SubjectName>
-- <Scores>100</Scores>
-- </_x0023_tempStu>
-- <_x0023_tempStu>
-- <Name>王五</Name>
-- <SubjectName>語文</SubjectName>
-- <Scores>80</Scores>
-- </_x0023_tempStu>
- FOR XML PATH
- 一行一個(gè)父節(jié)點(diǎn)，每一個(gè)字段作為一個(gè)子節(jié)點(diǎn)
- PATH() 參數(shù)為父節(jié)點(diǎn)名稱，無參則父節(jié)點(diǎn)名稱默認(rèn)為row
- 可以認(rèn)為是FOR XML RAW ,ELEMENT的快捷方法
SELECT Name,
SubjectName,
Scores
FROM #tempStu
FOR XML PATH
-- 結(jié)果：
-- <Student>
-- <Name>張三</Name>
-- <SubjectName>語文</SubjectName>
-- <Scores>90</Scores>
-- </Student>
-- <Student>
-- <Name>李四</Name>
-- <SubjectName>語文</SubjectName>
-- <Scores>100</Scores>
-- </Student>
-- <Student>
-- <Name>王五</Name>
-- <SubjectName>語文</SubjectName>
-- <Scores>80</Scores>
-- </Student>
- 創(chuàng)建根節(jié)點(diǎn)：ROOT
- 字段值作為元素，并添加根節(jié)點(diǎn)
SELECT * FROM #tempStu FOR XML RAW('Stu'),ROOT('Students')
--結(jié)果：
-- <Students>
-- <Stu Name="張三" SubjectName="語文" Scores="90" />
-- <Stu Name="李四" SubjectName="語文" Scores="100" />
-- <Stu Name="王五" SubjectName="語文" Scores="80" />
-- </Students>
- 字段值作為子節(jié)點(diǎn)，并添加根節(jié)點(diǎn)
SELECT Name, SubjectName, Scores FROM #tempStu FOR XML PATH, ROOT('Students')
-- <Students>
-- <row>
-- <Name>張三</Name>
-- <SubjectName>語文</SubjectName>
-- <Scores>90</Scores>
-- </row>
-- <row>
-- <Name>李四</Name>
-- <SubjectName>語文</SubjectName>
-- <Scores>100</Scores>
-- </row>
-- <row>
-- <Name>王五</Name>
-- <SubjectName>語文</SubjectName>
-- <Scores>80</Scores>
-- </row>
-- </Students>
- 字段值作為子節(jié)點(diǎn)，同時(shí)自定義節(jié)點(diǎn)名稱，并添加根節(jié)點(diǎn)
Name AS [Student/Name]：將 Name 列的節(jié)點(diǎn)名稱設(shè)置為 Name，并將其包含在 Student 元素中。
SELECT
Name AS [Student/Name],
SubjectName AS [Student/Subject],
Scores AS [Student/Score]
FROM #tempStu
FOR XML PATH(''), ROOT('Students');
-- <Students>
-- <Student>
-- <Name>張三</Name>
-- <Subject>語文</Subject>
-- <Score>90</Score>
-- </Student>
-- <Student>
-- <Name>李四</Name>
-- <Subject>語文</Subject>
-- <Score>100</Score>
-- </Student>
-- <Student>
-- <Name>王五</Name>
-- <Subject>語文</Subject>
-- <Score>80</Score>
-- </Student>
-- </Students>
- 將XML賦值到XML類型的變量
- 使用場景：將一些記錄創(chuàng)建為XML作為日志保存
DECLARE @xml XML ;
SELECT @xml=(
SELECT Name, SubjectName, Scores FROM #tempStu FOR XML PATH, ROOT('Students')
)
SELECT @xml
1. 列值拼接為字符串
即實(shí)現(xiàn)某一列的列值累加為字符串，這個(gè)需求的實(shí)現(xiàn)方法有多種，可以參考我之前的博文T-SQL——透視PIVOT動(dòng)態(tài)獲取待擴(kuò)展元素集
該文中就是動(dòng)態(tài)的將列值拼接為使用逗號隔開的字符串，
這里通過使用FOR XML PATH('')和STUFF()函數(shù)實(shí)現(xiàn)列值的拼接
DECLARE @subjectStr VARCHAR(100);
WITH cteStudent AS
(
SELECT '張三' AS Name,'語文'AS SubjectName,90 AS Scores
UNION ALL
SELECT '張三' AS Name,'數(shù)學(xué)'AS SubjectName,90 AS Scores
UNION ALL
SELECT '李四' AS Name,'語文'AS SubjectName,100 AS Scores
UNION ALL
SELECT '李四' AS Name,'數(shù)學(xué)'AS SubjectName,100 AS Scores
UNION ALL
SELECT '王五' AS Name,'語文'AS SubjectName,80 AS Scores
)
,Temp AS
(
SELECT DISTINCT SubjectName FROM cteStudent
)
SELECT @subjectStr =STUFF((SELECT ','+SubjectName FROM Temp FOR XML PATH('')),1,1,'')
SELECT @subjectStr
--結(jié)果：
--數(shù)學(xué),語文
2. 字符串轉(zhuǎn)換為列值
將一串使用特定符號分隔的字符，轉(zhuǎn)換為一個(gè)表值。
實(shí)現(xiàn)這個(gè)功能的方法很多，可以參考我之前的博文
這里我們通過XML類型和REPLACE()函數(shù)實(shí)現(xiàn)字符串的分裂
--需要
DECLARE @str VARCHAR(MAX)='1,2,3,4,';
DECLARE @xmlstr XML;
SET ARITHABORT ON;
SET @xmlstr=CONVERT(XML, '<root><v>'+REPLACE(@str, ',', '</v><v>')+'</v></root>');
--SELECT @xmlstr
DECLARE @tableVar TABLE (F1 VARCHAR(100))
INSERT INTO @tableVar
SELECT F1=N.v.value('.', 'varchar(100)') FROM @xmlstr.nodes('/root/v') N(v);
SELECT * FROM @tableVar;
--結(jié)果
F1
--
1
2
3
4
--因?yàn)榇至训淖址Y(jié)尾也有一個(gè)逗號，所以這里是空字符串
上述操作在實(shí)際開發(fā)是中可以簡單的封裝為一個(gè)表值函數(shù)，便于重復(fù)使用
CREATE FUNCTION dbo.funSplitStr
(
@str varchar(1000),--待分裂的字符串
@separator varchar(10)--字符串中分隔使用的符號，比如說'，'
)
RETURNS @tableVar TABLE
(
F1 VARCHAR(100)
)
AS
BEGIN
DECLARE @xmlstr XML;
--SET ARITHABORT ON;
SET @xmlstr = CONVERT(XML, '<root><v>' + REPLACE(@str, @separator, '</v><v>') + '</v></root>');
--SELECT @xmlstr;
INSERT INTO @tableVar
SELECT F1 = N.v.value('.', 'varchar(100)') FROM @xmlstr.nodes('/root/v') N(v);
RETURN;
END;
GO
--測試該函數(shù)
DECLARE @str VARCHAR(100)='1,2,3,4,'
DECLARE @separator VARCHAR(10)=','
SELECT * FROM dbo.funSplitStr(@str,@separator)
--結(jié)果
F1
-------
1
2
3
4
--空字符串
- 示例：分組求和，并將聚合字段值使用“，”保留在一起
---測試數(shù)據(jù)
DECLARE @test TABLE
(
[Name] VARCHAR(4),
[Subject] VARCHAR(4),
[Grade] INT
);
INSERT INTO @test
VALUES
('張三', '語文', 100),
('張三', '數(shù)學(xué)', 90),
('李四', '語文', 90),
('李四', '數(shù)學(xué)', 80),
('李四', '英語', 70);
SELECT * FROM @test
SELECT Name,
Subjects= STUFF( (SELECT ','+Subject FROM @test WHERE Name=T1.Name FOR XML PATH ('')),1,1,'') ,
SUM(Grade) AS TotalGrade
FROM @test AS T1
GROUP BY Name
--結(jié)果：
Name Subjects Grade
----- ---------------- -----
李四 語文,數(shù)學(xué),英語 240
張三 語文,數(shù)學(xué) 190
3. 一些說明
現(xiàn)在開發(fā)中，除了配置文件使用XML，我已經(jīng)很少有需要使用XML的地方了，
SQL Server2000中便存在XML類型，2005中擴(kuò)展了一些針對XML的操作函數(shù)。
而在SQL Server2016中已經(jīng)支持JSON了，添加了許多處理JSON的內(nèi)置函數(shù)。
但是，通過SQL Server中的XML類型處理字符串是及其便利的，如上文中的將字符串串轉(zhuǎn)為列值等。
其用處還體現(xiàn)在一些需要數(shù)據(jù)庫動(dòng)態(tài)生成SQL語句的地方，總而言之就是方便與構(gòu)造一些字符串
這里在舉一個(gè)例子，無實(shí)際的意義，但是可以開闊一下思維。
示例：數(shù)據(jù)庫層面構(gòu)造簡單的JSON字符串
DECLARE @test TABLE
(
[Name] VARCHAR(4),
[Subject] VARCHAR(4),
[Grade] INT
);
INSERT INTO @test
VALUES
('張三', '語文', 100),
('張三', '數(shù)學(xué)', 90),
('李四', '語文', 90),
('李四', '數(shù)學(xué)', 80),
('李四', '英語', 70);
SELECT '['+STUFF((SELECT ',{"name":"'+Name+'","subject":"'+[Subject]+'","grade":"' +CAST(Grade AS VARCHAR(4))+'"}' FROM @test FOR XML PATH ('')),1,1,'') +']'
--結(jié)果：
-- [
-- {
-- "name": "張三",
-- "subject": "語文",
-- "grade": "100"
-- },
-- {
-- "name": "張三",
-- "subject": "數(shù)學(xué)",
-- "grade": "90"
-- },
-- {
-- "name": "李四",
-- "subject": "語文",
-- "grade": "90"
-- },
-- {
-- "name": "李四",
-- "subject": "數(shù)學(xué)",
-- "grade": "80"
-- },
-- {
-- "name": "李四",
-- "subject": "英語",
-- "grade": "70"
-- }
-- ]
浙公網(wǎng)安備 33010602011771號