Category Archives: xml

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

 

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 )

Catch bad XML characters

 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
/*
	Date : 17/01/2018
	Notes: Catches a bad xml character error and displays messages to help with debugging
*/

DECLARE @x VARCHAR(MAX)
SET @x = 'dave' + CHAR(160) + 'smith'

BEGIN TRY
	SELECT CAST(@x AS XML) AS xmlOutput
END TRY
BEGIN CATCH

	DECLARE  @errorMessage VARCHAR(MAX)
			,@errPos INT

	SELECT @errorMessage = ERROR_MESSAGE()
	
	IF PATINDEX('%illegal xml character%', @errorMessage) > 0
	BEGIN  
		SELECT @errPos = SUBSTRING(@errorMessage, 
									PATINDEX('%character %', @errorMessage) + 10,
									PATINDEX('%, illegal%', @errorMessage) - PATINDEX('%character %', @errorMessage) - 10 )

		PRINT 'Error caused by illegal character in XML:'
		PRINT 'Character:' + SUBSTRING(@x,@errPos,1)
		PRINT 'Ascii Code:'	+ CAST(ASCII(SUBSTRING(@x,@errPos,1)) AS VARCHAR(MAX))
		PRINT 'Surrounding text:' + SUBSTRING(@x,@errPos-100,200)
		PRINT ''
	END
    
END CATCH