XQuery Error - .VALUE not recognized

[RRinTetons]

Continuing in my quest to access data stored as XML.

Using an example from

http://beyondrelational.com

I get very close.

DECLARE @t TABLE (DATA XML)

INSERT INTO @t([DATA])
        
SELECT [ox].[XMLText]
FROM [dbo].[OrderXML] AS ox

SELECT [DATA].[VALUE]('/MyApp.Framework.DAL.Order/ClientCode','CHAR(3)') AS [ClientCode]
FROM @t

The xpath was extracted using XMLSpy and has been shown to work to locate the data. The problem is the final SELECT. I *think* what I should get is a list of the ClientCodes from all the Orders stored in the XML. When I execute that I get:

Msg 227, Level 15, State 1, Line 11
"VALUE" is not a valid function, property, or field.

I'm cookbooking this from the beyondrelational site's example, so I could be making some really obvious mistake, but it seems like it should work.

????


-
Richard Ray
Jackson Hole Mountain Resort

 

[markros]

value should be in a lower case

value

-------------
May be BeyondRelational has the same problem as LTD site had before the Admin fixed it. Somehow the plugin for the SQL code converted value in lower case to upper case, but in XML it's important to have in the lower case.

If you can give me the exact link where you found the problem, I'll notify Jacob and may be he will be able to apply the same fix.

PluralSight Learning Library

 

[RRinTetons]

I think my SQL editor did it. I forced it back to lower case, and now I get:

Msg 2389, Level 16, State 1, Line 12
XQuery [@t.DATA.value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

I promise that if I can get this to work I'll figure out exactly what I did! For now I'm just groping my way.



-
Richard Ray
Jackson Hole Mountain Resort

 

[markros]

If you can post a sample if the XML in one of the column, I may try to figure this out, although I don't use XML a lot, so I need myself to work out based on the examples.

PluralSight Learning Library

 

[tsuji]

[0] First the function name "value" is case-sensitive, you cannot put it otherwise.

[1] value() exiges the xpath capable of resulting a "single" scalar value.
[tt]
SELECT [DATA].[red]value[/red]('[red]([/red]/MyApp.Framework.DAL.Order/ClientCode[red])[1][/red]','CHAR(3)') AS [ClientCode]
FROM @t[/tt]

[1.1] I don't see the point of square-bracketing the .value. You can do, but, the hesitation will be created by your own.

 

[RRinTetons]

OK, two things fixed it. It works like this:

DECLARE @t TABLE (DATA XML)

INSERT INTO @t([DATA])
        
SELECT CAST([ox].[XMLText] AS XML)
FROM [dbo].[OrderXML] AS ox

SELECT [DATA].value('(//ClientCode)[1]','INT') AS [Client Code]
FROM @t

Changing the name to '.value' (lowercase) was definitely crucial.

I thought CASTing the NVARCHAR(MAX) column as as XML type might help, but now it seems to work without that.

I really don't know why dropping the initial part of the xPath string or adding what looks like an extra paren inside the single quotes was critical, but that seemed to turn the trick.

Getting the [1] in there to convert it to an explicit singleton was crucial.

I don't really know why some of those changes worked, but it just goes to show that enough hacking about will eventually get the results. Sometimes...

-
Richard Ray
Jackson Hole Mountain Resort

 

[tsuji]

If you further had constructed a wrong xpath on what shown in original post - in addition to those mistakes highlighted by the responses - no one can help you as you did not show the xml. It is no hack, enough or not enough.

 

[RRinTetons]

The XML data I'm working with is over 100,000 characters long. Where can I post that so that you can see it?

-
Richard Ray
Jackson Hole Mountain Resort

 

[markros]

Is it one XML column or the whole XML? I think you could have posted the beginning of XML then.

PluralSight Learning Library

 

[tsuji]

>The XML data I'm working with is over 100,000 characters long. Where can I post that so that you can see it?
Does it matter? It is not the size. It is the generic structure. Is there a tradition of fetish of size in programming?

 

[tsuji]

Maybe that's why you ask O(10^2) of questions and only find one useful?

 

[RRinTetons]

Dear Sir:

I apologize that my lack of knowledge has offended you. If I knew what part of the large block of XML that is stored in each row of the table was causing my problem I assure you I would have posted only that portion. I don't, so I am, admittedly floundering.

I will close this thread and seek elsewhere for assistance.

-
Richard Ray
Jackson Hole Mountain Resort