Tag 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	

 

The other space character… ascii 160

I recently discovered that the space character in ascii has an evil twin.  Like all evil twins it looks exactly the same but it does evil…

Here they are in SSMS:

spacesSideBySide

They look the same – why is there another space character in ascii?

The second space is a non-breaking space character – so any program displaying the text should not separate a line at that point.

Why is ascii 160 evil?

The other day I was trying to extract some data into xml but I kept getting an illegal xml character error.

illegalCharacter

The source data had a single ascii 160 character in it, but all the other spaces were ascii 32.  Even when you find the row of data which has the non breaking space it still requires a lot of work to actually find where the character is!

How can you solve this error?

The best way is to make sure that the data going into your system is cleaned of the non breaking space – if you intend to extract it in XML.

Another possibility is to replace non breaking spaces with breaking spaces in the output.

replace160

Or, if the system that will receive the XML is happy with it, you could change the encoding to UTF-16.

illegalCharToUTF16