Member 10003929 Ответов: 1

Мне нужно изменить структуру XML, возвращаемого в SQL-запросе

у меня есть следующий запрос 
select r.AccommodationId as 'Property', r.RoomId as 'RoomId' from	BookingEngine_Live.dbo.Rooms r FOR XML RAW ('PropertyDataSet'),ROOT ('Transaction'),ELEMENTS ;

что дает мне следующий результат:

<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>

в то время как мне нужен результат, как показано ниже:

<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. Пожалуйста, проводите. Спасибо.

1 Ответов

Рейтинг:
1

NightWizzard

То FOR XML опция всегда переводит полученную таблицу строка за строкой. То SELECT statemtent, который вы используете, приведет к:

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


Чтобы получить структуру XML по мере необходимости, вы должны создать запрос, который приведет к:
Property            RoomId            RoomID      RoomId
--------------------------------------------------------
1                     51                53         65
2                     51               481  101739578

Это невозможно, потому что вы не можете иметь одно и то же имя столбца более одного раза, даже если вы используете оператор PIVOT, чтобы превратить RoomIds из строк в столбцы.
Вы можете получить RoomIds только на одном уровне в подобном подмножестве:
<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-запросы:
Вложенных запросов for XML-результатов в режиме путь SQL сервера – Ричард Дингуолл[^]