CODEWITHSUNDEEP
sqlsql-serverxmlt-sql
Rafalsonn
Rafalsonn

Reputation: 440

Microsoft SQL Server 2014 change format of generated xml

I'm using the FOR XML functionality in SQL Server, but I want to change the format of the resulting xml.

This is my query:

SELECT * 
FROM dbo.myTable AS row 
FOR XML RAW, ELEMENTS, ROOT('rows')

The result is: 

<rows>
    <row>
       <name>A Time to Kill</name>
       <author>John Grisham</author>
       <price>12.99</price>
    </row>
    <row>
       <name>Blood and Smoke</name>
       <author>Stephen King</author>
       <price>10</price>
    </row>
</rows>

but the result I want is:

<rows> 
    <row id="1"> 
        <cell>A Time to Kill</cell> 
        <cell>John Grisham</cell> 
        <cell>12.99</cell> 
    </row>
    <row id="2"> 
        <cell>Blood and Smoke</cell> 
        <cell>Stephen King</cell> 
        <cell>10</cell> 
    </row>
</rows>

How can I achieve this? I've tried to change "auto" for "raw" or "path" but it didn't work.

Greetings, Rafał

Upvotes: 2

Views: 65

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81970

Declare @YourTable table (id int,name varchar(50),author varchar(50),price money)
Insert Into @YourTable values 
 (1,'A Time to Kill','John Grisham',12.99)
,(2,'Blood and Smoke','Stephen King',10)

Select [@id]  = id
      ,[cell] = name
      ,null
      ,[cell] = author
      ,null
      ,[cell] = price
 From  @YourTable
 For   XML Path('row'),root('rows')

Returns

<rows>
  <row id="1">
    <cell>A Time to Kill</cell>
    <cell>John Grisham</cell>
    <cell>12.9900</cell>
  </row>
  <row id="2">
    <cell>Blood and Smoke</cell>
    <cell>Stephen King</cell>
    <cell>10.0000</cell>
  </row>
</rows>

Upvotes: 4

Related Questions