CODEWITHSUNDEEP
sql-serverxmlt-sqlservice-broker
Marian Zagoruiko
Marian Zagoruiko

Reputation: 1596

Inserting distinct values from XML into table

I have a table with a complex primary key. Also I have a Service Broker service which receives XML message which basically looks like the following:

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

My goal is to insert those values into a table.

I tried the following query:

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)

But in case when we have several records with the same complex key (key1, key2, key3) and different timestamp (value I need for my biz logic) this query fails with the following error message:

Violation of PRIMARY KEY constraint 'TableName'. Cannot insert duplicate key in object 'dbo.TableName'. The duplicate key value is (1, 2, 3).

Is there a way to insert only distinct values with the latest timestamp from that message?

Upvotes: 1

Views: 751

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138990

Do a group by on the keys and aggregate on timestamp (min or max?).

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

To be really safe you should use the attribute names instead of position(). According to Limitations of the xml Data Type the order of attributes is not guaranteed.

The order of attributes in an XML instance is not preserved. When you query the XML instance stored in the xml type column, the order of attributes in the resulting XML may be different from the original XML instance.

Upvotes: 2

Related Questions