ChrisYencer
asked on
How to convert XML stored in SQL into a table
Greetings,
I'm trying to convert this XML that's stored in the database into a table that can be queryed.
When done, I'd like a table showing at the minimum:
Index, Id, Timestamp, Weighting, Type, Latency, Learner_Response, Result, and Time Â
I'm using SQL Server 2005. Â In case you're wondering, this is data stored in our Learning Management System (TotalLMS) and it's from SCORM 1.2 / 2004 Online Learning Activities.
Thanks!
Chris
I'm trying to convert this XML that's stored in the database into a table that can be queryed.
<interactions>
<interaction index="0" id="Interaction10215" timestamp="2011-04-18T12:40:54" weighting="3" type="choice" latency="PT0H0M13S" learner_response="A[,]C[,]E" result="correct" description="NULL">
<objectives />
<corr_resps>
<corr_resp index="0" pattern="A[,]C[,]E" />
</corr_resps>
</interaction>
<interaction index="1" id="Interaction10215" timestamp="2011-04-18T12:40:58" weighting="1" type="choice" latency="PT0H0M3S" learner_response="C" result="correct" description="NULL">
<objectives />
<corr_resps>
<corr_resp index="0" pattern="C" />
</corr_resps>
</interaction>
<interaction index="2" id="Interaction12805" timestamp="2011-04-18T12:41:08" weighting="1" type="choice" latency="PT0H0M8S" learner_response="A" result="correct" description="NULL">
<objectives />
<corr_resps>
<corr_resp index="0" pattern="A" />
</corr_resps>
</interaction>
<interaction index="3" id="Interaction12911" timestamp="2011-04-18T12:41:17" weighting="1" type="choice" latency="PT0H0M5S" learner_response="B" result="correct" description="NULL">
<objectives />
<corr_resps>
<corr_resp index="0" pattern="B" />
</corr_resps>
</interaction>
<interaction index="4" id="Interaction10609" timestamp="2011-04-18T12:41:26" weighting="1" type="choice" latency="PT0H0M7S" learner_response="0" result="correct" description="NULL">
<objectives />
<corr_resps>
<corr_resp index="0" pattern="0" />
</corr_resps>
</interaction>
<interaction index="5" id="Interaction10660" timestamp="2011-04-18T12:41:30" weighting="1" type="choice" latency="PT0H0M2S" learner_response="0" result="correct" description="NULL">
<objectives />
<corr_resps>
<corr_resp index="0" pattern="0" />
</corr_resps>
</interaction>
<interaction index="6" id="Interaction10693" timestamp="2011-04-18T12:41:37" weighting="1" type="true-false" latency="PT0H0M3S" learner_response="false" result="correct" description="NULL">
<objectives />
<corr_resps>
<corr_resp index="0" pattern="false" />
</corr_resps>
</interaction>
<interaction index="7" id="Interaction10743" timestamp="2011-04-18T12:41:47" weighting="1" type="choice" latency="PT0H0M5S" learner_response="B" result="correct" description="NULL">
<objectives />
<corr_resps>
<corr_resp index="0" pattern="B" />
</corr_resps>
</interaction>
<interaction index="8" id="Interaction10864" timestamp="2011-04-18T12:42:09" weighting="1" type="choice" latency="PT0H0M13S" learner_response="B" result="correct" description="NULL">
<objectives />
<corr_resps>
<corr_resp index="0" pattern="B" />
</corr_resps>
</interaction>
<interaction index="9" id="Interaction11602" timestamp="2011-04-18T12:42:22" weighting="1" type="choice" latency="PT0H0M10S" learner_response="0" result="correct" description="NULL">
<objectives />
<corr_resps>
<corr_resp index="0" pattern="0" />
</corr_resps>
</interaction>
<interaction index="10" id="Interaction11651" timestamp="2011-04-18T12:42:29" weighting="1" type="choice" latency="PT0H0M2S" learner_response="0" result="correct" description="NULL">
<objectives />
<corr_resps>
<corr_resp index="0" pattern="0" />
</corr_resps>
</interaction>
<interaction index="11" id="Interaction11686" timestamp="2011-04-18T12:42:41" weighting="1" type="choice" latency="PT0H0M9S" learner_response="0" result="correct" description="NULL">
<objectives />
<corr_resps>
<corr_resp index="0" pattern="0" />
</corr_resps>
</interaction>
<interaction index="12" id="Interaction11706" timestamp="2011-04-18T12:42:52" weighting="1" type="choice" latency="PT0H0M8S" learner_response="0" result="correct" description="NULL">
<objectives />
<corr_resps>
<corr_resp index="0" pattern="0" />
</corr_resps>
</interaction>
<interaction index="13" id="Interaction11723" timestamp="2011-04-18T12:42:57" weighting="1" type="choice" latency="PT0H0M2S" learner_response="0" result="correct" description="NULL">
<objectives />
<corr_resps>
<corr_resp index="0" pattern="0" />
</corr_resps>
</interaction>
<interaction index="14" id="Interaction11745" timestamp="2011-04-18T12:43:02" weighting="1" type="choice" latency="PT0H0M1S" learner_response="0" result="correct" description="NULL">
<objectives />
<corr_resps>
<corr_resp index="0" pattern="0" />
</corr_resps>
</interaction>
<interaction index="15" id="Interaction11767" timestamp="2011-04-18T12:43:08" weighting="1" type="choice" latency="PT0H0M4S" learner_response="0" result="correct" description="NULL">
<objectives />
<corr_resps>
<corr_resp index="0" pattern="0" />
</corr_resps>
</interaction>
<interaction index="16" id="Interaction11804" timestamp="2011-04-18T12:43:24" weighting="1" type="choice" latency="PT0H0M13S" learner_response="D" result="correct" description="NULL">
<objectives />
<corr_resps>
<corr_resp index="0" pattern="D" />
</corr_resps>
</interaction>
<interaction index="17" id="Interaction12063" timestamp="2011-04-18T12:43:29" weighting="1" type="choice" latency="PT0H0M3S" learner_response="C" result="correct" description="NULL">
<objectives />
<corr_resps>
<corr_resp index="0" pattern="C" />
</corr_resps>
</interaction>
<interaction index="18" id="Interaction11954" timestamp="2011-04-18T12:43:35" weighting="1" type="choice" latency="PT0H0M4S" learner_response="B" result="correct" description="NULL">
<objectives />
<corr_resps>
<corr_resp index="0" pattern="B" />
</corr_resps>
</interaction>
<interaction index="19" id="Interaction11845" timestamp="2011-04-18T12:43:44" weighting="1" type="choice" latency="PT0H0M5S" learner_response="A" result="correct" description="NULL">
<objectives />
<corr_resps>
<corr_resp index="0" pattern="A" />
</corr_resps>
</interaction>
<interaction index="20" id="Interaction10921" timestamp="2011-04-18T12:43:50" weighting="1" type="choice" latency="PT0H0M4S" learner_response="B" result="correct" description="NULL">
<objectives />
<corr_resps>
<corr_resp index="0" pattern="B" />
</corr_resps>
</interaction>
<interaction index="21" id="Interaction10403" timestamp="2011-04-18T12:43:56" weighting="1" type="true-false" latency="PT0H0M4S" learner_response="false" result="correct" description="NULL">
<objectives />
<corr_resps>
<corr_resp index="0" pattern="false" />
</corr_resps>
</interaction>
</interactions>When done, I'd like a table showing at the minimum:
Index, Id, Timestamp, Weighting, Type, Latency, Learner_Response, Result, and Time Â
I'm using SQL Server 2005. Â In case you're wondering, this is data stored in our Learning Management System (TotalLMS) and it's from SCORM 1.2 / 2004 Online Learning Activities.
Thanks!
Chris
And this should work for the table in your database, replace table_name and column_name with yours.
select [description] = t.c.value('../../@description', 'varchar(100)')
, id = t.c.value('../../@id', 'varchar(100)')
, [index] = t.c.value('@index', 'varchar(100)')
, latency = t.c.value('../../@latency', 'varchar(100)')
, learner_response = t.c.value('../../@learner_response', 'varchar(100)')
, pattern = t.c.value('@pattern', 'varchar(100)')
, result = t.c.value('../../@result', 'varchar(100)')
, [timestamp] = t.c.value('../../@timestamp', 'datetime')
, [type] = t.c.value('../../@type', 'varchar(100)')
, weighting = t.c.value('../../@weighting', 'varchar(100)')
from table_name
crpss apply column_name.nodes('//interaction/corr_resps/corr_resp') t(c)
Er, that last line should read cross apply...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Amazingly fast response!
Open in new window