CODEWITHSUNDEEP
sql-serverxmlt-sql
user481779
user481779

Reputation: 1081

SQL Server - Convert table data to xml

Hi I have the following table data I need to convert to Xml in SQl Server. Any ideas?

Thanks in advance

From

Party_Id  HomePhoneNumber  WorkPhoneNumber
62356     6314993578    
62356                      6314590922
62356                      6313795488

To

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

Upvotes: 3

Views: 100

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 82010

Convert the empty values into NULLs. These NULL values will be excluded from the 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('')

Returns

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

Upvotes: 5

Related Questions