September 12, 2007 at 3:34 am
Creation of XML is extremely easy when using select.....for XML
My problem is that in some fields, extended ascii is present. IN XML I need the corresponding HTML code
when é is in a field, it must be converted to é in my XML file...
when ë is in a field, it must be converted to ë in my XML file...
etc.
Is there a command in T-SQL or some other option to do this ??
any help is appreciated !!
thanks in advance,
Wim
September 12, 2007 at 6:43 am
obviously you can do this on the application side, but not natively in TSQL...looks like you'd need a function.
I bet the .NET HTMLEncode and HTMLDecode functions would be perfect for this, does someone know how to run these via CLR? bet it;'s been done already, so we might want to just google this.
otherwise it's looking thru the code and replacing CHAR(199) with it's &#HEX counterpart
Lowell
September 12, 2007 at 7:12 am
ha!
once again, stealing/borrowing Jeff Modens example and running with it, here's a TSQL function:
--===== Create and populate a 100,000 row test table.
-- Column RowNum has a range of 1 to 1,000,000 unique numbers
-- Column "SomeString" has data with characters above the CHAR(128) range
-- Takes about 2 seconds to execute.
SELECT TOP 100000
RowNum = IDENTITY(INT,1,1),
SomeString = CAST('‘Special Digital Data Service Obligation’' AS VARCHAR(80))
INTO dbo.JBMTest
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
--Lack of join criteria makes this a CROSS-JOIN
--===== A table is not properly formed unless a Primary Key has been assigned
ALTER TABLE dbo.JBMTest
ADD PRIMARY KEY CLUSTERED (RowNum)
--===== Put some other data of concern in the table that must remain "meaningful"...
UPDATE dbo.JBMTest SET SomeString = 'GPS position 14º 54’ 0”' WHERE RowNum %2 = 0
--Now, before we get to the solution, we need to make a well indexed table of sequential numbers.
-- These "Tally" or "Numbers" tables are very powerful and can help do things in SQL Server 2000
--as if we were using ROWNUM from SQL Server 2005.
--You should make a permanent Tally table as follows... yes, this is part of the solution for this and many other "impossible" tasks...
--===== Create and populate the Tally table on the fly
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
--===== Allow the general public to use it
GRANT SELECT ON dbo.Tally TO PUBLIC
ALTER FUNCTION HTMLEncode(@OriginalText VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
DECLARE @CleanedText VARCHAR(8000)
SELECT @CleanedText = ISNULL(@CleanedText,'') +
CASE WHEN ASCII(SUBSTRING(@OriginalText,Tally.N,1)) < 128 THEN SUBSTRING(@OriginalText,Tally.N,1)
WHEN ASCII(SUBSTRING(@OriginalText,Tally.N,1)) >= 128 THEN '&#' + CONVERT(VARCHAR,ASCII(SUBSTRING(@OriginalText,Tally.N,1)) ) + ';' END
FROM dbo.Tally WHERE Tally.N <= LEN(@OriginalText)
RETURN @CleanedText
END
SELECT dbo.HTMLEncode('é')
SELECT dbo.HTMLEncode('happinéss ës a warm blankët')
SELECT dbo.HTMLEncode('‘Special Digital Data Service Obligation’')
results:
--------------------------------------------
é
happinéss ës a warm blankët
‘Special Digital Data Service Obligation’
Lowell
September 12, 2007 at 7:54 am
the original function did not encode <> symbols as well as quotes and ampersands;
this is a little better; someone critque this function please:
ALTER FUNCTION HTMLEncode(@OriginalText VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
DECLARE @CleanedText VARCHAR(8000)
SELECT @CleanedText = ISNULL(@CleanedText,'') +
CASE
WHEN ASCII(SUBSTRING(@OriginalText,Tally.N,1)) = 32 THEN ' '
WHEN ASCII(SUBSTRING(@OriginalText,Tally.N,1)) = 34 THEN '"'
WHEN ASCII(SUBSTRING(@OriginalText,Tally.N,1)) = 38 THEN '&'
WHEN ASCII(SUBSTRING(@OriginalText,Tally.N,1)) = 60 THEN '<'
WHEN ASCII(SUBSTRING(@OriginalText,Tally.N,1)) = 62 THEN '>'
WHEN ASCII(SUBSTRING(@OriginalText,Tally.N,1)) BETWEEN 32 AND 127 THEN SUBSTRING(@OriginalText,Tally.N,1)
WHEN ASCII(SUBSTRING(@OriginalText,Tally.N,1)) <= 32 THEN '&#' + CONVERT(VARCHAR,ASCII(SUBSTRING(@OriginalText,Tally.N,1)) ) + ';'
WHEN ASCII(SUBSTRING(@OriginalText,Tally.N,1)) >= 128 THEN '&#' + CONVERT(VARCHAR,ASCII(SUBSTRING(@OriginalText,Tally.N,1)) ) + ';' END
FROM dbo.Tally WHERE Tally.N <= LEN(@OriginalText)
RETURN @CleanedText
END
Lowell
November 20, 2007 at 7:01 am
now only they gave me some time for further testing on this....thanks a lot for your effort lowell, I managed to get perfect export now, cheers!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply