xquery - grab node attribute value and its grandchild node attribute values

Question

Post reply

xquery - grab node attribute value and its grandchild node attribute values

tmitchelar

Hall of Fame

Points: 3391
More actions
March 14, 2011 at 9:58 am

#99557

<views>
<view viewName="test_view" viewSequence="1" viewVersion="2.0" viewitem="US" viewStart="2011/02/16 15:00:56:056" viewEnd="2011/02/16 15:00:57:057" viewDuration="00:00:00.5468750">
<viewEvents>
<viewEvent subItemType="Shown" subItemValue="10" />
</viewEvents>
</view>
<view viewName="test2_view" viewSequence="3" viewVersion="2.0" viewCulture="US" viewStart="2011/02/16 15:00:59:059" viewEnd="2011/02/16 15:01:08:18" viewDuration="00:00:08.6250000">
<viewEvents>
<viewEvent subItemType="Change" subItemValue="browse-filter-family,alpha" />
<viewEvent subItemType="Shown" subItemValue="18" />
</viewEvents>
</view>
</views>
How would I grab the viewName attribute and the viewEvent subItemType attribute values?
What I'd like to return is
test_view Shown
test2_view Change
test2_view Shown
Ideas?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

This should get you there:

DECLARE @idoc INT ;

DECLARE @xml XML = N'

<views>

<view viewName="test_view" viewSequence="1" viewVersion="2.0" viewitem="US" viewStart="2011/02/16 15:00:56:056" viewEnd="2011/02/16 15:00:57:057" viewDuration="00:00:00.5468750">

<viewEvents>

<viewEvent subItemType="Shown" subItemValue="10" />

</viewEvents>

</view>

<view viewName="test2_view" viewSequence="3" viewVersion="2.0" viewCulture="US" viewStart="2011/02/16 15:00:59:059" viewEnd="2011/02/16 15:01:08:18" viewDuration="00:00:08.6250000">

<viewEvents>

<viewEvent subItemType="Change" subItemValue="browse-filter-family,alpha" />

<viewEvent subItemType="Shown" subItemValue="18" />

</viewEvents>

</view>

</views>' ;

EXEC sys.sp_xml_preparedocument

@idoc OUTPUT,

@xml

SELECT *

FROM OPENXML (@idoc, '/views/view/viewEvents/viewEvent',1)

WITH (viewName VARCHAR(50) '../../@viewName',

viewEvent_SubItemType VARCHAR(50) '@subItemType') ;

EXEC sys.sp_xml_removedocument @idoc ;

edit: housekeeping...remove document when done

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

tmitchelar Hall of Fame Points: 3391 More actions · Answer 2

Thank you so much! I modified your code a bit to get what I needed:

SELECT

X.x.value('../../@viewName','varchar(50)') AS result1,

X.x.value('@subItemType','varchar(50)') AS result2

FROM KSession a CROSS APPLY

[SData].nodes('/views/view/viewEvents/viewEvent') as X(x)

What would be the code to change the node to '/views/view' and then go to the grandchild from the parent?

Something like:

SELECT

X.x.value('@viewName','varchar(50)') AS result1,

X.x.value('@subItemType/../../','varchar(50)') AS result2

FROM KSession a CROSS APPLY

[SessionData].nodes('/views/view') as X(x)

???

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

I am a bit confused as to what you're now asking. My solution was based on using OPENXML...it lokos like somewhere along the line though you switched to using CROSS APPLY. If you would post a complete example including the definitions of KioskSession and SessionData I can try to help.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato