Red Gate forums :: View topic - (XML) value statement is autom. converted to uppercase!
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Prompt Previous Versions
SQL Prompt Previous Versions forum

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

Search in SQL Prompt Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
TSchwab



Joined: 11 Jan 2008
Posts: 62
Location: Germany

PostPosted: Mon Feb 01, 2010 3:56 pm    Post subject: (XML) value statement is autom. converted to uppercase! Reply with quote

Crying or Very sad

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

Code:

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


is changed to this:
Code:

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
Back to top
View user's profile Send private message
Anu Deshpande



Joined: 20 Apr 2009
Posts: 685
Location: Cambridge

PostPosted: Tue Feb 02, 2010 4:52 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
TSchwab



Joined: 11 Jan 2008
Posts: 62
Location: Germany

PostPosted: Wed Feb 03, 2010 9:18 am    Post subject: Reply with quote

Hello Anu,

that does not help at all. Sad
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. Crying or Very sad

Thanks.
Thorsten
_________________
Regards,

Thorsten
Back to top
View user's profile Send private message
Anu Deshpande



Joined: 20 Apr 2009
Posts: 685
Location: Cambridge

PostPosted: Fri Feb 05, 2010 1:46 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
TSchwab



Joined: 11 Jan 2008
Posts: 62
Location: Germany

PostPosted: Fri Feb 05, 2010 4:22 pm    Post subject: Re: Reply with quote

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:

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:

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 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
Back to top
View user's profile Send private message
mscheuner



Joined: 07 Dec 2005
Posts: 39
Location: Switzerland

PostPosted: Wed Feb 24, 2010 10:27 am    Post subject: Reply with quote

Thorsten,

I've been pestering Red Gate about this behavior ever since they took over SQL Prompt - without any success, so far Sad 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/viewtopic.php?t=9434


Last edited by mscheuner on Wed Feb 24, 2010 10:31 am; edited 1 time in total
Back to top
View user's profile Send private message
TSchwab



Joined: 11 Jan 2008
Posts: 62
Location: Germany

PostPosted: Wed Feb 24, 2010 10:30 am    Post subject: Reply with quote

Hi,

you are absolutly right. Escpecially that the problem also occures with the keyword modify... Sad Shocked

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

Thorsten
Back to top
View user's profile Send private message
Anu Deshpande



Joined: 20 Apr 2009
Posts: 685
Location: Cambridge

PostPosted: Wed Feb 24, 2010 10:40 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
TSchwab



Joined: 11 Jan 2008
Posts: 62
Location: Germany

PostPosted: Thu Apr 08, 2010 10:41 am    Post subject: Re: Reply with quote

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
Back to top
View user's profile Send private message
CodantiM



Joined: 29 Jun 2007
Posts: 180

PostPosted: Fri Apr 09, 2010 10:39 pm    Post subject: Reply with quote

Hopefully you will get a better answer than I did:

Quote:
...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.
Back to top
View user's profile Send private message
TSchwab



Joined: 11 Jan 2008
Posts: 62
Location: Germany

PostPosted: Thu Apr 15, 2010 11:15 am    Post subject: Reply with quote

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

Thorsten
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group