CODEWITHSUNDEEP
sql-serverxml
Hilo
Hilo

Reputation: 869

FOR XML PATH with duplicate column names

I am trying to get the desired output from MS SQL Server 2012 using FOR XML PATH.

My query is basically this:

select field1 as fields, field2 as fields, field3 as fields 
from mytable 
for xml path

which yields this xml: (for values 0,3,7 in the fieldN columns)

<row>
    <fields>037</fields>
</row>

The output I desire is this:

<row>
    <fields>0</fields>
    <fields>3</fields>
    <fields>7</fields>
</row>

Is this possible?

Upvotes: 0

Views: 558

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81930

Declare @YourTable table (Field1 int,Field2 int,Field3 int)
Insert Into @YourTable values (0,3,7),(8,9,10)

Select [fields] = Field1
      ,''
      ,[fields] = Field2
      ,''
      ,[fields] = Field3
 From  @YourTable
 For   XML Path('row')

Returns

<row>
  <fields>0</fields>
  <fields>3</fields>
  <fields>7</fields>
</row>
<row>
  <fields>8</fields>
  <fields>9</fields>
  <fields>10</fields>
</row>

Upvotes: 5

Related Questions