Select Data in XML

Question

Post reply

Select Data in XML

JKSQL

SSCrazy Eights

Points: 8481
More actions
May 29, 2009 at 9:05 am

#134475

I was trying to use xquery to extract the data from a xml field so that i could use that data in another field.
XML:
1.5
I was trying to select it like this per msdn
SELECT Extension.Value('Data((/doc//Extension/Length)[1]','Nvarchar(max)')
FROM [dbo].[Test]
This is the message.
Msg 227, Level 15, State 1, Line 1
"Value" is not a valid function, property, or field.
Any help would be great

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

XQuery is case-sensitive.

"Value" needs to be "value" (all lower-case), "Extension" needs to be "EXTENSION" in the value query, and so on.

Here's a working version:

select cast('1.5' as XML) as Extension

into #T;

--

select Extension.value('(/EXTENSION/.)[1]','Nvarchar(max)')

from #T;

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 2

X-Query is case sensitive, so all the methods and the data elements used in the xml must match with that used in the methods. For e.g.

The method "Value" should "value" and the xml element "Extension" should be "extension" and also the "Data" method is not a valid method (i suppose).

The working code for the sample:

DECLARE @SomeXML XML

SET @SomeXML = '

1.5

'

SELECT@SomeXML.value('(/extension/length)[1]','Nvarchar(max)')

Edit:

Just a little too late to post!!!:-)

Gus, does you mean the element "Extension" should be changed to "extension" (same as in xml) and not to "EXTENSION"?

--Ramesh

JKSQL SSCrazy Eights Points: 8481 More actions · Answer 3

JKSQL

SSCrazy Eights

Points: 8481

May 29, 2009 at 10:05 am

#1002154

Awesome thanks. Works great.

GSquared SSC Guru Points: 260824 More actions · Answer 4

Ramesh (5/29/2009)
X-Query is case sensitive, so all the methods and the data elements used in the xml must match with that used in the methods. For e.g.
The method "Value" should "value" and the xml element "Extension" should be "extension" and also the "Data" method is not a valid method (i suppose).
The working code for the sample:
DECLARE @SomeXML XML
SET @SomeXML = '
1.5
'
SELECT@SomeXML.value('(/extension/length)[1]','Nvarchar(max)')
Edit:
Just a little too late to post!!!:-)
Gus, does you mean the element "Extension" should be changed to "extension" (same as in xml) and not to "EXTENSION"?

In the OP's example XML, the elements were uppercase on my screen. That might be something with the scripts on the page, but it's how they show on my screen, so it's what I used.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

LutzM SSC Guru Points: 107049 More actions · Answer 5

GSquared (5/29/2009)
In the OP's example XML, the elements were uppercase on my screen. That might be something with the scripts on the page, but it's how they show on my screen, so it's what I used.

As stated by Flo in another thread it's caused by the java-script client-side render engine.

Easiest way to find the original xml format is using the quote button - it'll show the original xml (learned from Flo again...)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004435 More actions · Answer 6

Heh... "XML is case sensitive"... one more reason for me to keep it out of my databases. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 7

GSquared (5/29/2009)
In the OP's example XML, the elements were uppercase on my screen. That might be something with the scripts on the page, but it's how they show on my screen, so it's what I used.

I knew there must be something I am missing when I saw your post. Never seen such rendering on my machine (Or may be I never noticed it), looks strange to me.

--Ramesh