sql-server - SQL Server-将表数据转换为xml

嗨，我有下面的表格数据，需要在SQL Server中转换为XML。有什么想法吗？
提前谢谢
从

Party_Id  HomePhoneNumber  WorkPhoneNumber
62356     6314993578
62356                      6314590922
62356                      6313795488

到

<HomePhoneNumber>6314993578</HomePhoneNumber>
<WorkPhoneNumber>6314590922</WorkPhoneNumber>
<WorkPhoneNumber>6313795488</WorkPhoneNumber>

最佳答案

将空值转换为空值。这些空值将从XML中排除。

Declare @YourTable table (Party_Id int,HomePhoneNumber varchar(25),WorkPhoneNumber varchar(25))
Insert Into @YourTable values
(62356,'6314993578',''),
(62356,'','6314590922'),
(62356,'','6313795488')

Select HomePhoneNumber=case when HomePhoneNumber='' then null else HomePhoneNumber end
      ,WorkPhoneNumber=case when WorkPhoneNumber='' then null else WorkPhoneNumber end
 From  @YourTable
 For   XML Path('')

退换商品

<HomePhoneNumber>6314993578</HomePhoneNumber>
<WorkPhoneNumber>6314590922</WorkPhoneNumber>
<WorkPhoneNumber>6313795488</WorkPhoneNumber>