Select xml tags from nvarchar datatype column in SQL Server-CodePudding

My data sample is below. This data is saved in a Nvarchar datatype column of table named XMLDATA.

<interactions>
<interaction index="0" id="3-2-1-10-10" timestamp="2022-04-27T16:49:26" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction>
<interaction index="1" id="3-2-1-20-10" timestamp="2022-04-27T16:51:17" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction>
<interaction index="2" id="3-2-1-30-10" timestamp="2022-04-27T16:51:21" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction>
</interactions>

The tables looks like

ID  Interactions
46020960    <interactions><interaction index="0" id="3-2-1-10-10" timestamp="2022-04-27T16:49:26" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="1" id="3-2-1-20-10" timestamp="2022-04-27T16:51:17" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="2" id="3-2-1-30-10" timestamp="2022-04-27T16:51:21" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="3" id="3-2-1-40-10" timestamp="2022-04-27T16:51:25" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="4" id="3-2-1-50-10" timestamp="2022-04-27T16:51:29" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="5" id="3-2-1-60-10" timestamp="2022-04-27T16:51:32" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="6" id="3-2-1-70-10" timestamp="2022-04-27T16:51:35" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="7" id="3-2-1-80-10" timestamp="2022-04-27T16:51:39" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="8" id="3-2-1-90-10" timestamp="2022-04-27T16:51:42" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="9" id="3-2-1-100-10" timestamp="2022-04-27T16:51:45" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="10" id="3-2-1-110-10" timestamp="2022-04-27T16:51:49" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="11" id="3-2-1-120-10" timestamp="2022-04-27T16:51:53" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="12" id="3-2-1-130-10" timestamp="2022-04-27T16:51:56" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="13" id="3-2-1-140-10" timestamp="2022-04-27T16:51:59" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="14" id="3-2-1-150-10" timestamp="2022-04-27T16:52:03" weighting="NULL" type="numeric" latency="NULL" learner_response="8" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="15" id="3-2-1-160-10" timestamp="2022-04-27T16:52:06" weighting="NULL" type="numeric" latency="NULL" learner_response="5" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="16" id="3-2-1-170-10" timestamp="2022-04-27T16:52:10" weighting="NULL" type="numeric" latency="NULL" learner_response="8" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="17" id="3-2-1-180-10" timestamp="2022-04-27T16:52:14" weighting="NULL" type="numeric" latency="NULL" learner_response="8" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="18" id="3-2-1-190-10" timestamp="2022-04-27T16:52:17" weighting="NULL" type="numeric" latency="NULL" learner_response="9" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="19" id="3-2-1-200-10" timestamp="2022-04-27T16:52:21" weighting="NULL" type="numeric" latency="NULL" learner_response="9" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="20" id="3-2-1-210-10" timestamp="2022-04-27T16:52:23" weighting="NULL" type="numeric" latency="NULL" learner_response="9" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="21" id="3-2-1-220-10" timestamp="2022-04-27T16:52:26" weighting="NULL" type="numeric" latency="NULL" learner_response="9" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="22" id="3-2-1-230-10" timestamp="2022-04-27T16:52:29" weighting="NULL" type="numeric" latency="NULL" learner_response="8" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="23" id="3-2-1-240-10" timestamp="2022-04-27T16:52:33" weighting="NULL" type="numeric" latency="NULL" learner_response="8" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="24" id="3-3-1-20-10" timestamp="2022-04-27T16:52:50" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="25" id="3-3-1-40-10" timestamp="2022-04-27T16:52:56" weighting="NULL" type="numeric" latency="NULL" learner_response="5" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="26" id="3-3-1-60-10" timestamp="2022-04-27T16:53:00" weighting="NULL" type="numeric" latency="NULL" learner_response="9" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="27" id="3-3-1-80-10" timestamp="2022-04-27T16:53:06" weighting="NULL" type="numeric" latency="NULL" learner_response="6" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="28" id="3-3-1-100-10" timestamp="2022-04-27T16:53:12" weighting="NULL" type="numeric" latency="NULL" learner_response="9" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="29" id="3-3-1-120-10" timestamp="2022-04-27T16:53:39" weighting="NULL" type="numeric" latency="NULL" learner_response="9" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="30" id="3-3-1-140-10" timestamp="2022-04-27T16:53:44" weighting="NULL" type="numeric" latency="NULL" learner_response="7" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="31" id="3-3-1-160-10" timestamp="2022-04-27T16:53:51" weighting="NULL" type="numeric" latency="NULL" learner_response="8" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="32" id="3-3-1-180-10" timestamp="2022-04-27T16:53:55" weighting="NULL" type="numeric" latency="NULL" learner_response="7" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="33" id="3-3-1-200-10" timestamp="2022-04-27T16:54:01" weighting="NULL" type="numeric" latency="NULL" learner_response="3" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="34" id="3-3-1-220-10" timestamp="2022-04-27T16:54:07" weighting="NULL" type="numeric" latency="NULL" learner_response="3" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="35" id="3-3-1-240-10" timestamp="2022-04-27T16:54:12" weighting="NULL" type="numeric" latency="NULL" learner_response="7" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="36" id="3-3-1-260-10" timestamp="2022-04-27T16:54:17" weighting="NULL" type="numeric" latency="NULL" learner_response="7" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="37" id="3-3-1-280-10" timestamp="2022-04-27T16:54:22" weighting="NULL" type="numeric" latency="NULL" learner_response="9" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="38" id="3-3-1-300-10" timestamp="2022-04-27T16:54:26" weighting="NULL" type="numeric" latency="NULL" learner_response="8" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="39" id="3-3-1-320-10" timestamp="2022-04-27T16:54:33" weighting="NULL" type="numeric" latency="NULL" learner_response="8" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="40" id="3-3-1-340-10" timestamp="2022-04-27T16:54:37" weighting="NULL" type="numeric" latency="NULL" learner_response="9" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="41" id="3-3-1-360-10" timestamp="2022-04-27T16:54:44" weighting="NULL" type="numeric" latency="NULL" learner_response="8" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="42" id="3-3-1-380-10" timestamp="2022-04-27T16:54:49" weighting="NULL" type="numeric" latency="NULL" learner_response="9" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="43" id="3-3-1-400-10" timestamp="2022-04-27T16:55:04" weighting="NULL" type="numeric" latency="NULL" learner_response="8" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="44" id="3-3-1-420-10" timestamp="2022-04-27T16:55:08" weighting="NULL" type="numeric" latency="NULL" learner_response="8" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="45" id="3-3-1-460-10" timestamp="2022-04-27T16:55:15" weighting="NULL" type="numeric" latency="NULL" learner_response="7" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="46" id="3-3-1-480-10" timestamp="2022-04-27T16:55:21" weighting="NULL" type="numeric" latency="NULL" learner_response="8" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="47" id="3-3-1-440-10" timestamp="2022-04-27T16:56:58" weighting="NULL" type="numeric" latency="NULL" learner_response="6" result="correct" description="NULL"><objectives/><corr_resps/></interaction></interactions>
46111758    <interactions><interaction index="0" id="3-2-1-10-10" timestamp="2022-04-27T19:09:28" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="1" id="3-2-1-20-10" timestamp="2022-04-27T19:09:33" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="2" id="3-2-1-30-10" timestamp="2022-04-27T19:09:37" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="3" id="3-2-1-40-10" timestamp="2022-04-27T19:09:41" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="4" id="3-2-1-50-10" timestamp="2022-04-27T19:09:45" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="5" id="3-2-1-60-10" timestamp="2022-04-27T19:09:48" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="6" id="3-2-1-70-10" timestamp="2022-04-27T19:09:52" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="7" id="3-2-1-80-10" timestamp="2022-04-27T19:09:55" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="8" id="3-2-1-90-10" timestamp="2022-04-27T19:09:59" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="9" id="3-2-1-100-10" timestamp="2022-04-27T19:10:02" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="10" id="3-2-1-110-10" timestamp="2022-04-27T19:10:07" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="11" id="3-2-1-120-10" timestamp="2022-04-27T19:10:11" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="12" id="3-2-1-130-10" timestamp="2022-04-27T19:10:15" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="13" id="3-2-1-140-10" timestamp="2022-04-27T19:10:18" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="14" id="3-2-1-150-10" timestamp="2022-04-27T19:10:25" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="15" id="3-2-1-160-10" timestamp="2022-04-27T19:10:29" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="16" id="3-2-1-170-10" timestamp="2022-04-27T19:10:59" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="17" id="3-2-1-180-10" timestamp="2022-04-27T19:11:02" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="18" id="3-2-1-190-10" timestamp="2022-04-27T19:11:07" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="19" id="3-2-1-200-10" timestamp="2022-04-27T19:11:10" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="20" id="3-2-1-210-10" timestamp="2022-04-27T19:11:13" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="21" id="3-2-1-220-10" timestamp="2022-04-27T19:11:17" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="22" id="3-2-1-230-10" timestamp="2022-04-27T19:11:21" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="23" id="3-2-1-240-10" timestamp="2022-04-27T19:11:24" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="24" id="3-3-1-20-10" timestamp="2022-04-27T19:11:36" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="25" id="3-3-1-40-10" timestamp="2022-04-27T19:11:41" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="26" id="3-3-1-60-10" timestamp="2022-04-27T19:11:46" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="27" id="3-3-1-80-10" timestamp="2022-04-27T19:11:53" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="28" id="3-3-1-100-10" timestamp="2022-04-27T19:11:57" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="29" id="3-3-1-120-10" timestamp="2022-04-27T19:12:04" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="30" id="3-3-1-140-10" timestamp="2022-04-27T19:12:09" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="31" id="3-3-1-160-10" timestamp="2022-04-27T19:12:15" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="32" id="3-3-1-180-10" timestamp="2022-04-27T19:12:19" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="33" id="3-3-1-200-10" timestamp="2022-04-27T19:12:22" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="34" id="3-3-1-220-10" timestamp="2022-04-27T19:12:26" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="35" id="3-3-1-240-10" timestamp="2022-04-27T19:12:31" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="36" id="3-3-1-260-10" timestamp="2022-04-27T19:12:34" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="37" id="3-3-1-280-10" timestamp="2022-04-27T19:12:39" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="38" id="3-3-1-300-10" timestamp="2022-04-27T19:12:42" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="39" id="3-3-1-320-10" timestamp="2022-04-27T19:12:48" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="40" id="3-3-1-340-10" timestamp="2022-04-27T19:12:52" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="41" id="3-3-1-360-10" timestamp="2022-04-27T19:12:58" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="42" id="3-3-1-380-10" timestamp="2022-04-27T19:13:02" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="43" id="3-3-1-400-10" timestamp="2022-04-27T19:13:06" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="44" id="3-3-1-420-10" timestamp="2022-04-27T19:13:10" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="45" id="3-3-1-440-10" timestamp="2022-04-27T19:13:15" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="46" id="3-3-1-460-10" timestamp="2022-04-27T19:13:19" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction><interaction index="47" id="3-3-1-480-10" timestamp="2022-04-27T19:13:25" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction></interactions>

My requirement is to get the 'id' and 'learner_response' only in a select query.
I have tried below but its not working for me. Its only giving blanks.

select a.Id,
x.y.value('(/interactions/interaction/id/node())[2]', 'nvarchar(255)') as Q_id,
x.y.value('(/interactions/interaction/learner_response/node())[7]', 'nvarchar(255)') as Response
from (select  Convert(XML, Interactions) as XML, ID from xmldata) As A
Cross Apply xml.nodes('//Attributes/learner_response') As x(y)

I need the data like :

ID              Q_ID    Response
46020960    3-2-1-40-10 10
46111758    3-2-1-40-10 10
46096320    3-2-1-40-10 7
46028650    3-2-1-40-10 null

CodePudding user response：

You didn't specify which interaction you want for each ID, but this query will return them all, and you can filter after if necessary.

create table xmldata(id int, interactions nvarchar(max))
insert into xmldata(id, interactions) values (46020960,'
<interactions>
<interaction index="0" id="3-2-1-10-10" timestamp="2022-04-27T16:49:26" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction>
<interaction index="1" id="3-2-1-20-10" timestamp="2022-04-27T16:51:17" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction>
<interaction index="2" id="3-2-1-30-10" timestamp="2022-04-27T16:51:21" weighting="NULL" type="numeric" latency="NULL" learner_response="10" result="correct" description="NULL"><objectives/><corr_resps/></interaction>
</interactions>')

go

with x as (select id, cast(interactions as xml) doc from xmldata) 
select x.ID, 
       d.i.value('@index','int') response_index,
       d.i.value('@id','varchar(20)') q_id, 
       d.i.value('@learner_response','int') learner_response
from x
cross apply x.doc.nodes('/interactions/interaction') d(i)