(XML) value statement is autom. converted to uppercase!

Provides intelligent code completion for SQL Server editors.

Moderators: David Atkinson, Anu Deshpande, Luke Jefferson

(XML) value statement is autom. converted to uppercase!

Postby TSchwab » Mon Feb 01, 2010 3:56 pm

:cry:

When I use an XML variable and try to create a SELECT like this:

Code: Select all
SELECT T.C.value('@Test', 'INT') as Test
FROM @XML.nodes('V') T(C)


is changed to this:
Code: Select all
SELECT T.C.VALUE('@Test', 'INT') as Test
FROM @XML.nodes('V') T(C)



The keyword value is always converted into (upper case) VALUE and that doesn't work for XML.
It's very anoying... I have to manually change it back to lower case...

Please, help.

Thanks,
Thorsten
Regards,

Thorsten
TSchwab
 
Posts: 62
Joined: Fri Jan 11, 2008 9:47 am
Location: Germany

Postby Anu Deshpande » Tue Feb 02, 2010 4:52 pm

Many thanks for your post.

Can you try the folowing settings:

1. SQL Prompt 4 --> Options --> Format --> Case --> Reserved Keywords --> change to 'Leave as is'

2. SQL Prompt 4 --> Options --> Format --> Case --> Build-in functions --> change to 'Leave as is'

3. SQL Prompt 4 --> Options --> Format --> Case --> Build-in data types --> change to 'Leave as is'

And than try to replicate the issue and let me know if this fixes the issue.
Anuradha Deshpande
Product Support
Redgate Software Ltd.
E-mail: support@red-gate.com
Anu Deshpande
 
Posts: 692
Joined: Mon Apr 20, 2009 3:53 pm
Location: Cambridge

Postby TSchwab » Wed Feb 03, 2010 9:18 am

Hello Anu,

that does not help at all. :(
It works for the word'value but all the other keywords are kept in the case i'm typing them in.
That's not helpful. All other keywords, function and data-types are needed in upper case.

So i have to keep these settings with uppercase.
I don't understand why value is changed to upper case. It's not a reserved keyword, or a function or a data-type?

Sorry, but this solution makes it worse. :cry:

Thanks.
Thorsten
Regards,

Thorsten
TSchwab
 
Posts: 62
Joined: Fri Jan 11, 2008 9:47 am
Location: Germany

Postby Anu Deshpande » Fri Feb 05, 2010 1:46 pm

Apologies that the suggestion didn't help.

Can you send us the complete query so that we can understand the scenario and replicate the issue?
Anuradha Deshpande
Product Support
Redgate Software Ltd.
E-mail: support@red-gate.com
Anu Deshpande
 
Posts: 692
Joined: Mon Apr 20, 2009 3:53 pm
Location: Cambridge

Re:

Postby TSchwab » Fri Feb 05, 2010 4:22 pm

Anu Deshpande wrote:Apologies that the suggestion didn't help.

Can you send us the complete query so that we can understand the scenario and replicate the issue?


Here is a sample code:
Code: Select all
DECLARE @XML XML

SET @XML = (SELECT ID
                 , LastName
                 , FirstName
            FROM (  SELECT  1 AS ID
                          , 'LastName1' AS LastName
                          , 'FirstName1' AS FirstName
                    UNION
                    SELECT 2 AS ID
                          , 'LastName2' AS LastName
                          , 'FirstName2' AS FirstName
                    UNION
                    SELECT 3 AS ID
                          , 'LastName3' AS LastName
                          , 'FirstName3' AS FirstName) AS NameList
            FOR XML AUTO, TYPE
            )
           
           
SELECT T.C.VALUE('@ID', 'INT') AS ID
       , T.C.VALUE('@LastName', 'NVARCHAR(100)') AS LastName
       , T.C.VALUE('@FirstName', 'NVARCHAR(100)') AS FirstName
FROM @XML.nodes('/NameList') T(C)
           


If I execute the code I get the error message:
Code: Select all
Msg 227, Level 15, State 1, Line 21
"VALUE" is not a valid function, property, or field.


"value" is case sensitive and must be in lower case. SQLPrompt changes the value always to upper case.

It must look like this:
Code: Select all
SELECT T.C.value('@ID', 'INT') AS ID
       , T.C.value('@LastName', 'NVARCHAR(100)') AS LastName
       , T.C.value('@FirstName', 'NVARCHAR(100)') AS FirstName
FROM @XML.nodes('/NameList') T(C)
Regards,

Thorsten
TSchwab
 
Posts: 62
Joined: Fri Jan 11, 2008 9:47 am
Location: Germany

Postby mscheuner » Wed Feb 24, 2010 10:27 am

Thorsten,

I've been pestering Red Gate about this behavior ever since they took over SQL Prompt - without any success, so far :-( Glad to see another user also seems to agree this is an issue that needs to be fixed ASAP.

See this thread here - an \"enhancement request\" has already been logged - express your interest in it, then we might get them to finally fix it some day!!

http://www.red-gate.com/MessageBoard/vi ... php?t=9434
Last edited by mscheuner on Wed Feb 24, 2010 10:31 am, edited 1 time in total.
mscheuner
 
Posts: 39
Joined: Wed Dec 07, 2005 7:17 am
Location: Switzerland

Postby TSchwab » Wed Feb 24, 2010 10:30 am

Hi,

you are absolutly right. Escpecially that the problem also occures with the keyword modify... :( :shock:

I hope redgate will have a solution very soon.
Regards,

Thorsten
TSchwab
 
Posts: 62
Joined: Fri Jan 11, 2008 9:47 am
Location: Germany

Postby Anu Deshpande » Wed Feb 24, 2010 10:40 am

Apologies for the inconvenience caused by this issue.

It is logged in our internal tracking system (SP-2914).

We are working on this and I will update you as soon as it is fixed.

Many thanks for your patience.
Anuradha Deshpande
Product Support
Redgate Software Ltd.
E-mail: support@red-gate.com
Anu Deshpande
 
Posts: 692
Joined: Mon Apr 20, 2009 3:53 pm
Location: Cambridge

Re:

Postby TSchwab » Thu Apr 08, 2010 10:41 am

Anu Deshpande wrote:Apologies for the inconvenience caused by this issue.

It is logged in our internal tracking system (SP-2914).

We are working on this and I will update you as soon as it is fixed.

Many thanks for your patience.


Hi,
I was wondering if there is a time schedule for this error to be fixed?
Regards,

Thorsten
TSchwab
 
Posts: 62
Joined: Fri Jan 11, 2008 9:47 am
Location: Germany

Postby CodantiM » Fri Apr 09, 2010 10:39 pm

Hopefully you will get a better answer than I did:

...has advised me that there will more than likely be no layout requests / bugs fixed with this release.


The quote is referring to version 5 that they are planning to work on in the next quarter.
CodantiM
 
Posts: 182
Joined: Fri Jun 29, 2007 2:07 am

Postby TSchwab » Thu Apr 15, 2010 11:15 am

Gee... that's quite a long time... :shock: I guess I have to switch back to SQL-Prompt 3.9 which does not have this problem. :(
Regards,

Thorsten
TSchwab
 
Posts: 62
Joined: Fri Jan 11, 2008 9:47 am
Location: Germany


Return to SQL Prompt Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests