April 4, 2013 at 7:04 am
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
April 4, 2013 at 7:16 am
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.
Training cats makes SQL Server look easy
April 4, 2013 at 7:19 am
That's exactly what I wanted. Nice one.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply