久久久久久久av_日韩在线中文_看一级毛片视频_日本精品二区_成人深夜福利视频_武道仙尊动漫在线观看

TSQL 中無類型 XML 的參照完整性問題

Referential integrity issue with Untyped XML in TSQL(TSQL 中無類型 XML 的參照完整性問題)
本文介紹了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

好吧,看來我的問題有點難,或者我腦子太硬了而變得愚蠢:).我會盡量簡化.

  • NumbersLabels 列中,每個 元素都必須存在于Labels 表中
  • 更新Numbers中的Labels列時,如果發現XML中的Label在Labels表中不存在,必須引發錯誤.
  • XML 是在我的應用程序中預先形成的,這意味著每次運行更新時,NumbersLabels 列中的舊 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

這篇關于TSQL 中無類型 XML 的參照完整性問題的文章就介紹到這了,希望我們推薦的答案對大家有所幫助,也希望大家多多支持html5模板網!

【網站聲明】本站部分內容來源于互聯網,旨在幫助大家更快的解決問題,如果有圖片或者內容侵犯了您的權益,請聯系我們刪除處理,感謝您的支持!

相關文檔推薦

Modify Existing decimal places info(修改現有小數位信息)
The correlation name #39;CONVERT#39; is specified multiple times(多次指定相關名稱“CONVERT)
T-SQL left join not returning null columns(T-SQL 左連接不返回空列)
remove duplicates from comma or pipeline operator string(從逗號或管道運算符字符串中刪除重復項)
Change an iterative query to a relational set-based query(將迭代查詢更改為基于關系集的查詢)
concatenate a zero onto sql server select value shows 4 digits still and not 5(將零連接到 sql server 選擇值仍然顯示 4 位而不是 5)
主站蜘蛛池模板: 国产美女精品 | 天天插天天操 | 成人一区二区三区在线观看 | 精品1区2区 | 国产毛片毛片 | 少妇无套高潮一二三区 | 青青草综合 | 中文字幕亚洲视频 | 日韩综合 | 亚洲精品久久久久中文字幕欢迎你 | 国产精品久久久久不卡 | 97色综合| 麻豆av一区二区三区久久 | 国内精品久久久久久久 | 欧美精品在线视频 | 国产一区二区在线免费观看 | 黄频视频| 久久久久久综合 | 国产99热 | 91免费在线视频 | 日韩精品免费看 | 亚洲综合色视频在线观看 | 国产精品日韩在线观看一区二区 | 国产激情在线观看 | 午夜电影日韩 | 国产在线资源 | 欧亚av在线| 久久精品网 | 国产清纯白嫩初高生视频在线观看 | 男女网站免费观看 | 视频在线一区二区 | 美女国产精品 | 欧美白人做受xxxx视频 | 韩日精品在线观看 | 欧美高清hd| 国产99久久 | 欧美日韩国产中文字幕 | 91久久久www播放日本观看 | 国产精品视频免费观看 | 91麻豆久久久 | 日韩成人精品一区二区三区 |