Re: XQuery Error - Trying to parse XML column

#1

Mitchell_Collen via SQLMonster.com wrote:

Quote:

Hi SQL Monsters
I am getting the following error when I run my query. The query is supposed
to be pulling the waitresource data ("FILE: 5:0") from the XML formatted
column name TextData. Do you know what I could be doing wrong? Thanks in
advance. MC

SELECT X.x.value('waitresource')
FROM dbo.BLOCKING_EVENTS
CROSS APPLY TextData.nodes('/blocked-process-report/') AS X(x)

Msg 2256, Level 16, State 1, Line 3
XQuery [dbo.BLOCKING_EVENTS.TextData.nodes()]: Syntax error near '<eof>',
expected a "node test".

The argument to the nodes method should be a path expression like e.g.
'/blocked-process-report'. Your argument ends with '/', that is not a
syntactically correct path expression. You might want e.g.

SELECT X.x.value('@waitresource', 'nvarchar(20)')
FROM dbo.BLOCKING_EVENTS
CROSS APPLY
TextData.nodes('/blocked-process-report/blocked-process/process') AS X(x)

to use a path that is more likely to give a meaningful result with the
XML sample you show below.

Quote:

blocked-process-report monitorLoop="29629"
blocked-process
process id="process84a7a8" taskpriority="0" logused="10000"
waitresource="FILE: 5:0" waittime="76812" schedulerid="1" kpid="3656"
status="background" spid="11" sbid="0" ecid="0" priority="0" transcount="0"
executionStack
frame line="1"
sqlhandle="0x0000000000000000000000000000000000000 00000000000" /
/executionStack
inputbuf /
/process
/blocked-process

--

Martin Honnen --- MVP Data Platform Development
http://msmvps.com/blogs/martin_honnen/

#2

Martin, you are a genius! Thanks! It did exactly what I need, now I can
expand on that in my query!

Thanks so much!

MC

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums...r-xml/201008/1

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: XQuery Error - Trying to parse XML column

microsoft.public.sqlserver.xml microsoft.public.sqlserver.xml

Re: XQuery Error - Trying to parse XML column - 08-31-2010 , 07:07 AM

Re: XQuery Error - Trying to parse XML column - 08-31-2010 , 08:08 AM