TSQL query XML file

• adb2303

  SSCertifiable

  Points: 7588

  More actions

  March 7, 2012 at 7:29 am

  #252717

  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,

• Schadenfreude-Mei

  SSChampion

  Points: 14083

  More actions

  March 7, 2012 at 10:02 am

  #1456115

  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

• adb2303

  SSCertifiable

  Points: 7588

  More actions

  March 8, 2012 at 3:27 am

  #1456418

  anyone any ideas? thanks

• MNH

  SSCommitted

  Points: 1663

  More actions

  March 8, 2012 at 4:41 pm

  #1456879

  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.

• adb2303

  SSCertifiable

  Points: 7588

  More actions

  March 9, 2012 at 3:12 am

  #1457036

  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