All posts by dev129

Developer

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 )

Biml resources

Business Intelligence Markup Language is a way of defining SSIS and SSAS packages in a (fairly) readable format.  The Biml format facilitates the reuse of code and can then be used to generate packages rapidly.

Biml script makes Biml dynamic.  You can use metadata to drive the generation of packages which significantly reduces the time to develop SSIS and SSAS packages.

Recommended resources:

Increase your productivity with Biml – a clear introducton for anyone who has no knowledge of Biml.

BimlOnline – Tool for converting existing packages into Biml.

BIDS Helper (New Github site) – Visual Studio add-in which adds the Biml file format and the Biml processor for generating SSIS packages.  The previous site can be found at BIDS Helper (Archive Codeplex site)

Varigence – the company that has developed Biml and produced many of the leading products related to Biml.