–kolumna jako atrybut
SELECT * FROM Production.Categories
FOR XML AUTO
–element i kolumna zagnieżdżone
SELECT * FROM Production.Categories
FOR XML PATH
SELECT * FROM Production.Categories
FOR XML PATH (’Category’)
SELECT * FROM Production.Categories
FOR XML PATH (’Category’), ROOT(’Categories’)
SELECT categoryid as [@ID],
categoryname AS [Category/Name],
description AS [Category/Description]
FROM Production.Categories
FOR XML PATH (’Category’), ROOT(’Categories’)
SELECT categoryid as [@ID],
categoryname AS [Category/@Name],
description AS [Category/Description]
FROM Production.Categories
FOR XML PATH (’Category’), ROOT(’Categories’)
–problemy z zabezpieczeniami
EXEC xp_cmdshell 'bcp „SELECT * FROM Production.Categories
FOR XML PATH (”Category”), ROOT(”Categories”)” queryout „C:\Gastro\Categories.xml” -c -T’
DECLARE @categories xml
SELECT @categories = C
FROM OPENROWSET (BULK 'c:\Gastro\Categories.xml’, SINGLE_BLOB) AS Categories(C)
–SELECT @categories
SELECT tab.item.value(’categoryname[1]’, 'Varchar(255)’),
tab.item.value(’description[1]’, 'Varchar(255)’)
FROM @categories.nodes(’//Categories/Category’) AS tab(item)
DECLARE @hdoc int
EXEC sp_xml_preparedocument @hdoc OUTPUT, @categories
SELECT *
FROM OPENXML (@hdoc, '/Categories/Category’ , 2)
WITH(
categoryname VARCHAR(20),
description VARCHAR(20)
)
EXEC sp_xml_removedocument @hdoc
SELECT
myXML.Category.query(’categoryname’).value(’.’, 'VARCHAR(20)’),
myXML.Category.query(’description’).value(’.’, 'VARCHAR(50)’)
FROM (SELECT CAST(myXML AS xml)
FROM OPENROWSET(BULK 'c:\Gastro\Categories.xml’, SINGLE_BLOB) AS T(myXML)) AS T(myXML)
CROSS APPLY myXML.nodes(’/Categories/Category’) AS myXML (Category);
–MERGE <target_table> [AS TARGET]
–USING <table_source> [AS SOURCE]
–ON <search_condition>
–[WHEN MATCHED
–THEN <merge_matched> ]
–[WHEN NOT MATCHED [BY TARGET]
–THEN <merge_not_matched> ]
–[WHEN NOT MATCHED BY SOURCE
–THEN <merge_matched> ];
DROP TABLE StudentExam
DROP TABLE Student
CREATE TABLE Student
(
ID INTEGER PRIMARY KEY,
Name VARCHAR(15),
FlgDeleted BIT DEFAULT 0
)
GO
INSERT INTO Student
VALUES
(1,’Adam Nowak’, 0),
(2,’Ewa Kowalska’, 0),
(3,’Dominik Iks’, 0),
(4,’Tomasz Igrek’, 0),
(5,’Robert Wu’, 0)
CREATE TABLE StudentExam
(
StudentID INTEGER REFERENCES Student,
Points INTEGER,
FlgPassed BIT default 0
)
INSERT INTO StudentExam
VALUES
(1,55,0),
(2,64,0),
(3,86,0)
UPDATE Student SET FlgDeleted = 1 WHERE ID = 2
SELECT s.Name
,se.Points
,se.FlgPassed
,s.FlgDeleted
FROM dbo.StudentExam AS se
JOIN dbo.Student AS s ON s.ID = se.StudentID
MERGE StudentExam AS target
USING (SELECT ID, Name, FlgDeleted FROM Student) AS source
ON target.StudentID = source.ID
WHEN MATCHED AND source.FlgDeleted = 1 THEN DELETE
WHEN MATCHED AND target.Points > 80 THEN UPDATE SET target.FlgPassed = 1
WHEN NOT MATCHED THEN INSERT(StudentID, Points, FlgPassed) VALUES(source.ID, 0, 0);
SELECT s.Name
,se.Points
,se.FlgPassed
,s.FlgDeleted
FROM dbo.StudentExam AS se
JOIN dbo.Student AS s ON s.ID = se.StudentID
–;WITH expression_name (column_list)
–AS
–(
— — Anchor member
— initial_query
— UNION ALL
— — Recursive member that references expression_name.
— recursive_query
–)
—- references expression name
–SELECT *
–FROM expression_name
/*
select 1
union –union all
select 2
select 1
union all
select 1
select 1
union
select 1
*/
;WITH cte_numbers(n, weekday)
AS (
SELECT
0,
DATENAME(DW, 0)
UNION ALL
SELECT
n + 1,
DATENAME(DW, n + 1)
FROM
cte_numbers
WHERE n < 6
)
SELECT
n, weekday
FROM
cte_numbers;
IF OBJECT_ID(’tempdb..#Person’) IS NOT NULL
DROP TABLE #Person
CREATE TABLE #Person
(
ID INT PRIMARY KEY NOT NULL,
mgrID INT NULL,
Name VARCHAR(50) NOT NULL,
BirthDate DATETIME NULL
)
INSERT INTO #Person (ID, mgrID ,Name,BirthDate)
VALUES
(1,NULL,’Adam Nowak’,’1964-12-12′),
(2,1,’Jerzy Kowalski’,’1973-05-26′),
(3,2,’Anna Kozłowska’,’1974-05-16′),
(4,3,’Maria Kowalik’,’1964-06-11′),
(5,3, 'Robert Kowal’,’1978-11-13′),
(6,1,’Kamila Kowal’,’1973-02-23′),
(7,NULL,’Szef 2′,’1964-12-12′),
(8,5,’Ewa Iks’,’1973-02-21′)
;WITH personCTE AS (
SELECT ID,
mgrID,
Name,
BirthDate,
Level = 0
FROM #Person
WHERE mgrID IS NULL
UNION ALL
SELECT e.ID,
e.mgrID,
e.Name,
e.BirthDate,
ecte.Level + 1
FROM #Person e
INNER JOIN personCTE ecte ON ecte.ID = e.mgrID
)
SELECT *
FROM personCTE ORDER BY Level
OPTION (MaxRecursion 0)
–bez rekurencji do skomplikowanych zapytań często poprawia wydajność
;WITH OrderCTE (OrderID, customerID, shipperid)
AS
(
SELECT OrderID,
custid,
shipperid
FROM Sales.Orders
)
,
ShipperCTE (shipperid, companyname)
AS
(
SELECT shipperid,
companyname
FROM Sales.Shippers
)
SELECT orderid,
customerid,
companyname
FROM OrderCTE
INNER JOIN ShipperCTE ON OrderCTE.shipperid = ShipperCTE.shipperid;