xml function

Question

xml function

sql_prodigy

SSCrazy

Points: 2254
More actions
July 30, 2008 at 5:33 am

#188816

hi, can someone please explain to me what is happening here...
CREATE FUNCTION [dbo].[f_ConvertXML]
(
@NText NText
)
RETURNS XML
AS
BEGIN
Declare @XML XML
SET @XML = convert(XML,replace(replace(replace(convert(nvarchar(max),@NText),'''',''''''),'UTF-8','UTF-16'),'utf-8','UTF-16'))
RETURN @XML
END

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

tertiusdp SSCommitted Points: 1569 More actions · Answer 1

The function takes a text string and converts it to an XML data type and returns the converted XML data.

It seems the input text also is not in the correct format, so the function does the following to the string to get it in a format that can be converted:

1) Converts the NText datatype to Nvarchar: convert(nvarchar(max),@NText)

2) It replaces single quotes with double quotes: replace('''',''''''). If you have don't it will convert it to don''t.

3) It replaces the utf-8 string to be UTF-16. This is part of the XML document header.

sql_prodigy SSCrazy Points: 2254 More actions · Answer 2

sql_prodigy

SSCrazy

Points: 2254

July 30, 2008 at 6:56 am

#850439

thanks

antonio.collins SSCrazy Eights Points: 9600 More actions · Answer 3

as an aside, using replace() to change UTF-x with UTF-y (or any encoding) is not a good idea. changing the character encoding declaration in the doctype DOES NOT change the actual character encoding of the XML string.

going from UTF-8 to UTF-16 is more/less benign, but UTF-16 to UTF-8 is very likely to cause issues. you should also realize that there are many more encodings other than UTF-8/16 so if you need something to be UTF-16 then you should take the proper steps to change the encoding and not assume that replace() and implicit conversions are sufficient.

sql_prodigy SSCrazy Points: 2254 More actions · Answer 4

is there a better way that can improve performance cause im calling that function through a stored procedure. the table concerned has lots of xml in it...

tertiusdp SSCommitted Points: 1569 More actions · Answer 5

You can convert the column to an XML datatype. Then you can use Xpath etc to query the xml. You can also create indexes on XML columns so that could also help. It depends really what the query does.