我正在嘗試為此任務找到解決方案：撰寫一個接受以下格式的 XML 檔案的程序：
<Buyer N="John" S="Doe" CTY="Madrid" CTR="Spain">
<E>[email protected]</E>
<T>123456789</T>
</Buyer>
該程式必須驗證您的資料庫是否已經包含國家、城市或買家（通過電子郵件檢查買家）。
如果您的資料庫中缺少任何資料，您的程序必須將其插入到表中。
呼叫顯示上述程序功能的函式。
這就是我開始分離所有重要事物的方式：
CREATE PROCEDURE pXML
@var XML
AS
SELECT
X.U.value('@N', 'nvarchar(20)') AS pName,
X.U.value('@S', 'nvarchar(20)') AS pSurename,
X.U.value('@CTY', 'nvarchar(20)') AS pCity,
X.U.value('@CTR', 'nvarchar(20)') AS pCountry
FROM
@var.nodes('/Buyer') AS X(U)
SELECT X.U.value('.', 'nvarchar(50)' ) AS pEmail
FROM @var.nodes('/Buyer/E') as X(U)
我不知道如何在該程序中放置?? IF 陳述句或任何必要的條件。
我的偽代碼是：
declare @temp nvarchar(30)
set @temp = pEmail
IF NOT EXISTS(select * from Buyers where Buyer.Email = pEmail)
INSERT INTO Buyer values (pName, pSurename, pCity, pCountry)
(more IF NOT EXISTS statements for city and for country)
有人愿意在這里幫我一把嗎？
提前致謝！告訴我，如果不夠清楚，我會盡力添加任何需要的資訊。
uj5u.com熱心網友回復：
請嘗試以下解決方案。
它是使用MERGE陳述句來處理INSERT或UPDATE基于電子郵件。
SQL
-- DDL and sample data population, start
DECLARE @Buyer TABLE (
ID INT IDENTITY PRIMARY KEY,
pEmail NVARCHAR(100),
pName nvarchar(20),
pSurename nvarchar(20),
pCity nvarchar(20),
pCountry nvarchar(20)
);
INSERT INTO @Buyer (pEmail, pName, pSurename, pCity, pCountry) VALUES
(N'[email protected]', N'John', N'Doe', N'Madrid', N'Spain');
-- DDL and sample data population, end
-- before
SELECT * FROM @Buyer;
DECLARE @var XML =
N'<Buyer N="Johnny" S="Doe" CTY="Barcelona" CTR="Spain">
<E>[email protected]</E>
<T>123456789</T>
</Buyer>';
MERGE INTO @Buyer as Trg
USING (
SELECT c.value('(E/text())[1]', 'nvarchar(100)') as pEmail
, c.value('@N', 'nvarchar(20)') as pName
, c.value('@S', 'nvarchar(20)') as pSurename
, c.value('@CTY', 'nvarchar(20)') as pCity
, c.value('@CTR', 'nvarchar(20)') as pCountry
FROM @var.nodes('/Buyer') AS t(c)
) as Src
ON Trg.pEmail = Src.pEmail
WHEN Matched /*AND Src.pName IS NOT NULL*/ THEN -- if needed to add additional conditions
UPDATE
SET Trg.pName = Src.pName
, Trg.pSurename = Src.pSurename
, Trg.pCity = Src.pCity
, Trg.pCountry = Src.pCountry
WHEN NOT MATCHED THEN
INSERT (pEmail, pName, pSurename, pCity, pCountry) VALUES
(Src.pEmail, Src.pName, Src.pSurename, Src.pCity, Src.pCountry)
OUTPUT
$action ,
inserted.*;
-- after
SELECT * FROM @Buyer;
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/508598.html
上一篇：使用ARKit時如何減少發熱？