Вставка различных значений из XML в таблицу

У меня есть таблица со сложным первичным ключом. Также у меня есть служба Service Broker, которая получает XML-сообщение, которое в основном выглядит следующим образом:

  <TableName>
    <CHANGED key1="1" key2="2" key3="3" timestamp="00:00:01"/>
    <CHANGED key1="1" key2="2" key3="3" timestamp="00:00:02"/>
    <CHANGED key1="1" key2="2" key3="3" timestamp="00:00:03"/>
  </TableName>

Моя цель - вставить эти значения в таблицу.

Я попробовал следующий запрос:

INSERT INTO TableName (KEY1, KEY2, KEY3, TS)
  SELECT 
      Tbl.Col.value('@*[1]', 'int'),
      Tbl.Col.value('@*[2]', 'int'),
      Tbl.Col.value('@*[3]', 'int'),
      Tbl.Col.value('@*[4]', 'datetime')
  FROM   @MESSAGE.nodes('//CHANGED') Tbl(Col)

Но в случае, когда у нас есть несколько записей с одним и тем же сложным ключом (key1, key2, key3) и разной отметкой времени (значение, которое мне нужно для моей бизнес-логики), этот запрос завершается со следующим сообщением об ошибке:

Нарушение ограничения PRIMARY KEY 'TableName'. Не удается вставить повторяющийся ключ в объект «dbo.TableName». Повторяющееся значение ключа (1, 2, 3).

Есть ли способ вставить только отдельные значения с последней отметкой времени из этого сообщения?


xml sql-server tsql service-broker
person Marian Zagoruiko    schedule 13.04.2012    source источник

Ответы (1)


arrow_upward
2
arrow_downward

Сделайте group by по ключам и агрегируйте по timestamp (минимум или максимум?).

INSERT INTO TableName (KEY1, KEY2, KEY3, TS)
SELECT key1, key2, key3, MIN(ts)
FROM
  (
    SELECT 
      Tbl.Col.value('@key1', 'int') AS key1,
      Tbl.Col.value('@key2', 'int') AS key2,
      Tbl.Col.value('@key3', 'int') AS key3,
      Tbl.Col.value('@timestamp', 'datetime') as ts
    FROM   @MESSAGE.nodes('//CHANGED') Tbl(Col)
  ) AS M
GROUP BY key1, key2, key3

Чтобы быть действительно безопасным, вы должны использовать имена атрибутов вместо position(). Согласно ограничениям типа данных xml порядок атрибутов не гарантируется.

Порядок атрибутов в экземпляре XML не сохраняется. Когда вы запрашиваете экземпляр XML, хранящийся в столбце типа xml, порядок атрибутов в результирующем XML может отличаться от исходного экземпляра XML.

person Mikael Eriksson    schedule 13.04.2012
comment
Да, это именно то, что мне было нужно!!! И спасибо за ваш комментарий об атрибутах, я приму это во внимание. - person Marian Zagoruiko; 13.04.2012