TSQL 中无类型 XML 的参照完整性问题

问题描述

我将首先显示我的表结构:

数字表:

Id AccountId MobileNr FirstName LastName AttributeKeyValues 标签--- ---------- ----------- ---------- ----------- -------------------- -------490 2000046 2XXXXXXXXXX Eon du Plessis <attrs/><lbs><lbl>Meep11e</lbl><lbl>43210</lbl><lbl>1234</lbl><lbl>标签5</lbl><lbl>标签 6(编辑)</lbl></lbs>-----------------------------------------------------------------------------

标签表:

Id AccountId Label RGB LastAssigned LastMessage----------- ----------- ----------------- ------ ----------------------- -----------91 2000046 Meep11e 000000 2013-04-15 13:42:06.660 NULL-----------------------------------------------------------------------

这是问题

每个数字都可以分配多个标签，并存储为无类型 XML.在 Numbers.Labels //lbls/lbl/text() 中，您会注意到那里的文本将与 Labels.Label<中的文本匹配/p>

这是更新 Numbers.Labels 列的存储过程，由我正在编写的外部应用程序运行.XML 结构由此外部应用程序生成，具体取决于在 Labels.Label 表

创建程序 [dbo].[UpdateLabels]@Id INT，@Labels XML作为开始更新数字放标签 = @标签在哪里Id = @Id更新标签放最后分配 = GETDATE()在哪里标签在(SELECT @Labels.value('(//lbls/lbl)[1]', 'VARCHAR(100)'))结尾

这里的问题是，如果 2 个人使用他们自己的会话登录同一个帐户，并且用户 1 尝试运行此更新存储过程，但就在按下按钮进行此更新之前，用户 2 删除了其中的 1 个用户 1 的更新会话中包含的 Labels.label 表中的标签，它会导致 XML 包含已删除"行，并且当我再次尝试查询数字时可能会出现问题(RGB 列在我显示数字时被查询，因为标签在 jQuery 中被标记为具有十六进制彩色背景)

我的想法是在提交更新之前检查构建的 XML 中包含的行是否存在.我怎样才能在 TSQL 中实现这一点?或者有什么更好的方法可以推荐?

编辑

我们的表结构是故意非规范化的，没有外键约束.

编辑 2

好吧，看来我的问题有点难，或者我脑子太硬了而变得愚蠢:).我会尽量简化.

• 在Numbers 的Labels 列中，每个 元素都必须存在于Labels 表中
• 更新Numbers中的Labels列时，如果发现XML中的Label在Labels表中不存在，必须引发错误.
• XML 是在我的应用程序中预先形成的，这意味着每次运行更新时，Numbers 中 Labels 列中的旧 XML 将被替换为我的应用程序生成的新 XML
• 这是我需要检查我的 XML 中是否存在 Labels 表中不再存在的标签节点

在尝试任何操作之前，我会检查您的 xml 中是否有不在真实表(在数据库中)中的行.如果你发现了什么，早点退出.

这是一个 Northwind 的例子.

使用北风走声明@data XML;设置@数据=N'<根><订单><OrderId>10248</OrderId><CustomerId>VINET</CustomerId></订单><订单><OrderId>-9999</OrderId><CustomerId>CHOPS</CustomerId></订单>';/* 从 dbo.Orders 中选择 * */声明@Holder 表( OrderId int, CustomerId nchar(5) )插入到@Holder (OrderId , CustomerId )选择T.myAlias.value('(./OrderId)[1]', 'int') AS OrderId, T.myAlias.value('(./CustomerId)[1]', 'nchar(5)') AS CustomerId从@data.nodes('//root/Order') AS T(myAlias);如果存在(从不存在的@Holder h 中选择空值(从 dbo.Orders realTable 中选择空值，其中 realTable.OrderID = h.OrderId ))开始打印 '你的 xml 中有不在真实表中的行.在这里引发错误'结尾别的开始打印使用数据"更新 dbo.Orders 设置 CustomerID = h.CustomerId来自 dbo.Orders o ，@Holder h其中 o.OrderID = h.OrderId结尾

I am going to start off by displaying my table structures:

Numbers Table:

Id  AccountId  MobileNr    FirstName  LastName    AttributeKeyValues  Labels
--- ---------- ----------- ---------- ----------- ------------------- -------
490 2000046    2XXXXXXXXXX Eon        du Plessis  <attrs />           <lbls>
                                                                        <lbl>Meep11e</lbl>
                                                                        <lbl>43210</lbl>
                                                                        <lbl>1234</lbl>
                                                                        <lbl>Label 5</lbl>
                                                                        <lbl>Label 6 (edit)</lbl>
                                                                      </lbls>
-----------------------------------------------------------------------------

Labels Table:

Id          AccountId   Label             RGB    LastAssigned            LastMessage
----------- ----------- ----------------- ------ ----------------------- ------------
91          2000046     Meep11e           000000 2013-04-15 13:42:06.660 NULL
-------------------------------------------------------------------------------------

This is the issue

Every number can have multiple labels assigned to it and is stored as untyped XML. In Numbers.Labels //lbls/lbl/text() you will notice that the text there will match the text in Labels.Label

This is the stored procedure which updates the Numbers.Labels column, and is run by an external application I am busy writing. The XML structure is generated by this external application, depending on which rows are read in the Labels.Label table

CREATE PROCEDURE [dbo].[UpdateLabels]
    @Id     INT,
    @Labels XML
AS
BEGIN
    UPDATE 
        Numbers
    SET
        Labels = @Labels
    WHERE
        Id = @Id

    UPDATE 
        Labels
    SET
        LastAssigned = GETDATE()
    WHERE
        label
    IN
        (SELECT @Labels.value('(//lbls/lbl)[1]', 'VARCHAR(100)'))
    END

The issue here is if 2 people log onto the same account, both with their own session, and User 1 tries to run this update stored procedure, but just before the button is pressed to do this update, user 2 deletes 1 of the labels in the Labels.label table which was included in User 1's update session, it will cause the XML to include the "Deleted" row, and can be problematic when I try to query the numbers again (The RGB column gets queried when I display the number since the label is marked up in jQuery to have a hexidecimal colored background)

My thought approach went to checking if the rows included in the built up XML exists before committing the update. How can I achieve this in TSQL? Or can any better way be recommended?

EDIT

Our table structure is intentionally denormalized, there are no foreign key constraints.

EDIT 2

Ok, it would seem my question is a bit hard, or that I brained too hard and got the dumb :). I will try and simplify.

• In the Labels column in Numbers, every <lbl> element must exist within the Labels table
• When updating the Labels column in Numbers, if a Label in the XML is found which does not exist in the Labels table, an error must be raised.
• The XML is pre-formed in my application, meaning, every time the update is run, the old XML in the Labels column in Numbers will be REPLACED with the new XML generated by my application
• This is where I need to check whether there are label nodes in my XML which no longer exists within the Labels table

I would check to see if there are rows in your xml that are not in the real table (in the database) before trying anything. And if you find something, exit out early.

Here is a Northwind example.

Use Northwind
GO

DECLARE @data XML;

SET @data = 

N'
<root>
    <Order>
        <OrderId>10248</OrderId>
        <CustomerId>VINET</CustomerId>
    </Order>
    <Order>
        <OrderId>-9999</OrderId>
        <CustomerId>CHOPS</CustomerId>
    </Order>
</root>';


/* select * from dbo.Orders */

declare @Holder table ( OrderId int, CustomerId nchar(5) )

Insert Into @Holder (OrderId , CustomerId )
SELECT
      T.myAlias.value('(./OrderId)[1]', 'int') AS OrderId
    , T.myAlias.value('(./CustomerId)[1]', 'nchar(5)') AS CustomerId
FROM 
    @data.nodes('//root/Order') AS T(myAlias);


if exists (select null from @Holder h where not exists (select null from dbo.Orders realTable where realTable.OrderID = h.OrderId )) 
BEGIN
    print 'you have rows in your xml that are not in the real table.  raise an error here'
END
Else
BEGIN
    print 'Using the data'
    Update dbo.Orders Set CustomerID = h.CustomerId
    From dbo.Orders o , @Holder h
    Where o.OrderID = h.OrderId
END