illegal name character

Question

I have a table A with two Columns:

Create table A( pk_Columns varchar(1000), PK_Source VARCHAR(1000))
SELECT 'INo: ACV: ACodeDescription', 
'37001:H3BMHCH3EG:Pentazocine/Acetaminophen:'
UNION ALL
SELECT 'INo: ACV: ACodeDescription', 
'37001:H3DA  J2AB  J2AH  W2GB  W2GU  :Usept:'
 

I also implemented a function that assigns the PK_Value_SOurce values to the repective PK_KeyColumn values

SET NOCOUNT ON;
USE tempdb;
GO
CREATE FUNCTION dbo.GetNums(@n AS BIGINT) RETURNS TABLE
AS
RETURN
  WITH
  L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
  L2   AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
  L3   AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
  L4   AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
  L5   AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
  Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
  SELECT TOP (@n) n FROM Nums ORDER BY n;
GO
SELECT
	*,
	STUFF(
	(
	SELECT
		', ' +
		T.k.value('(k[sql:column("G.n")][1]/text())[1]', 'varchar(max)') + '= ' +
		T.v.value('(v[sql:column("G.n")][1]/text())[1]', 'varchar(max)') AS [*]
	FROM
		(
		SELECT 
			CAST('<k>' + REPLACE(A.PK_Columns, ':', '</k><k>') + '</k>' AS xml),
			CAST('<v>' + REPLACE(A.PK_Value_Source, ':', '</v><v>') + '</v>' AS xml)
		) AS T(k, v)
		CROSS APPLY
		dbo.GetNums(T.k.value('count(/k)', 'int')) AS G
	ORDER BY
		G.n
	FOR XML PATH('')
	), 1, 2, '') AS PK_Key_Columns_Value_Source
FROM
	dbo.A
GO
IF OBJECT_ID('dbo.GetNums') IS NOT NULL DROP FUNCTION dbo.GetNums;

When I use a cross join with my function GetNums, I get the following error:

XML parsing: line 1, character 41, illegal name character

Any ideas on how to debug this?

Thanks,

Ion

---

Ion

Answer 1

Do you have any rows with the following values in?

&

<

>

These will have to be escaped, eg & becomes &amp;

select *
from a
where PK_Columns Like '%&%'
  or PK_Columns Like '%<%'
  or PK_Columns Like '%>%'
  or PK_Source Like '%&%'
  or PK_Source Like '%<%'
  or PK_Source Like '%>%'

Fix them up like this:

UPDATE a
SET PK_Source = REPLACE( PK_Source, '&', '&' )

Answer 2

Hello Bob,

Yes, I do.

How do you fix those with the > or < OR /?

Thanks,

ion

---

Ion

Answer 3

REPLACE (PK_Source, '<', '&lt;')
REPLACE (PK_Source, '>', '&gt;')

Or

REPLACE (REPLACE (REPLACE (PK_Source, '&', '&')
  , '>', '>')
  , '<', '&lt;')

---

Russel Loski, MCT, MCITP Business Intelligence Developer and Database Developer 2008 Blog: http://www.bidn.com/blogs/RussLoski/ Twitter: @sqlmovers

Answer 4

Gentlemen,

I also catered for  the double-quot character (") replacing it with '&quot;' and single-quot or apostrophe (`) replacing it with '&apos;'

Thanks alot,

Ion

---

Ion