返回格式如
创建临时数据
SELECT IDENTITY(INT,1,1) AS ID,*
INTO #TempTable
FROM
(
SELECT
‘张三‘ AS Name,‘张家长‘ AS Parent,12 AS Age, ‘男‘ AS Sex, ‘+0743 123456‘ AS Phone,‘zhangsan.@xx.com‘ AS Mail
UNION ALL
SELECT ‘李四‘ AS Name,‘李家长‘ AS Parent,13 AS Age,‘男‘ AS Sex,null AS Phone,‘lisi.@xx.com‘ AS Mail
UNION ALL
SELECT ‘王五‘ AS Name,‘王家长‘ AS Parent,12 AS Age,‘男‘ AS Sex,‘+0743 111222‘ AS Phone,null AS Mail
) T
数据处理
DECLARE @Count INT ,@Index INT=1 ,@XmlStr NVARCHAR(MAX)=‘‘,@XmlText XML=‘‘ SELECT @Count=COUNT(1) FROM #TempTable WHILE (@Count>=@Index)
BEGIN DECLARE @Contact XML
–行专列
SET @Contact=
(
SELECT * FROM
(
SELECT
CASE [Type]
WHEN ‘ContactMail‘ THEN ‘EM‘
WHEN ‘ContactPhone‘ THEN ‘TE‘
ELSE ‘‘END AS ‘@Type‘,
[ID] AS ‘@ID‘
FROM
(
SELECT
CASE WHEN ISNULL(Mail,‘‘)<>‘‘ THEN CAST( Mail AS VARCHAR(50)) END AS ContactMail
,CASE WHEN ISNULL(Phone,‘‘)<>‘‘ THEN CAST( Phone AS VARCHAR(50)) END AS ContactPhone
FROM #TempTable
WHERE ID=@Index
) p
UNPIVOT ([ID] FOR [Type] IN (ContactMail,ContactPhone)) AS unpvt
) A FOR XML PATH(‘item‘),Type
) –拼接XML
SET @XmlStr +=
(
SELECT
(
SELECT
name,–节点
(
SELECT
Age AS ‘@Age‘, –节点属性
Sex AS ‘@Sex‘,
(
SELECT
Parent AS ‘@Name‘,
(
SELECT @Contact FOR XML PATH(‘Comm‘),TYPE
)
FOR XML PATH(‘Contact‘),TYPE
)
FOR XML PATH(‘Studentfo‘),Type
)
FOR XML PATH(‘Student‘),Type
)
FROM #TempTable
WHERE ID=@Index
FOR XML PATH(‘Data‘)
)
SET @Index = @Index + 1
END
SELECT @XmlText = @XmlStr
SELECT @XmlText AS XmlText DROP TABLE #TempTable