Sql server. Bidirectional column detection of an XML type column.

Question

In Sql Server, I use an XML type column to store the message. I do not want to keep duplicate messages.

I will only have a few posts for each user. I am currently querying a table for these posts, converting the XML into a string in my C # code. Then I compare the strings with what I'm going to insert.

Unfortunately, Sql Server pretty much prints data in XML-typed fields. What you store in the database does not necessarily match the same line as you later. This is functionally equivalent, but empty space can be removed, etc.

Is there an efficient way to compare the XML string that I consider when pasting with those that are already in the database? Aside, if I find a duplicate, I need to delete the old message, and then insert the replacement.

Answer 1

0 - add hash column to table

1 - when you receive a new message, convert all XML to uppercase, remove all spaces and returns / linefeed, and then calculate the hash value of the normalized string.

2 - check if you already have a string with the resulting hash code.

• If yes, this is duplicated, handle it accordingly,
• If not, save the original XML along with the hash in a new line

Answer 2

100% , -, . , , . ( SQL 2008):

declare @messages table (msg xml)
insert into @messages values 
('<message>You like oranges</message>')
,('<message>You like apples</message>')

declare @newMessage xml = '<message>You like apples</message>'

insert into @messages (msg)
select @newMessage
where @newMessage.value('(message)[1]', 'nvarchar(50)') not in (
  select msg.value('(message)[1]', 'nvarchar(50)')
  from @messages  
)

Answer 3

XML- . XML- varchar.

, p.marino. , -, .

Answer 4

, OPENXML XML- / ? , , OPENXML .