How to save the " character in a CDATA in SQL SErver 2005

Dear Pals,

I know that special characters, such as <, in a cdata section will be converted properly into entity reference before getting stored in a xml field in the database.

However, it seems that the character " (quotation) does not get converted to &quot;. This would result in problems when the XML document is fetched.

For example, a xml document like:

<A><![CDATA[THis is "John"]]></A>

when stored, it becomes

<A>THis is "John"</A>

This of course causes problem for XML parsers.

IS there any cure for that problem.

Thanks

Feng-Hsu Wang

Feng-Hsu,

According to the XML spec:

The ampersand character (&) and the left angle bracket (<) MUST NOT appear in their literal form, except when used as markup delimiters, or within a comment, a processing instruction, or a CDATA section. If they are needed elsewhere, they MUST be escaped using either numeric character references or the strings " & " and " < " respectively. The right angle bracket (>) may be represented using the string " > ", and MUST, for compatibility, be escaped using either " > " or a character reference when it appears in the string " ]]> " in content, when that string is not marking the end of a CDATA section.

In the content of elements, character data is any string of characters which does not contain the start-delimiter of any markup and does not include the CDATA-section-close delimiter, " ]]> ". In a CDATA section, character data is any string of characters not including the CDATA-section-close delimiter, " ]]> ".

To allow attribute values to contain both single and double quotes, the apostrophe or single-quote character (') may be represented as " ' ", and the double-quote character (") as " " ".

As I understand the XML spec, a single quote and a double quote character does not need to be escaped inside an element value. They only need to be escaped inside an attribute value.

So, this should not be causing any problems for XML parsers.

Jimmy Wu

|||

<A>THis is "John"</A>

is well-formed xml. That means it is following all the syntax rules for xml. If you are getting an error from an xml parser reading this then that parser is broken.

Dan

sql

How to restore, transfer or copy a sql 2005 database to sql 2000 server

Because I'm stupid I assumed, it would be simple to move a sql server 2005 database to sql server 2000, if I don't use special 2005 features. But restore completely not work. No option in case of restore?

OK - there are some hints about using export etc. in

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=166196&SiteID=1

But it seems to be not so easy. Can someone please explain, how to do this move step by step? I need complete transfer for all objects, including fulltext, indexes, triggers and data. Can I use BCP :-( ?

Many thanks for help - I run into real trubble with this...

fd2000

The on-disk format of SQL databases has changed between SQL 2000 and SQL 2005. That is why you cannot restore a SQL 2005 backup onto a SQL 2000 instance. This is not going to change.

So, your options are to find a way to make SQL 2005 work for you, or to export your data, and re-import into a SQL 2000 instance. BCP should work for this. Use the BCP80 option which ensures that no SQL 2005 options are used. Note that if you have added anything which relies on a SQL2005 feature, it will not be fully transferred to your SQL 2000 instance. (UDTs come across as LOBs, etc.)

|||

You have to install a instance of sql server 2000 and export the data/object back to it else u have to make your sql server 2005 work for u.

sanjeevupd