SQL Server XML queries

Question

I'm trying to find a solution for this task: write a procedure that accepts XML document in the following format:

<Buyer N="John" S="Doe" CTY="Madrid" CTR="Spain">
   <E>[email protected]</E> 
   <T>123456789</T> 
</Buyer>

The procedure must verify if your database already contains the country, city or buyer (check buyer by email).

If any data is missing in your database, your procedure has to insert it into the table.

Call the functions that show the mentioned procedure functionalities.

This is how I started to separate all the important things:

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)

What I don't know is how to put IF statement, or whatever condition is necessary INSIDE that procedure.

My pseudo code would be:

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)

Is anyone willing to give me a hand here?

Thanks in advance! Tell me if didn't clarify enough, I'll do my best to add any needed info.

While asking a question, you need to provide a minimal reproducible example: (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). — Yitzhak Khabinsky
– Yitzhak Khabinsky, Commented Sep 15, 2022 at 13:16

Yitzhak Khabinsky · Accepted Answer · 2022-09-15 14:13:28Z

Please try the following solution.

It is using MERGE statement to handle INSERT or UPDATE based on the email.

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;

Collectives™ on Stack Overflow

SQL Server XML queries

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related