XQuery Error - Trying to parse XML column

[Mitchell_Collen via SQLMonster.com]

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".

TextData <---- column used
**************************************************************************

<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="0x000000000000000000000000000000000000000000000000" />
</executionStack>
<inputbuf />
</process>
</blocked-process>
<blocking-process>
<process status="suspended" spid="110" sbid="0" ecid="0" priority="0"
transcount="0" lastbatchstarted="2010-08-23T16:49:32.420"
lastbatchcompleted="2010-08-23T16:49:32.420" clientapp="SQLAgent - TSQL
JobStep (Job 0x9E71065CF90DAE4B8DFFF1B805EA7951 : Step 1)" hostname="QTKSRV5"
hostpid="3840" loginname="NT AUTHORITY\SYSTEM" isolationlevel="read committed
(2)" xactid="0" currentdb="5" lockTimeout="4294967295"
clientoption1="536873056" clientoption2="128056">
<executionStack>
<frame line="1"
sqlhandle="0x01000500b0b75d01287a343f000000000000000000000000" />
</executionStack>
<inputbuf>
BACKUP LOG claims TO Claims_Log WITH SKIP, INIT </inputbuf>
</process>
</blocking-process>
</blocked-process-report>

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

[Martin Honnen]

Mitchell_Collen via SQLMonster.com wrote:
> 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.

> <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="0x000000000000000000000000000000000000000000000000" />
> </executionStack>
> <inputbuf />
> </process>
> </blocked-process>

--

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

[Mitchell_Collen via SQLMonster.com]

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