Category Archives: T-SQL

Editing XML data in SQL Server

Anything to do with handling XML data can be tricky in SQL Server.  Here are some examples of how to insert/update/delete elements.  I have chosen to use the sql:variable to demonstrate how dynamic elements can be used in these functions.

 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
-- DECLARE A BASIC XML DOCUMENT
DECLARE @xml XML 
SET @xml = 
'<root>
<people>
	<person name="richard">
		<age>20</age>
	</person>
	<person name="michael">
		<age>25</age>
	</person>
	<person name="davidson">
		<age>30</age>
	</person>
</people>
</root>';


DECLARE  @name VARCHAR(20) = 'michael'
		,@age VARCHAR(20) = '100000'

SELECT @xml.value('((/root/people/person)[@name = sql:variable("@name")]/age)[1]','varchar(20)') as SelectVariable

-- 1. Update the age
SET @xml.modify('
	replace value of ((/root/people/person)[@name = sql:variable("@name")]/age/text())[1]
	with sql:variable("@age")
')

SELECT @xml AS AgeUpdated

-- 2. Delete
SET @xml.modify('
	delete (/root/people/person[age/text() = sql:variable("@age")])[1]
')

-- New XML to insert
DECLARE @insert XML = '<person><age>41</age></person>'


SET @xml.modify('
	insert sql:variable("@insert")
	after (/root/people/person)[1]
')

SET @insert = '<person><age>40</age></person>'

SET @xml.modify('
	insert sql:variable("@insert")
	after (/root/people/person)[1]
')

SET @insert = '<person><age>39</age></person>'

SET @xml.modify('
	insert sql:variable("@insert")
	after (/root/people/person)[1]
')

SELECT @xml AS AfterAdditions

 

Window functions: referencing an indeterminate number of rows above

Problem:

You have a table of data where you want rows with a dummy value in a field to display the value in the row above.  You could use lag – but what if the row above had a dummy value too?  In that case you want it to look at the next row up.  And so on until it found the value you wanted…

Solution:

Utilise the aggregates with window functions to identify groups so that the non-dummy values above persist down the rows.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
IF OBJECT_ID(N'tempdb..#CreditHistory') IS NOT NULL DROP TABLE #CreditHistory;

CREATE TABLE #CreditHistory(
	 id				INT			IDENTITY(1,1)	NOT NULL
	,[name]			VARCHAR(20)					NOT NULL	
	,[status]		CHAR(1)						NULL
	,[dtRecorded]	DATE						NOT NULL
)


INSERT INTO #CreditHistory([name],[status],[dtRecorded])
VALUES  ('dave',	'5', '20170101')
	   ,('dave',	'5', '20170201')
	   ,('dave',	'5', '20170301')
	   ,('dave',	'4', '20170401')
	   ,('dave',	'I', '20170501')
	   ,('dave',	'I', '20170601')
	   ,('dave',	'I', '20170701')
	   ,('dave',	'I', '20170801')
	   ,('dave',	'0', '20170901')
	   ,('dave',	'I', '20171001')
	   ,('dave',	'1', '20171101')
	   ,('dave',	'2', '20171201')
	   ,('mike',	'I', '20170101')
	   ,('mike',	'1', '20170201')
	   ,('mike',	'2', '20170301')
	   ,('mike',	'3', '20170401')
	   ,('mike',	'4', '20170501')
	   ,('mike',	'I', '20170601')
	   ,('mike',	'I', '20170701')
	   ,('mike',	'1', '20170801')
	   ,('mike',	'2', '20170901')
	   ,('mike',	'I', '20171001')
	   ,('mike',	'I', '20171101')
	   ,('mike',	'0', '20171201')
	   
/*=================================================================================================
STEP 1:
		Use the CTE to identify the groups that each record falls into.
=================================================================================================*/
;WITH CTE_GROUPED AS (
	SELECT 
		 [id]
		,[name]
		,[status] 
		,[dtRecorded]
		 -- COUNT: Only counts non null values!
		,COUNT(
				-- CASE: returns null when you want it to be grouped with values above
				CASE WHEN [status] = 'I' THEN NULL ELSE [status] END
		 ) 
		 OVER (PARTITION BY [name] ORDER BY [name], [dtRecorded]) AS [grouping]

	FROM #CreditHistory
)


/*=================================================================================================
STEP 2:
		Return the results with the new status value.
		The [grouping] field is used to limit the max of the status- which is the newStatus
=================================================================================================*/
SELECT 
	 [id]
	,[name]
	,[status]
	,[dtRecorded]
	,[grouping] 

	 -- MAX: Discards null values
	,MAX(CASE WHEN [status] = 'I' THEN NULL ELSE [status] END) 
	 -- Add [grouping] to the partition clause to limit the max calculation 
	 OVER (PARTITION BY [name],[grouping] ORDER BY [name],[dtRecorded]) AS [newStatus]

FROM CTE_GROUPED

XML Namespaces – Getting Report server report info

In T-SQL, if an XML data point has namespaces defined then you must use the WITH XMLNAMESPACES … clause when querying it.

 

 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition', 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd )
SELECT  
CATDATA.Name AS ReportName
,CATDATA.Path AS ReportPathLocation
,xmlcolumn.value('(@Name)[1]', 'VARCHAR(250)') AS DataSetName  
,xmlcolumn.value('(Query/DataSourceName)[1]','VARCHAR(250)') AS DataSoureName 
,xmlcolumn.value('(Query/CommandText)[1]','VARCHAR(2500)') AS CommandText
FROM (  
       SELECT C.Name
       ,c.Path
       ,CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXML
       FROM  ReportServer.dbo.Catalog C
       WHERE  C.Content is not null
       AND  C.Type = 2
       ) CATDATA
CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet/') xmltable ( xmlcolumn )

Calculating year to day targets by month

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
DECLARE @TargetDate DATETIME = '01 mar 2018'

IF OBJECT_ID(N'tempdb..#temp') IS NOT NULL DROP TABLE #temp

;WITH cte AS (
	SELECT CAST('01 jan 2018' AS DATE) AS dateid
	UNION ALL
	SELECT dateadd(month,1,dateid) FROM cte 
	WHERE MONTH(dateid) <> 12 
)


SELECT dateid,'averageLoan' AS [TargetName], month(dateid) AS [Target] INTO #temp FROM cte
UNION ALL
SELECT dateid,'target_1', 2 AS [Target] FROM cte
UNION ALL
SELECT dateid,'target_2', 3 AS [Target] FROM cte
UNION ALL
SELECT dateid,'target_3', 4 AS [Target] FROM cte
UNION ALL
SELECT dateid,'target_4', 5 AS [Target] FROM cte


SELECT 
	t1.targetName					AS TargetName, 
	SUM(t1.[target] * t2.[target])	AS TargetValue

FROM		#temp t1 
INNER JOIN	#temp t2	ON t1.dateid = t2.dateid

WHERE 
	MONTH(t1.dateid) <= MONTH(@TargetDate)
AND 
	t1.targetName <> 'averageLoan'
AND 
	t2.targetName = 'averageLoan'
GROUP BY 
	t1.targetName