微软SQL Server 2014更改格式生成的xml。

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

我在SQL Server中使用FOR XML功能，但是我想更改生成的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.

我如何做到这一点?我试着改变“原始”或“路径”的“auto”，但它不起作用。

Greetings, Rafał

问候,Rafał

1 个解决方案

#1

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>