xml20316

use TSQL2012;
GO
/*1. Utworzyć XML (wyeksportować dane) na podstawie danych o produktach z tabeli Production.Products zawierające
productname, z category name, z supplier companyname i dodatkowo dane o liczbie zamówień dla tego produktu oraz wartości tych zamówień.*/
SELECT pp.productname AS [ProductName],
       pc.categoryname AS [Category],
       ps.companyname AS [Supplier],
       sum(sod.qty) AS [ProductInfo/CountProduct],
       sum(sod.qty*sod.unitprice) AS [ProductInfo/FullPrice]
FROM Production.Products pp, Production.Categories pc, Production.Suppliers ps, Sales.OrderDetails sod
WHERE pp.categoryid = pc.categoryid AND pp.supplierid = ps.supplierid AND sod.productid = pp.productid
GROUP BY pp.productname, pc.categoryname, ps.companyname
ORDER BY pp.productname
FOR XML PATH ('Product'), ROOT ('Products')
/*2. Zaimportować dane produktów na podstawie XML (z informacjami jak w zad. 1 oczywiście zignorować liczbę i wartość zamówień).
Gdy productname jest w bazie zrobić update rekordu używając nowych danych z XML, w przeciwnym wypadku dodać nowy produkt (można zastosować MERGE).
Gdy nie ma companyname w Production.Suppliers zignorować dodanie (aktualizację) produktu.
Gdy nie ma categoryname w Production.Categories dodać nową kategorię z dodaniem (aktualizacją) produktu.*/
DECLARE @products xml
SELECT @products = P
FROM OPENROWSET (BULK 'C:\Users\alexa\OneDrive\Документы\SQL Server Management Studio\ProductsZ2.xml', SINGLE_BLOB) AS Products(P)
SELECT @products

CREATE TABLE MyProducts(
    ProductName VARCHAR(30),
    CategoryName VARCHAR(30),
    SupplierName VARCHAR(30)
);

DECLARE @hdoc INT
EXEC sp_xml_preparedocument @hdoc OUTPUT, @products
INSERT INTO MyProducts (ProductName, CategoryName, SupplierName)
SELECT ProductName, Category, Supplier  FROM OPENXML (@hdoc, '/Products/Product', 2)
WITH(
    ProductName NVARCHAR(30),
    Category NVARCHAR(30),
    Supplier NVARCHAR(30)
    )
EXEC sp_xml_removedocument @hdoc
SELECT*FROM MyProducts
--------------------------------------------------------------------------------------------------
--category table merge
DECLARE @idcat INT = 0
MERGE Production.Categories AS target
USING (SELECT CategoryName FROM MyProducts) AS source
ON (target.categoryname = source.CategoryName)
WHEN NOT MATCHED
    THEN INSERT VALUES(source.CategoryName, 'New description num.'+cast((@idcat+1) AS NVARCHAR));

select*from Production.Categories
-----------------------------------------------------------------------------------------------------------
--products table merge
MERGE Production.Products AS target
USING (SELECT * FROM MyProducts) AS source
ON (TARGET.productname = source.ProductName)
WHEN MATCHED AND EXISTS (SELECT supplierid from Production.Suppliers where source.SupplierName = companyname)
    THEN
        UPDATE SET  target.supplierid = (SELECT supplierid FROM Production.Suppliers s WHERE source.SupplierName = s.companyname),
                    target.categoryid = (SELECT categoryid FROM Production.Categories c WHERE source.CategoryName = c.categoryname)
WHEN NOT MATCHED AND EXISTS (SELECT supplierid from Production.Suppliers where source.SupplierName = companyname)-- AND supplierid IS NOT NULL) /*(SELECT FROM WHERE p.supplierid = ) IS NOT NULL*/
    THEN
        INSERT VALUES (source.ProductName,
                       (SELECT supplierid FROM Production.Suppliers s WHERE source.SupplierName = s.companyname),
                       (SELECT categoryid FROM Production.Categories c WHERE source.CategoryName = c.categoryname),100,1);

SELECT*FROM Production.Products
SELECT*FROM MyProducts
-----------------------------------------------------------------------------------------------------------------------------------
DROP TABLE MyProducts;