CODEWITHSUNDEEP
sql-serverxml
user1869407
user1869407

Reputation: 227

What is the best way to get a unique ID an element in XML

Here is my description of my system and constraints. I have a MSSQL database that store xml files. These files are for use for my end users in my Windows Store App. Basically, a user creates a device configuration using this GUI's application, its converted to xml, and uploaded to the database. Each one of these xml configs represents a representation of a device configuration. I want to be able to assign a unique id for each unique configuration that will be consistent among all my users. This means if someone creates an identical device configuration using the app as someone else that uses the app that the XML unique ID for the device configuration will be the same. This is to say that the constituent parts that ultimately make the device configuration is what can be identified through one single value that I can store.

How do I go about creating this unique ID? What function can be run when creating my XML file to make is unique ID? Please describe the process for doing this.

Upvotes: 2

Views: 721

Answers (2)

nathan_jr
nathan_jr

Reputation: 9292

Calculate a checksum over the xml document and index that so your future existence checks are fast.

Note, xml can be "equivalent" but not be equal as a string. If you only have one producer of the xml this might be a non-issue for you.

Heres a simple example:

create table dbo.Devices 
(
    DeviceId int identity(1,1) primary key, 
    Configuration xml, 
    ConfigurationChecksum as binary_checksum(cast(Configuration as varchar(max))),

    constraint ck_Configuration unique(ConfigurationChecksum)
);
go

--insert new (OK)
insert into dbo.Devices(Configuration)
    values('<root><Config>123</Config></root>');
--insert new (OK)
insert into dbo.Devices(Configuration)
    values('<root><Config>456</Config></root>');

-- insert existing (Violation of UNIQUE KEY constraint 'ck_Configuration')
insert into dbo.Devices(Configuration)
    values('<root><Config>456</Config></root>');




--check if exists (lookup will seek on our ck_Configuration index)
declare @x xml = '<root><Config>789</Config></root>'
declare @i int;

if exists(select 1 from dbo.Devices where ConfigurationChecksum = binary_checksum(cast(@x as varchar(max))))
begin 
    raiserror('Already exists!', 10, 1) with nowait;
end else
begin
    insert into dbo.Devices(Configuration)
        values(@x);
    set @i = scope_identity();
    raiserror('Created new config %d', 10, 1, @i) with nowait;
end

As mentioned by Shnugo, this will only work if the XML documents are exactly the same. If you need to constrain a subset of the XML document then you can still use the same strategy, but the checksum needs to be calculated over a subset of elements:

declare @x xml = '<root><Name>ABC</Name><Port>80</Port><Created>2001-01-01</Created></root>'
select @x.query('/*/*[local-name()=("Name","Port")]'), binary_checksum(cast(@x.query('/*/*[local-name()=("Name","Port")]') as varchar(max)))

To do that you need to wrap in a function and reference that from your constraint.

Upvotes: 2

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

Easiest would be to do a simple concatenation of all relevant values,such as value1|value2|value3|value4 All these values in the given order must be unique.

If you want, you could either

  • calculate a Hash-value for the given string which should be unique (or at least it is expectable) or
  • you add a VARCHAR(XXX) column to your table with the XML to store the concatenated string and let this be unique. This makes sure you never insert a given configuration twice. If you want you could store a GUID (NEWID()) or an IDENTITY value to have an easy key to handle...

Upvotes: 1

Related Questions