We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

How to convert XML stored in SQL into a table

Medium Priority
269 Views
Last Modified: 2012-05-11
Greetings,

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>

Open in new window


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
Comment
Watch Question

Just using your document:


declare @doc xml
set @doc = 
'<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>'

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 @doc.nodes('//interaction/corr_resps/corr_resp') t(c)

Open in new window

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)

Open in new window

Er, that last line should read cross apply...
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
Amazingly fast response!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.