我需要修改SQL查询中返回的XML结构 [英] I need to modify structure of XML returned in SQL query

问题描述

我有以下查询

select r.AccommodationId as 'Property', r.RoomId as 'RoomId' from	BookingEngine_Live.dbo.Rooms r FOR XML RAW ('PropertyDataSet'),ROOT ('Transaction'),ELEMENTS ; 

给出了以下结果：

which gives me below result:

<transaction>
  <propertydataset>
    <property>1</property>
    <roomid>51</property>
  </propertydataset>
  <propertydataset>
    <property>1</property>
    <roomid>53</property>
  </propertydataset>  
  <propertydataset>
    <property>1</property>
    <roomid>65</property>
  </propertydataset>  
  <propertydataset>
    <property>2</property>
    <roomid>51</property>
  </propertydataset>  
  <propertydataset>
    <property>2</property>
    <roomid>481</property>
  </propertydataset>
  <propertydataset>
    <property>2</property>
    <roomid>101739578</property>
  </propertydataset> 
</transaction>

我需要的结果如下：

while I need the result as below:

<transaction>
  <propertydataset>
    <property>1</property>
    <roomid>51</property>
    <roomid>53</property>
    <roomid>65</property>
  </propertydataset>  
  <propertydataset>
    <property>2</property>
    <roomid>51</property>
    <roomid>481</property>
    <roomid>101739578</property>
  </propertydataset> 
</transaction>

我的尝试：



简单来说，我不想为每个RoomID元素重复属性元素。请指导。谢谢。

What I have tried:

In simple words, I dont want property element to be repeated for every RoomID element. Please guide. Thanks.

推荐答案

FOR XML 选项总是逐行转换结果表。你正在使用的 SELECT statemtent将导致：

The FOR XML option always translates the resulting table row by row. The SELECT statemtent you're using will result in:

Property           RoomId
-------------------------
1                   51
1                   53
1                   65
2                   51
2                   481
2                   101739578

要根据需要获取XML结构，您必须创建一个查询将导致：

To get the XML structure as needed you have to create a query that will result in:

Property            RoomId            RoomID      RoomId
--------------------------------------------------------
1                     51                53         65
2                     51               481  101739578

这是不可能的，因为你不能多次使用相同的列名，即使你使用PIVOT语句来转换RoomIds表单也是如此行到列。

你只能在这样的子集中得到相同级别的RoomIds：

This isn't possible because you can't have the same column name more than once, even if you're using a PIVOT statement to turn the RoomIds form rows to columns.
You only can get the RoomIds at same level in a subset like this:

<transaction>
  <propertydataset>
    <property>1</property>
      <Rooms>
        <roomid>51</property>
        <roomid>53</property>
        <roomid>65</property>
      </Rooms>
  </propertydataset>  
  <propertydataset>
    <property>2</property>
      <Rooms>
        <roomid>51</property>
        <roomid>481</property>
        <roomid>101739578</property>
      </Rooms>
  </propertydataset> 
</transaction>

在此处查看如何创建嵌套XML查询：

使用SQL Server的PATH模式嵌套FOR XML结果 - Richard Dingwall [ ^ ]

这篇关于我需要修改SQL查询中返回的XML结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！