XML Data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

XML Data

Author

Topic

djcarpen11
Starting Member

25 Posts

Posted - 2012-02-03 : 04:11:17

Hi i'm having issues trying to shred some xml created by trying to catch procedures that don't complete. in the real world code there would be multiple EVENT tags, what I'm trying to achieve is get the value from each Data and action tag into one row along with the timestamp from the event tag like the table below.

source_database_id¦object_id¦session_id¦sql_text¦timestamp
5 ¦ 8272 ¦ 63 ¦ sp_test¦ 2012-02-02T

PairingTarget truncated="0" orphanCount="5" matchedCount="17421" memoryPressureDroppedCount="0">
<event name="sp_statement_starting" package="sqlserver" id="61" version="1" timestamp="2012-02-02T15:35:32.266Z">
<data name="source_database_id">
<type name="uint16" package="package0" />
<value>5</value>
<text />
</data>
<data name="object_id">
<type name="uint32" package="package0" />
<value>1110867620</value>
<text />
</data>
<data name="object_type">
<type name="uint16" package="package0" />
<value>8272</value>
<text />
</data>
<data name="state">
<type name="statement_state" package="sqlserver" />
<value>0</value>
<text>Normal</text>
</data>
<data name="offset">
<type name="int32" package="package0" />
<value>98</value>
<text />
</data>
<data name="offset_end">
<type name="int32" package="package0" />
<value>150</value>
<text />
</data>
<data name="nest_level">
<type name="int32" package="package0" />
<value>1</value>
<text />
</data>
<action name="session_id" package="sqlserver">
<type name="uint16" package="package0" />
<value>63</value>
<text />
</action>
<action name="sql_text" package="sqlserver">
<type name="unicode_string" package="package0" />
<value>sp_davetest</value>
<text />
</action>
</event>
</PairingTarget>

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-03 : 06:50:05

If this data is in an XML variable, query directly the variable as in:

SELECT
	c.value('(event/data[@name="source_database_id"]/value)[1]','varchar(256)'),
	c.value('(event/data[@name="object_id"]/value)[1]','varchar(256)'),
	--- and similar constructs for you other columns
	c.value('(event)[1]/@timestamp','varchar(256)')
FROM
	@xmlVariable.nodes('PairingTarget') T(c);

If the data is in a table, shred each row using CROSS APPLY by changing the where clause to:

FROM
	YourTable y
	CROSS APPLY y.XMLColumn.nodes('PairingTarget') T(c);

djcarpen11
Starting Member

25 Posts

Posted - 2012-02-03 : 07:01:09

Thanks the reply, How do I select the all the xml records not just the first one? Obviously the VALUE[1] needs changing but I'm not sure to what.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-03 : 08:03:58

Can you post a representative sample of the actual data that shows the XML structure?

What I am trying to find is the structure of the XML when you have multiple records. Is it several PairingTarget nodes, or several event nodes under a single PairingTarget node, or several data nodes with same names under each event node, or some combination of those

djcarpen11
Starting Member

25 Posts

Posted - 2012-02-03 : 09:12:36

Thanks the reply the xml will look like the below

<PairingTarget truncated="0" orphanCount="3" matchedCount="16358" memoryPressureDroppedCount="0">
<event name="sp_statement_starting" package="sqlserver" id="61" version="1" timestamp="2012-02-02T15:35:32.266Z">
<data name="source_database_id">
<type name="uint16" package="package0" />
<value>5</value>
<text />
</data>
<data name="object_id">
<type name="uint32" package="package0" />
<value>1110867620</value>
<text />
</data>
<data name="object_type">
<type name="uint16" package="package0" />
<value>8272</value>
<text />
</data>
<data name="state">
<type name="statement_state" package="sqlserver" />
<value>0</value>
<text>Normal</text>
</data>
<data name="offset">
<type name="int32" package="package0" />
<value>98</value>
<text />
</data>
<data name="offset_end">
<type name="int32" package="package0" />
<value>150</value>
<text />
</data>
<data name="nest_level">
<type name="int32" package="package0" />
<value>1</value>
<text />
</data>
<action name="session_id" package="sqlserver">
<type name="uint16" package="package0" />
<value>63</value>
<text />
</action>
<action name="sql_text" package="sqlserver">
<type name="unicode_string" package="package0" />
<value>sp_davetest</value>
<text />
</action>
</event>
<event name="sp_statement_starting" package="sqlserver" id="61" version="1" timestamp="2012-02-03T11:26:48.590Z">
<data name="source_database_id">
<type name="uint16" package="package0" />
<value>5</value>
<text />
</data>
<data name="object_id">
<type name="uint32" package="package0" />
<value>720288514</value>
<text />
</data>
<data name="object_type">
<type name="uint16" package="package0" />
<value>20816</value>
<text />
</data>
<data name="state">
<type name="statement_state" package="sqlserver" />
<value>0</value>
<text>Normal</text>
</data>
<data name="offset">
<type name="int32" package="package0" />
<value>366</value>
<text />
</data>
<data name="offset_end">
<type name="int32" package="package0" />
<value>462</value>
<text />
</data>
<data name="nest_level">
<type name="int32" package="package0" />
<value>2</value>
<text />
</data>
<action name="session_id" package="sqlserver">
<type name="uint16" package="package0" />
<value>56</value>
<text />
</action>
<action name="sql_text" package="sqlserver">
<type name="unicode_string" package="package0" />
<value>sp_testproc
</value>
<text />
</action>
</event>
<event name="sp_statement_starting" package="sqlserver" id="61" version="1" timestamp="2012-02-02T10:54:33.917Z">
<data name="source_database_id">
<type name="uint16" package="package0" />
<value>5</value>
<text />
</data>
<data name="object_id">
<type name="uint32" package="package0" />
<value>1110867620</value>
<text />
</data>
<data name="object_type">
<type name="uint16" package="package0" />
<value>8272</value>
<text />
</data>
<data name="state">
<type name="statement_state" package="sqlserver" />
<value>0</value>
<text>Normal</text>
</data>
<data name="offset">
<type name="int32" package="package0" />
<value>98</value>
<text />
</data>
<data name="offset_end">
<type name="int32" package="package0" />
<value>150</value>
<text />
</data>
<data name="nest_level">
<type name="int32" package="package0" />
<value>1</value>
<text />
</data>
<action name="session_id" package="sqlserver">
<type name="uint16" package="package0" />
<value>71</value>
<text />
</action>
<action name="sql_text" package="sqlserver">
<type name="unicode_string" package="package0" />
<value>
DECLARE @return_value int

EXEC @return_value = [dbo].[sp_davetest]

SELECT 'Return Value' = @return_value

</value>
<text />
</action>
</event>
</PairingTarget>

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-02-03 : 12:18:43

best way to understand what goes on under the hood is as follows. you will see the guts of the xml


DECLARE @xml_table TABLE(xml_data xml)

DECLARE @xmlDoc NVARCHAR(MAX), @handle INT, @sqlstmt NVARCHAR(MAX)


SET @xmlDoc = 
'<PairingTarget truncated="0" orphanCount="3" matchedCount="16358" memoryPressureDroppedCount="0">
<event name="sp_statement_starting" package="sqlserver" id="61" version="1" timestamp="2012-02-02T15:35:32.266Z">
<data name="source_database_id">
<type name="uint16" package="package0" />
<value>5</value>
<text />
</data>
<data name="object_id">
<type name="uint32" package="package0" />
<value>1110867620</value>
<text />
</data>
<data name="object_type">
<type name="uint16" package="package0" />
<value>8272</value>
<text />
</data>
<data name="state">
<type name="statement_state" package="sqlserver" />
<value>0</value>
<text>Normal</text>
</data>
<data name="offset">
<type name="int32" package="package0" />
<value>98</value>
<text />
</data>
<data name="offset_end">
<type name="int32" package="package0" />
<value>150</value>
<text />
</data>
<data name="nest_level">
<type name="int32" package="package0" />
<value>1</value>
<text />
</data>
<action name="session_id" package="sqlserver">
<type name="uint16" package="package0" />
<value>63</value>
<text />
</action>
<action name="sql_text" package="sqlserver">
<type name="unicode_string" package="package0" />
<value>sp_davetest</value>
<text />
</action>
</event>
<event name="sp_statement_starting" package="sqlserver" id="61" version="1" timestamp="2012-02-03T11:26:48.590Z">
<data name="source_database_id">
<type name="uint16" package="package0" />
<value>5</value>
<text />
</data>
<data name="object_id">
<type name="uint32" package="package0" />
<value>720288514</value>
<text />
</data>
<data name="object_type">
<type name="uint16" package="package0" />
<value>20816</value>
<text />
</data>
<data name="state">
<type name="statement_state" package="sqlserver" />
<value>0</value>
<text>Normal</text>
</data>
<data name="offset">
<type name="int32" package="package0" />
<value>366</value>
<text />
</data>
<data name="offset_end">
<type name="int32" package="package0" />
<value>462</value>
<text />
</data>
<data name="nest_level">
<type name="int32" package="package0" />
<value>2</value>
<text />
</data>
<action name="session_id" package="sqlserver">
<type name="uint16" package="package0" />
<value>56</value>
<text />
</action>
<action name="sql_text" package="sqlserver">
<type name="unicode_string" package="package0" />
<value>sp_testproc
</value>
<text />
</action>
</event>
</PairingTarget>'

EXEC sp_xml_preparedocument @handle OUTPUT, @xmlDoc

--Songs from 
SELECT *
  FROM OPENXML (@handle, '/PairingTarget/event',2)
  --WITH (eventname  varchar(50) '@name',
  --      dataname varchar(50) 'data/@name') a
--where dataname = 'source_database_id'

EXEC sp_xml_removedocument @handle

If you don't have the passion to help people, you have no passion

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-03 : 13:08:52

quote:
Originally posted by djcarpen11

Thanks the reply the xml will look like the below

<PairingTarget truncated="0" orphanCount="3" matchedCount="16358" memoryPressureDroppedCount="0">
<event name="sp_statement_starting" package="sqlserver" id="61" version="1" timestamp="2012-02-02T15:35:32.266Z">
<data name="source_database_id">
<type name="uint16" package="package0" />
<value>5</value>
<text />
</data>
<data name="object_id">
<type name="uint32" package="package0" />
<value>1110867620</value>
....

The XML you posted was not well-formed, so I had to make some modifications (which may not have been the correct modifications). But what it seems to me is that you have one root node (PairingTarget), several event nodes under that, and each event node has several data items, each with a distinct name attribute and a single value node. If that is true:

SELECT
	c1.value('(data[@name="source_database_id"]/value)[1]','varchar(256)'),
	c1.value('(data[@name="object_id"]/value)[1]','varchar(256)'),
	--- and similar constructs for you other columns
	c.value('(event)[1]/@timestamp','varchar(256)')
FROM
	@x.nodes('PairingTarget') T(c)
	CROSS APPLY c.nodes('event') T1(c1)

In this, the first "table" (T) is shredding down to get all the nodes under PairingTarget. The second table (T1) is shredding each node that is in the first table T.

djcarpen11
Starting Member

25 Posts

Posted - 2012-02-07 : 04:30:26

Thanks for the reply, The code works but it only pulls through the first timestamp for all columns. The xml is being generated using SQL Servers 2008 extended events so I can't change the way it's formed. Any ideas?

Thanks

Dave

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-07 : 07:05:02

Shred it down to event level - and so then you would not need the cross apply unless there are many data nodes that have the same name attribute within a single event

SELECT
	c.value('(data[@name="source_database_id"]/value)[1]','varchar(256)'),
	c.value('(data[@name="object_id"]/value)[1]','varchar(256)'),
	--- and similar constructs for you other columns
	c.value('@timestamp','varchar(256)')
FROM
	@x.nodes('PairingTarget/event') T(c)

djcarpen11
Starting Member

25 Posts

Posted - 2012-02-08 : 03:22:30

thanks for the help, I've managed to get it working using the below code.

SELECT

n.value('(event/action[@name="session_id"]/value)[1]', 'int') AS session_id,
n.value('(event/@timestamp)[1]', 'datetime2') AS 'DateRan' ,
n.value('(event/data[@name="source_database_id"]/value)[1]', 'int') AS [source_database_id],
n.value('(event/data[@name="object_id"]/value)[1]', 'int') AS [OBJECT_ID],
n.value('(event/action[@name="sql_text"]/value)[1]', 'varchar(max)') AS SQL_Code

FROM
( SELECT td.query('.') AS n
FROM @target_data.nodes('PairingTarget/event') AS q(td)
) AS tab

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources