SqlServer: xml与表互相转换

DECLARE @t TABLE (
	cid INT,
	num INT,
	d   DATE	
)
INSERT INTO @t VALUES(1,23,'2021-11-10'),(2,45,'2021-11-11');
-------- 以上为测试数据 -------

--1. 表转xml
SELECT * FROM @t FOR XML PATH('row')
--<row><cid>1</cid><num>23</num><d>2021-11-10</d></row><row><cid>2</cid><num>45</num><d>2021-11-11</d></row>

--表转xml，并存入变量中
DECLARE @x XML
SET @x=(SELECT * FROM @t FOR XML PATH('row'))

--2. xml转表
SELECT 
T.C.value('cid[1]','int') AS cid,
T.C.value('num[1]','int') AS num,
T.C.value('d[1]','date') AS d
FROM @x.nodes('//row') AS T(C)
/*
cid	num	d
1	23	2021-11-10
2	45	2021-11-11
*/