Problem with CDATA section in XQuery

Hi,
i am trying to insert a CDATA section using XQuery in T-SQL. I am following
the example from books online
http://msdn2.microsoft.com/en-us/library/ms175466.aspx and i keep getting th
e
error 'XQuery [modify()]: Syntax error near '<!''
The code is:
DECLARE @.myDoc xml
SET @.myDoc =
'<Root>
<ProductDescription ProductID="1" ProductName="Road Bike">
<Features> </Features>
</ProductDescription>
</Root>'
SELECT @.myDoc
SET @.myDoc.modify('
insert <![CDATA[ <notxml> as text </notxml> or cdata ]]>
into (/Root/ProductDescription/Features)[1] ')
SELECT @.myDoc ;
Does anyone have a solution for this?
Thanks,
Stefan
I think you can achieve the same effect by doing this
SET @.myDoc.modify('
insert text {"<notxml> as text </notxml> or cdata"}
into (/Root/ProductDescription/Features)[1]')|||Hello markc600@.hotmail.com,
Not really. If he really does want to use the CDATA tag here, he can't --
the content doesn't mater. I suspect, but XMLRW isn't parsing it correctly.
This smells like a bug to me.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/|||In this case I believe you should be able to use the text node constructor,
like
this
SET @.myDoc.modify('insert text{"<notxml> as text </notxml> or cdata"} into
(/Root/ProductDescription/Features)[1] ')
Denis Ruckebusch
SQL Server XML datatype test team
http://blogs.msdn.com/denisruc
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"mindflower" <mindflower@.discussions.microsoft.com> wrote in message
news:9DBECB6E-006B-4537-A039-CEEA784B9FD8@.microsoft.com...
> Hi,
> i am trying to insert a CDATA section using XQuery in T-SQL. I am followin
g
> the example from books online
> http://msdn2.microsoft.com/en-us/library/ms175466.aspx and i keep getting
the
> error 'XQuery [modify()]: Syntax error near '<!''
> The code is:
> DECLARE @.myDoc xml
> SET @.myDoc =
> '<Root>
> <ProductDescription ProductID="1" ProductName="Road Bike">
> <Features> </Features>
> </ProductDescription>
> </Root>'
> SELECT @.myDoc
> SET @.myDoc.modify('
> insert <![CDATA[ <notxml> as text </notxml> or cdata ]]>
> into (/Root/ProductDescription/Features)[1] ')
> SELECT @.myDoc ;
> Does anyone have a solution for this?
> Thanks,
> Stefan

Problem with BCP in trigger, how to do it?

Hello all,

I′m pretty new to T-SQL, so please bear with me.

I′m trying to output the temp "inserted" table avalible in the trigger to a text file.

When this trigger executes, the server seems to enter a never ending query.

If i comment the last three lines (declare... select... exec...) the trigger works fine,

so it seems to be a problem with the BCP part of the trigger.

What is wrong here? If you have better suggestions on how to

accoplish the same thing, please feel free to share!

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER TRIGGER [Skapa_Transfil]

ON [dbo].[Products]

AFTER UPDATE

AS

BEGIN

SET NOCOUNT ON;

insert into dbo.TempProducts (ProdNo, CountryOfOrigin)

select prodno, CountryOfOrigin

from inserted

declare @.sql varchar(8000)

select @.sql = 'bcp avk..tempProducts out c:\fil.txt -c -t, -U sa -P dalla -S'

exec master..xp_cmdshell @.sql

END

I've managed to replicate this behaviour. The cause seems to be that the TRIGGER is taking out an exclusive lock on the rows that are being inserted into TempProducts and therefore the BCP statement is unable to obtain a shared and so cannot read the data. I can't see a way around this unfortunately.

Would you be able to handle this logic in a stored procedure,similar to the following:

Code Snippet

create procedure insertandexport
@.int1 int, @.int2 int
as
insert into tempproducts
values (@.int1, @.int2)

if @.@.rowcount > 0
begin
declare @.sql varchar(8000)

select @.sql = 'bcp tempdb..TempProducts out c:\fil.txt -c -t, -U"User" -P"password" -S"YouServer"'

exec master..xp_cmdshell @.sql
end

HTH!|||

The trigger runs inside a transaction, so the "insert into" statement is also inside that transaction and could be blocking the table or index. The execution of bcp is out of that transaction and has to wait till the blocking has gone if it is running in "read committed" isolation level.

AMB

|||

Since it would be acceptable with some delay of the updates from tempProducts to fil.txt, would it be a good idea to do something like this?

The trigger keeps updating the tempProducts table whenever the trigger fires, like so:

Code Snippet

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER TRIGGER [Skapa_Transfil]

ON [dbo].[Products]

AFTER UPDATE

AS

BEGIN

SET NOCOUNT ON;

insert into dbo.TempProducts (ProdNo, CountryOfOrigin)

select prodno, CountryOfOrigin

from inserted

END

Then I schedule the following sql script to run using "sqlcmd -i exportfromtempproducts" once a minute or so.

Code Snippet

begin transaction

declare @.sql varchar(8000)

select @.sql = 'bcp avk..tempProducts out c:\fil.txt -c -t, -U sa -P dalla -S'

exec master..xp_cmdshell @.sql

go

use avk

go

delete

from tempProducts

go

commit transaction

I tried this and to me it seems to work. Since I run the bcp and delete in one transaction, the trigger would never be able to insert data into tempProducts between the bcp and the delete?

Thoughts someone?

|||

I think the key to this is setting your TRANSATION ISOLATION LEVEL to SNAPSHOT. This will guarentee that the you will only be working with the rows as they were at the start of the transaction. Otherwise, no exclusive locks will be put on the tempProducts table and so you could get rows inserted after the bcp statement which would then be deleted.

Simulate this behaviour by executing the stages in 2 separate query windows, step by step (ie being tran, run the bcp statement only, update more rows in products in the other window, come back and then run the delete statement etc).

Check Books Online for a more thorough explanation of isolation levels.

As an aside, will you be overwriting fil.txt every minute? Does that matter?

Let us know how you get on!

|||

I made some changes to the query in order to get different file names for each execution,

naming the file with date and time.

How would you go about to execute the query in "separate stages" as you mention above?

I′m used to working with break-points from VB, but I can′t seem to find any similar feature for T-SQL

EDIT: For clarification, I also did the

ALTER DATABASE AVK

SET ALLOW_SNAPSHOT_ISOLATION

Code Snippet

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

BEGIN TRANSACTION

DECLARE @.date char(8)

DECLARE @.time char(8)

DECLARE @.sql VARCHAR(8000)

SELECT @.date = CONVERT(char(8), getdate(),112)

SELECT @.time = CONVERT(char(8), getdate(),108)

SELECT @.time = REPLACE(@.time,':','')

SELECT @.time

DECLARE @.dt char(14)

SELECT @.dt = @.date + '_' + @.time

SELECT @.sql = 'bcp avk..tempProducts out "c:\AVK_' + @.dt + '.txt" -c -t, -U sa -P dalla -S'

EXEC master..xp_cmdshell @.sql

GO

USE AVK

GO

DELETE

FROM tempProducts

GO

COMMIT TRANSACTION

|||

For testing purposes, i did the following:

Paste the whole block into a query window in SSMS and then just highlght and execute the individual sections:

ie hightlight this and execute

Code Snippet

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

BEGIN TRANSACTION

DECLARE @.date char(8)

DECLARE @.time char(8)

DECLARE @.sql VARCHAR(8000)

SELECT @.date = CONVERT(char(8), getdate(),112)

SELECT @.time = CONVERT(char(8), getdate(),108)

SELECT @.time = REPLACE(@.time,':','')

SELECT @.time

DECLARE @.dt char(14)

SELECT @.dt = @.date + '_' + @.time

SELECT @.sql = 'bcp avk..tempProducts out "c:\AVK_' + @.dt + '.txt" -c -t, -U sa -P dalla -S'

EXEC master..xp_cmdshell @.sql

GO

Then go into your another query window (which is a separate transaction) and execute an update statement on products

Then return to the main window, highlight and execute the final section

Code Snippet

USE AVK

GO

DELETE

FROM tempProducts

GO

COMMIT TRANSACTION

This will give you the behaviour as if an update to your products tabla occured while your second transaction was running and will allow you to view how the different isolation levels affect your results.

As for debugging a la VB, i think you are able to use this facility for stored procedures in Visual Studio but not SSMS.

HTH!

|||

WOHO!

Works like a charm!

Tried updating 3 records, then running the BCP-part of the query. 3 rows out as expected.

The updated 3 more records, did a select * on tempProducts, which now contained 6 records.

Finally ran the delete part of the query, checked tempProducts again. And yep, my last three updates where still there!

Thanks a bunch for all the help!

Problem with BCP in trigger, how to do it?

Hello all,

I′m pretty new to T-SQL, so please bear with me.

I′m trying to output the temp "inserted" table avalible in the trigger to a text file.

When this trigger executes, the server seems to enter a never ending query.

If i comment the last three lines (declare... select... exec...) the trigger works fine,

so it seems to be a problem with the BCP part of the trigger.

What is wrong here? If you have better suggestions on how to

accoplish the same thing, please feel free to share!

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER TRIGGER [Skapa_Transfil]

ON [dbo].[Products]

AFTER UPDATE

AS

BEGIN

SET NOCOUNT ON;

insert into dbo.TempProducts (ProdNo, CountryOfOrigin)

select prodno, CountryOfOrigin

from inserted

declare @.sql varchar(8000)

select @.sql = 'bcp avk..tempProducts out c:\fil.txt -c -t, -U sa -P dalla -S'

exec master..xp_cmdshell @.sql

END

I've managed to replicate this behaviour. The cause seems to be that the TRIGGER is taking out an exclusive lock on the rows that are being inserted into TempProducts and therefore the BCP statement is unable to obtain a shared and so cannot read the data. I can't see a way around this unfortunately.

Would you be able to handle this logic in a stored procedure,similar to the following:

Code Snippet

create procedure insertandexport
@.int1 int, @.int2 int
as
insert into tempproducts
values (@.int1, @.int2)

if @.@.rowcount > 0
begin
declare @.sql varchar(8000)

select @.sql = 'bcp tempdb..TempProducts out c:\fil.txt -c -t, -U"User" -P"password" -S"YouServer"'

exec master..xp_cmdshell @.sql
end

HTH!|||

The trigger runs inside a transaction, so the "insert into" statement is also inside that transaction and could be blocking the table or index. The execution of bcp is out of that transaction and has to wait till the blocking has gone if it is running in "read committed" isolation level.

AMB

|||

Since it would be acceptable with some delay of the updates from tempProducts to fil.txt, would it be a good idea to do something like this?

The trigger keeps updating the tempProducts table whenever the trigger fires, like so:

Code Snippet

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER TRIGGER [Skapa_Transfil]

ON [dbo].[Products]

AFTER UPDATE

AS

BEGIN

SET NOCOUNT ON;

insert into dbo.TempProducts (ProdNo, CountryOfOrigin)

select prodno, CountryOfOrigin

from inserted

END

Then I schedule the following sql script to run using "sqlcmd -i exportfromtempproducts" once a minute or so.

Code Snippet

begin transaction

declare @.sql varchar(8000)

select @.sql = 'bcp avk..tempProducts out c:\fil.txt -c -t, -U sa -P dalla -S'

exec master..xp_cmdshell @.sql

go

use avk

go

delete

from tempProducts

go

commit transaction

I tried this and to me it seems to work. Since I run the bcp and delete in one transaction, the trigger would never be able to insert data into tempProducts between the bcp and the delete?

Thoughts someone?

|||

I think the key to this is setting your TRANSATION ISOLATION LEVEL to SNAPSHOT. This will guarentee that the you will only be working with the rows as they were at the start of the transaction. Otherwise, no exclusive locks will be put on the tempProducts table and so you could get rows inserted after the bcp statement which would then be deleted.

Simulate this behaviour by executing the stages in 2 separate query windows, step by step (ie being tran, run the bcp statement only, update more rows in products in the other window, come back and then run the delete statement etc).

Check Books Online for a more thorough explanation of isolation levels.

As an aside, will you be overwriting fil.txt every minute? Does that matter?

Let us know how you get on!

|||

I made some changes to the query in order to get different file names for each execution,

naming the file with date and time.

How would you go about to execute the query in "separate stages" as you mention above?

I′m used to working with break-points from VB, but I can′t seem to find any similar feature for T-SQL

EDIT: For clarification, I also did the

ALTER DATABASE AVK

SET ALLOW_SNAPSHOT_ISOLATION

Code Snippet

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

BEGIN TRANSACTION

DECLARE @.date char(8)

DECLARE @.time char(8)

DECLARE @.sql VARCHAR(8000)

SELECT @.date = CONVERT(char(8), getdate(),112)

SELECT @.time = CONVERT(char(8), getdate(),108)

SELECT @.time = REPLACE(@.time,':','')

SELECT @.time

DECLARE @.dt char(14)

SELECT @.dt = @.date + '_' + @.time

SELECT @.sql = 'bcp avk..tempProducts out "c:\AVK_' + @.dt + '.txt" -c -t, -U sa -P dalla -S'

EXEC master..xp_cmdshell @.sql

GO

USE AVK

GO

DELETE

FROM tempProducts

GO

COMMIT TRANSACTION

|||

For testing purposes, i did the following:

Paste the whole block into a query window in SSMS and then just highlght and execute the individual sections:

ie hightlight this and execute

Code Snippet

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

BEGIN TRANSACTION

DECLARE @.date char(8)

DECLARE @.time char(8)

DECLARE @.sql VARCHAR(8000)

SELECT @.date = CONVERT(char(8), getdate(),112)

SELECT @.time = CONVERT(char(8), getdate(),108)

SELECT @.time = REPLACE(@.time,':','')

SELECT @.time

DECLARE @.dt char(14)

SELECT @.dt = @.date + '_' + @.time

SELECT @.sql = 'bcp avk..tempProducts out "c:\AVK_' + @.dt + '.txt" -c -t, -U sa -P dalla -S'

EXEC master..xp_cmdshell @.sql

GO

Then go into your another query window (which is a separate transaction) and execute an update statement on products

Then return to the main window, highlight and execute the final section

Code Snippet

USE AVK

GO

DELETE

FROM tempProducts

GO

COMMIT TRANSACTION

This will give you the behaviour as if an update to your products tabla occured while your second transaction was running and will allow you to view how the different isolation levels affect your results.

As for debugging a la VB, i think you are able to use this facility for stored procedures in Visual Studio but not SSMS.

HTH!

|||

WOHO!

Works like a charm!

Tried updating 3 records, then running the BCP-part of the query. 3 rows out as expected.

The updated 3 more records, did a select * on tempProducts, which now contained 6 records.

Finally ran the delete part of the query, checked tempProducts again. And yep, my last three updates where still there!

Thanks a bunch for all the help!

Problem with arithmetical calculations

Hello, everyone.

I have some experience working with T-SQL, but now I am faced with problem which I never seen before.

I have query:

SELECT2/(9+1)*1000

This query returns 0, but it must return 200.

I have tried this in SQL 2000 and SQL 2005, but result is the same.

What is wrong?

B.R.

Girts

I have found solution for this problem - it looks very simple and stypid :

SELECT 2/(9*1.0+1)*1000

|||

It's very simple, yes, but stupid? Not at all.

Have you thought about what you're calculating?
How much is 2 divided by 10?
How would one write 0.2 using only integers..? You can't, so it's rounded down to 0.

So your real problem is not how it's calculated, but what datatypes are used to do the calculation. When you add the decimalpoint as you found out, it's no longer done by all integers, but with decimals instead, so the rounding doesn't have to happen.
Thus you get the expected result. =:o)

You can find out more in BOL if you look for 'datatypes' and 'datatype precedence'

=;o)
/Kenneth

|||

Well, this is kind of wierd looking (the bold):

SELECT 2/(9*1.0+1)*1000

But like KeWin is saying it does the trick because it causes the 1.0 to be converted to a numeric value. The same effect can be achieved by:

SELECT 2.0/(9+1)*1000

You really have to be careful to consider the datatype at each step of the evaluation of the equation.

Integer + Integer = Integer
Integer / Integer = Integer

select 1/2 = 0 --because in integer math, you truncate the result, not round
select 1%2 = 1 --% is the mod function, gives you the remainder of the operation
--mod is essential when working with integers

Now consider:

select 1.0 / 1 = 1.000000

Numeric / Integer = Numeric

A trick to use to see the exact type is to use a sql_variant dataype:

declare @.value sql_variant
set @.value = 1.0 / 1

--give precision for integer even though not technically part of datatype
select cast(sql_variant_property(@.value,'baseType') as varchar(10))+
'(' + cast(sql_variant_property(@.value,'precision') as varchar(10))+ ',' +
cast (sql_variant_property(@.value,'scale') as varchar(10)) + ')'

select @.value

Using this query you can see what type is chosen for the output of an expression, either a scalar value, or a mathematic equation.

For the 1.0 / 1 before, this returns:

1.000000

SQL Server chooses a datatype that will always be able to store the result of the equation safely with no loss of precision if possible.

The point is that you have to be really careful with math because one little mess up on a type and your numbers are meaningless:

select (1/2) * 5000.00
select (1.0/2.0) * 5000

the first one is 0, because 1/2 returns integer 0 * numeric 5000.00 = 0.00

the second one 2500.000000, since 1.0/2.0 returns numeric(8,6) 0.500000 * 5000

Hopefully this is a bit clear. Datatype conversion can be tricky. In BOL (for 2005), look up Data Types [SQL Server]; Converting. There is an implicit conversion char to tell you what types will convert to what types as needed.