Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Xquery Problem Expand / Collapse
Author
Message
Posted Thursday, April 4, 2013 7:04 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: 2 days ago @ 5:22 AM
Points: 711, Visits: 2,209
Hi,

I have a field that contains some XML. The root level is ENTITIES there might be many ENTITY nodes. Here is an example query.

DECLARE @table TABLE
(
RiskXML XML
)

INSERT INTO @table
(
[RiskXML]
)
VALUES
(
'<ENTITIES>
<ENTITY Id="10863" Type="Properties">
<GROUP Type="No_Group">
<RISKGROUP Id="11251" Type="No_Group">
<RISKKEYITEM Name="donotshow_EntityGroupRepeatIndex" Value="1" Type="Integer" />
</RISKGROUP>
</GROUP>
</ENTITY>
<ENTITY Id="10864" Type="Properties">
<GROUP Type="No_Group">
<RISKGROUP Id="11252" Type="No_Group">
<RISKKEYITEM Name="donotshow_EntityGroupRepeatIndex" Value="2" Type="Integer" />
</RISKGROUP>
</GROUP>
</ENTITY>
<ENTITY Id="10865" Type="Properties">
<GROUP Type="No_Group">
<RISKGROUP Id="11253" Type="No_Group">
<RISKKEYITEM Name="donotshow_EntityGroupRepeatIndex" Value="3" Type="Integer" />
</RISKGROUP>
</GROUP>
</ENTITY>
</ENTITIES>'
)


SELECT
ROW_NUMBER() OVER (ORDER BY e.value('(./@Id)','int')) AS EntityNumber,
e.value('(./@Id)','int') AS EntityId,
e.query('(//ENTITY/GROUP/RISKGROUP/RISKKEYITEM[@Name="donotshow_EntityGroupRepeatIndex"])') AS EntityRKI,
t.RiskXML
FROM
@table t
CROSS APPLY t.RiskXML.nodes('//ENTITIES/ENTITY') Entities(e)

So... looking at that result set in the EntityRKI column there are 3 RISKKEYITEM nodes (one for each entity). What i want is the RISKKEYITEM node for the EntityId, not all 3 RISKKEYITEM nodes for each EntityId. Does anyone know how i can do that?

I appreciate my description may not be great so feel free to ask any questions and i'll try to clarify any confusion...

Thanks,


Simon




MCSE: Data Platform
MCSE: Business Intelligence
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne
Post #1438775
Posted Thursday, April 4, 2013 7:16 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 2, 2014 11:08 AM
Points: 16, Visits: 142
You need to 'anchor' the query to the entity as well:

SELECT 
ROW_NUMBER() OVER (ORDER BY e.value('(./@Id)','int')) AS EntityNumber,
e.value('(./@Id)','int') AS EntityId,
e.query('(./GROUP/RISKGROUP/RISKKEYITEM[@Name="donotshow_EntityGroupRepeatIndex"])') AS EntityRKI,
t.RiskXML
FROM
@table t
CROSS APPLY t.RiskXML.nodes('//ENTITIES/ENTITY') Entities(e)

That returns what I think you're looking for.


blog | napalmgram@Twitter


Training cats makes SQL Server look easy
Post #1438785
Posted Thursday, April 4, 2013 7:19 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: 2 days ago @ 5:22 AM
Points: 711, Visits: 2,209
That's exactly what I wanted. Nice one.



MCSE: Data Platform
MCSE: Business Intelligence
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne
Post #1438788
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse