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 rung SQL 2005 db on SQL 2000 server - no unique SQL 2005 st

I converted a database from Sql 2000 to Sql 2005 - no problems. Added
function to the database. I did not use any new functions unique to Sql
2005, as far as I know. I need to allw this database to run on both SQL 2000
and SQL 2005. When I attempted to load the database on SQL 2000 local
server, I received the following message:
Error 602 - Could not find Row in SysIndex for ID 7, Object ID 1, Index ID 1.
Run DBCC checktable on SysIndex.
I have never run this and I do not know if this is an on going problem. Any
information would be appreciated.
Thank You.
Jack
LitePipe ManagementYou cannot restore or attach a 2005 database to 2000, 2005 added stuff to the database file formats
that wasn't known when MS wrote 2000 (obviously). To downgrade, you have to go the script,
export/import route (using BCP, DTS, SSIS etc).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"LitePipe" <LitePipe@.discussions.microsoft.com> wrote in message
news:5BB70FC5-0BD5-4306-AC5D-0ED417835C6B@.microsoft.com...
>I converted a database from Sql 2000 to Sql 2005 - no problems. Added
> function to the database. I did not use any new functions unique to Sql
> 2005, as far as I know. I need to allw this database to run on both SQL 2000
> and SQL 2005. When I attempted to load the database on SQL 2000 local
> server, I received the following message:
> Error 602 - Could not find Row in SysIndex for ID 7, Object ID 1, Index ID 1.
> Run DBCC checktable on SysIndex.
> I have never run this and I do not know if this is an on going problem. Any
> information would be appreciated.
> Thank You.
> Jack
> LitePipe Management|||Thank you, I appreciate you taking the time to answer my question.
Jack Leach
"Tibor Karaszi" wrote:
> You cannot restore or attach a 2005 database to 2000, 2005 added stuff to the database file formats
> that wasn't known when MS wrote 2000 (obviously). To downgrade, you have to go the script,
> export/import route (using BCP, DTS, SSIS etc).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "LitePipe" <LitePipe@.discussions.microsoft.com> wrote in message
> news:5BB70FC5-0BD5-4306-AC5D-0ED417835C6B@.microsoft.com...
> >I converted a database from Sql 2000 to Sql 2005 - no problems. Added
> > function to the database. I did not use any new functions unique to Sql
> > 2005, as far as I know. I need to allw this database to run on both SQL 2000
> > and SQL 2005. When I attempted to load the database on SQL 2000 local
> > server, I received the following message:
> > Error 602 - Could not find Row in SysIndex for ID 7, Object ID 1, Index ID 1.
> > Run DBCC checktable on SysIndex.
> >
> > I have never run this and I do not know if this is an on going problem. Any
> > information would be appreciated.
> >
> > Thank You.
> >
> > Jack
> > LitePipe Management
>|||Hi,
If you select the option of scripting the SQL 2005 database tobe
compatible with SQL 2000.
There is a bug in SQL 2005 were it won't create the correct script
which can be used with SQL 2000.
May be you will have to wait for next SP or need to modify the script
so it can run on SQL 2k.
Thanks
Ajay Rengunthwar
MCTS
LitePipe wrote:
> Thank you, I appreciate you taking the time to answer my question.
> Jack Leach
> "Tibor Karaszi" wrote:
> > You cannot restore or attach a 2005 database to 2000, 2005 added stuff to the database file formats
> > that wasn't known when MS wrote 2000 (obviously). To downgrade, you have to go the script,
> > export/import route (using BCP, DTS, SSIS etc).
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "LitePipe" <LitePipe@.discussions.microsoft.com> wrote in message
> > news:5BB70FC5-0BD5-4306-AC5D-0ED417835C6B@.microsoft.com...
> > >I converted a database from Sql 2000 to Sql 2005 - no problems. Added
> > > function to the database. I did not use any new functions unique to Sql
> > > 2005, as far as I know. I need to allw this database to run on both SQL 2000
> > > and SQL 2005. When I attempted to load the database on SQL 2000 local
> > > server, I received the following message:
> > > Error 602 - Could not find Row in SysIndex for ID 7, Object ID 1, Index ID 1.
> > > Run DBCC checktable on SysIndex.
> > >
> > > I have never run this and I do not know if this is an on going problem. Any
> > > information would be appreciated.
> > >
> > > Thank You.
> > >
> > > Jack
> > > LitePipe Management
> >
> >

How to rung SQL 2005 db on SQL 2000 server - no unique SQL 2005 st

I converted a database from Sql 2000 to Sql 2005 - no problems. Added
function to the database. I did not use any new functions unique to Sql
2005, as far as I know. I need to allw this database to run on both SQL 200
0
and SQL 2005. When I attempted to load the database on SQL 2000 local
server, I received the following message:
Error 602 - Could not find Row in SysIndex for ID 7, Object ID 1, Index ID 1
.
Run DBCC checktable on SysIndex.
I have never run this and I do not know if this is an on going problem. Any
information would be appreciated.
Thank You.
Jack
LitePipe ManagementYou cannot restore or attach a 2005 database to 2000, 2005 added stuff to th
e database file formats
that wasn't known when MS wrote 2000 (obviously). To downgrade, you have to
go the script,
export/import route (using BCP, DTS, SSIS etc).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"LitePipe" <LitePipe@.discussions.microsoft.com> wrote in message
news:5BB70FC5-0BD5-4306-AC5D-0ED417835C6B@.microsoft.com...
>I converted a database from Sql 2000 to Sql 2005 - no problems. Added
> function to the database. I did not use any new functions unique to Sql
> 2005, as far as I know. I need to allw this database to run on both SQL 2
000
> and SQL 2005. When I attempted to load the database on SQL 2000 local
> server, I received the following message:
> Error 602 - Could not find Row in SysIndex for ID 7, Object ID 1, Index ID
1.
> Run DBCC checktable on SysIndex.
> I have never run this and I do not know if this is an on going problem. A
ny
> information would be appreciated.
> Thank You.
> Jack
> LitePipe Management

how to retrieve inserted rows in sql server 2000

Hi,

I have this scenario:

items should be inserted into a item table before they are converted into assets. The database server is sql server 2000.

so item-> itemTable ->assetTable

When I insert items into the itemTable, how can I retrieve the itemID (an identity column) that will be created during the insert?

I know I can use scope_identity(), but if two or three person are inserting items to the same table, which scope_identity() correctly return the identity value? Should I lock the table first?

Also, I am using openxml to insert more than one items in one tsql statement, is there any chance to get all the newly created identity values? To make things worse, some items do not need to be converted into assetTables (according to itemType column), how can I loop through the whole xml and only insert certain items (continue using openxml if possible).?

many thanks

jerry

To answer your first question, Scope_Identity() is designed for just this situation. No need to lock the table.

I've not used openxml so can't help you with that. You could do this through DTS easily.

On the bulk insert, I suspect you'll have to re-query after the insert to get the values.

|||

How can I use DTS to do this? Can you give me an example in c# (or vb)?

Cheers

|||

DTS is a feature of SS2000 Enterprise Manager.

How you do this depends upon exactly what you want to do.

For the absolute basics, you could use the DTS Wizard to create a simple transformation / import from the XML file to a SQL Server table.

Not knowing your situation, I'd suggest one of two approaches:

1) Import the data into a staging table, then insert into your two tables with a SQL statement based upon your desired criteria.

2) Import the data into the first table with a timestamp (this would require adding a column to this table). Insert into the second table with a SQL statement based upon your desired criteria and the timestamp.

|||

Unfortunately there is no easy way to get the generated identity values for multiple rows other than querying the table or using a trigger to persist the results. SCOPE_IDENTITY or @.@.IDENTITY will only give the last generated value and single row only. In SQL Server 2005, there is a new OUTPUT clause that can be used in DML statements to get the identity values into a temporary table for example. See post below for more details on this feature or Books Online:

http://blogs.msdn.com/sqltips/archive/2005/06/13/OUTPUT_clause.aspx

So getting back to SQL Server 2000, you can do the following:

-- in your SP:

create table #ident ( i int not null )

insert ...

select ...

from openxml()

-- in your trigger

if object_id('tempdb...#ident') is not null

insert into #ident (i)

select identity_col from table

Now you can query the temporary table to get the generated values. You will probably need other key column(s) to match the rows with the ones returned by OPENXML.

|||

Fantastic... I have been prowling through my new O'Reilly textbook looking for this type of feature (as I need it - adding new stock items to an e-commerce database) and I discover that Microsoft have JUST invented it and implemented it into TSQL and SQL Server 05?!
Microsoft has new-found respect from me...

how to retrieve inserted rows in sql server 2000

Hi,

I have this scenario:

items should be inserted into a item table before they are converted into assets. The database server is sql server 2000.

so item-> itemTable ->assetTable

When I insert items into the itemTable, how can I retrieve the itemID (an identity column) that will be created during the insert?

I know I can use scope_identity(), but if two or three person are inserting items to the same table, which scope_identity() correctly return the identity value? Should I lock the table first?

Also, I am using openxml to insert more than one items in one tsql statement, is there any chance to get all the newly created identity values? To make things worse, some items do not need to be converted into assetTables (according to itemType column), how can I loop through the whole xml and only insert certain items (continue using openxml if possible).?

many thanks

jerry

To answer your first question, Scope_Identity() is designed for just this situation. No need to lock the table.

I've not used openxml so can't help you with that. You could do this through DTS easily.

On the bulk insert, I suspect you'll have to re-query after the insert to get the values.

|||

How can I use DTS to do this? Can you give me an example in c# (or vb)?

Cheers

|||

DTS is a feature of SS2000 Enterprise Manager.

How you do this depends upon exactly what you want to do.

For the absolute basics, you could use the DTS Wizard to create a simple transformation / import from the XML file to a SQL Server table.

Not knowing your situation, I'd suggest one of two approaches:

1) Import the data into a staging table, then insert into your two tables with a SQL statement based upon your desired criteria.

2) Import the data into the first table with a timestamp (this would require adding a column to this table). Insert into the second table with a SQL statement based upon your desired criteria and the timestamp.

|||

Unfortunately there is no easy way to get the generated identity values for multiple rows other than querying the table or using a trigger to persist the results. SCOPE_IDENTITY or @.@.IDENTITY will only give the last generated value and single row only. In SQL Server 2005, there is a new OUTPUT clause that can be used in DML statements to get the identity values into a temporary table for example. See post below for more details on this feature or Books Online:

http://blogs.msdn.com/sqltips/archive/2005/06/13/OUTPUT_clause.aspx

So getting back to SQL Server 2000, you can do the following:

-- in your SP:

create table #ident ( i int not null )

insert ...

select ...

from openxml()

-- in your trigger

if object_id('tempdb...#ident') is not null

insert into #ident (i)

select identity_col from table

Now you can query the temporary table to get the generated values. You will probably need other key column(s) to match the rows with the ones returned by OPENXML.

|||

Fantastic... I have been prowling through my new O'Reilly textbook looking for this type of feature (as I need it - adding new stock items to an e-commerce database) and I discover that Microsoft have JUST invented it and implemented it into TSQL and SQL Server 05?!
Microsoft has new-found respect from me...