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 run package with variables using dtexec?

Hi,

I am running my package in this way:

exec xp_cmdshell 'dtexec /SQL "\SBLoadExcelDBLog" /SERVER test /USER **** /PASSWORD ***** /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW

/LOGGER "{6AA833A1-E4B2-4431-831B-DE695049DC61}";"Test.SuperBowl"

/Set \Package.Variables[User::varExcelFileName].Properties[Value];"aaab"

/Set \Package.Variables[User::varExcelWorkbookName].Value;"Sheet1$"

/Set \Package.Variables[User::varExcelFileFullPath].Value;"D:\testshare\aaab.xls"

/Set \Package.Variables[User::varDestinationTableName].Value;"FeaturesTmp"

/Set \Package.Variables[User::varPreSQLAction].Value;"delete from FeaturesTmp" '

I got errors:

Started: 3:49:51 PM

Progress: 2006-01-30 15:49:52.34

Source: Extract AdHoc Data from Excel

Validating: 0% complete

End Progress

Error: 2006-01-30 15:49:52.46

Code: 0xC0202009

Source: Extract AdHoc Data from Excel Excel Source [649]

Description: An OLE DB error has occurred. Error code: 0x80040E37.

End Error

Error: 2006-01-30 15:49:52.46

Code: 0xC02020E8

Source: Extract AdHoc Data from Excel Excel Source [649]

Description: Opening a rowset for "Sheet1$" failed. Check that the object exists in the database.

End Error

Error: 2006-01-30 15:49:52.51

Code: 0xC004706B

Source: Extract AdHoc Data from Excel DTS.Pipeline

Description: "component "Excel Source" (649)" failed validation and returned validation status "VS_ISBROKEN".

End Error

Progress: 2006-01-30 15:49:52.51

Source: Extract AdHoc Data from Excel

Validating: 25% complete

End Progress

Error: 2006-01-30 15:49:52.51

Code: 0xC004700C

Source: Extract AdHoc Data from Excel DTS.Pipeline

Description: One or more component failed validation.

End Error

Error: 2006-01-30 15:49:52.51

Code: 0xC0024107

Source: Extract AdHoc Data from Excel

Description: There were errors during task validation.

End Error

DTExec: The package execution returned DTSER_FAILURE (1).

Started: 3:49:51 PM

Finished: 3:49:52 PM

Elapsed: 0.703 seconds

Finally I found the problem:

Everything is OK But:

The way to put the command string is not right after dtexec /SQL.

If there is not ENTER and all commands in one line (as it is a dos command), everything is OK!

Is it COOL!?

Guangming

|||

Is there a reason for using "...[varibale name].Properties.[Value]" for the first variable and "...[varibale name].Value" for the others?

Dick Campbell

|||

No, they are basically same but different ways to access to the value of the variable.

Guangming

|||

Thanks,

I thought that it just might be a mistake. I was also curious to know, if they are both valid, whether there is any difference in the functionality.

Dick Campbell

How to run package with variables using dtexec?

Hi,

I am running my package in this way:

exec xp_cmdshell 'dtexec /SQL "\SBLoadExcelDBLog" /SERVER test /USER **** /PASSWORD ***** /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW

/LOGGER "{6AA833A1-E4B2-4431-831B-DE695049DC61}";"Test.SuperBowl"

/Set \Package.Variables[User::varExcelFileName].Properties[Value];"aaab"

/Set \Package.Variables[User::varExcelWorkbookName].Value;"Sheet1$"

/Set \Package.Variables[User::varExcelFileFullPath].Value;"D:\testshare\aaab.xls"

/Set \Package.Variables[User::varDestinationTableName].Value;"FeaturesTmp"

/Set \Package.Variables[User::varPreSQLAction].Value;"delete from FeaturesTmp" '

I got errors:

Started: 3:49:51 PM

Progress: 2006-01-30 15:49:52.34

Source: Extract AdHoc Data from Excel

Validating: 0% complete

End Progress

Error: 2006-01-30 15:49:52.46

Code: 0xC0202009

Source: Extract AdHoc Data from Excel Excel Source [649]

Description: An OLE DB error has occurred. Error code: 0x80040E37.

End Error

Error: 2006-01-30 15:49:52.46

Code: 0xC02020E8

Source: Extract AdHoc Data from Excel Excel Source [649]

Description: Opening a rowset for "Sheet1$" failed. Check that the object exists in the database.

End Error

Error: 2006-01-30 15:49:52.51

Code: 0xC004706B

Source: Extract AdHoc Data from Excel DTS.Pipeline

Description: "component "Excel Source" (649)" failed validation and returned validation status "VS_ISBROKEN".

End Error

Progress: 2006-01-30 15:49:52.51

Source: Extract AdHoc Data from Excel

Validating: 25% complete

End Progress

Error: 2006-01-30 15:49:52.51

Code: 0xC004700C

Source: Extract AdHoc Data from Excel DTS.Pipeline

Description: One or more component failed validation.

End Error

Error: 2006-01-30 15:49:52.51

Code: 0xC0024107

Source: Extract AdHoc Data from Excel

Description: There were errors during task validation.

End Error

DTExec: The package execution returned DTSER_FAILURE (1).

Started: 3:49:51 PM

Finished: 3:49:52 PM

Elapsed: 0.703 seconds

Finally I found the problem:

Everything is OK But:

The way to put the command string is not right after dtexec /SQL.

If there is not ENTER and all commands in one line (as it is a dos command), everything is OK!

Is it COOL!?

Guangming

|||

Is there a reason for using "...[varibale name].Properties.[Value]" for the first variable and "...[varibale name].Value" for the others?

Dick Campbell

|||

No, they are basically same but different ways to access to the value of the variable.

Guangming

|||

Thanks,

I thought that it just might be a mistake. I was also curious to know, if they are both valid, whether there is any difference in the functionality.

Dick Campbell