SQL 操作xml

DECLARE @xmlData XML='<Items><Item><Id>1</Id><ProjectNo>11111</ProjectNo><LoanNo>111111111111</LoanNo></Item><Item><Id>2</Id><ProjectNo>22222</ProjectNo><LoanNo>22222</LoanNo></Item></Items>';

/*xml 转DataTable*/
SELECT 
 T.c.value('Id[1]', 'nvarchar(50)') AS Id
,T.c.value('ProjectNo[1]', 'nvarchar(50)') AS ProjectNo
,T.c.value('LoanNo[1]', 'nvarchar(50)') AS LoanNo
 INTO #data
FROM @xmlData.nodes('Items/Item') AS T(c);

/*DataTable 转XML*/
SELECT Id,ProjectNo,LoanNo FROM #data FOR XML PATH('Item'),ROOT('Items'),TYPE