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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy