March 7, 2012 at 7:29 am
Hi,
I have the following XML file stored on disk ,i.e. c:\myxml.xml. I want to query the file and retrieve only the 'currentdb' information from the elements listed. I can get individual elements fine, but am stuck getting this individual part'
<EVENT_INSTANCE>
<EventType>DEADLOCK_GRAPH</EventType>
<PostTime>2011-09-30T10:46:17.697</PostTime>
<SPID>22</SPID>
<TextData>
<deadlock-list>
<deadlock victim="process969018">
<process-list>
<process id="process968d48" taskpriority="0" logused="240" waitresource="RID: 4:1:1010:0" waittime="7468" ownerId="12218309" transactionname="user_transaction" lasttranstarted="2011-09-30T10:46:01.443" XDES="0x8475400" lockMode="S" schedulerid="2" kpid="13744" status="suspended" spid="54" sbid="0" ecid="0" priority="0" transcount="1" lastbatchstarted="2011-09-30T10:46:10.217" lastbatchcompleted="2011-09-30T10:46:01.447" clientapp="Microsoft SQL Server Management Studio - Query" hostname="myworkstationname" hostpid="13404" loginname="myaduser" isolationlevel="read committed (2)" xactid="12218309" currentdb="4" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
<executionStack>
<frame procname="adhoc" line="1" sqlhandle="0x02000000f4d34532698b7ad324df813feb2ba5024730cb79">
SELECT * FROM t2; </frame>
</executionStack>
<inputbuf>
SELECT * FROM t2;
</inputbuf>
</process>
<process id="process969018" taskpriority="0" logused="240" waitresource="RID: 4:1:1027:0" waittime="3921" ownerId="12218349" transactionname="user_transaction" lasttranstarted="2011-09-30T10:46:06.023" XDES="0x84759a8" lockMode="S" schedulerid="2" kpid="16024" status="suspended" spid="55" sbid="0" ecid="0" priority="0" transcount="1" lastbatchstarted="2011-09-30T10:46:13.763" lastbatchcompleted="2011-09-30T10:46:06.027" clientapp="Microsoft SQL Server Management Studio - Query" hostname="myworkstationname" hostpid="13404" loginname="myaduser" isolationlevel="read committed (2)" xactid="12218349" currentdb="4" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
<executionStack>
<frame procname="adhoc" line="1" sqlhandle="0x02000000c70b7b1daba27f2ddf3e772600949464d524b6f2">
SELECT * FROM t1; </frame>
</executionStack>
<inputbuf>
SELECT * FROM t1;
</inputbuf>
</process>
</process-list>
<resource-list>
<ridlock fileid="1" pageid="1027" dbid="4" objectname="msdb.dbo.t1" id="lock3cd2380" mode="X" associatedObjectId="72057594055163904">
<owner-list>
<owner id="process968d48" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process969018" mode="S" requestType="wait" />
</waiter-list>
</ridlock>
<ridlock fileid="1" pageid="1010" dbid="4" objectname="msdb.dbo.t2" id="lock3cd40c0" mode="X" associatedObjectId="72057594055229440">
<owner-list>
<owner id="process969018" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process968d48" mode="S" requestType="wait" />
</waiter-list>
</ridlock>
</resource-list>
</deadlock>
</deadlock-list>
</TextData>
<TransactionID />
<LoginName>sa</LoginName>
<StartTime>2011-09-30T10:46:17.697</StartTime>
<ServerName>myworkstationname\SQL2005</ServerName>
<LoginSid>AQ==</LoginSid>
<EventSequence>11314506</EventSequence>
<IsSystem>1</IsSystem>
<SessionLoginName />
</EVENT_INSTANCE>
I'm currently using this:
DECLARE @xmlFile XML
SET @xmlFile = (SELECT * FROM OPENROWSET(BULK 'c:\myxml.xml', SINGLE_CLOB) AS xmldata)
SELECT ref.value('currentdb[1]', 'INT') AS DatabaseID
FROM @xmlFile.nodes('EVENT_INSTANCE/TextData/deadlock-list/*') xmldata(ref)
but I keep getting back NULL when I'm trying to get back '4' (or even better, the database name)
Thanks,
March 7, 2012 at 10:02 am
Did you have any luck with this?
I tried the following:
DECLARE @xmlFile XML
SET @xmlFile = (SELECT * FROM OPENROWSET(BULK 'c:\myxml.xml', SINGLE_CLOB) AS xmldata)
SELECT ref.value('currentdb[1]', 'INT') AS DatabaseID
FROM @xmlFile.nodes('EVENT_INSTANCE/TextData/deadlock-list/deadlock victim/process list/*') xmldata(ref)
And got:
Msg 2209, Level 16, State 1, Line 5
XQuery [nodes()]: Syntax error near 'deadlock'
Im guessing this is down to the ' ' in the "deadlock victime" and "process list" elements.
Adam Zacks-------------------------------------------Be Nice, Or Leave
March 8, 2012 at 3:27 am
anyone any ideas? thanks
March 8, 2012 at 4:41 pm
The following format will get the desired info.
DECLARE @xmlFile XML
SET @xmlFile = (SELECT * FROM OPENROWSET(BULK 'c:\myxml.xml', SINGLE_CLOB) AS xmldata)
SELECT @xmlFile.value('(//*/process/@currentdb)[1]', 'varchar(10)') AS DatabaseID
Refer to http://msdn.microsoft.com/en-us/library/ms178030.aspx for details.
March 9, 2012 at 3:12 am
MNH (3/8/2012)
The following format will get the desired info.
DECLARE @xmlFile XML
SET @xmlFile = (SELECT * FROM OPENROWSET(BULK 'c:\myxml.xml', SINGLE_CLOB) AS xmldata)
SELECT @xmlFile.value('(//*/process/@currentdb)[1]', 'varchar(10)') AS DatabaseID
Refer to http://msdn.microsoft.com/en-us/library/ms178030.aspx for details.
fantastic... thanks a lot
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy