Retrieving XML Datatype to a Record Set

I'm sure this is a simple thing for you guru's out there. Working in SQL 2000

I have a datatype in a colunm that is I assume is XML. I need a way to parse this so that the output is in a normal table output. I then want to join the results with other record from the same table. The nodes are all the same.

Ultimately, the data will be analyzed in reporting services.

Here is a sample of the xml data saved in a field. Thanks for your time.

<interactions><interaction index="0" id="I0001-1" timestamp="2007-07-10T14:14:00" weighting="1" type="true-false" latency="PT3S" learner_response="true" result="correct" description="Tire center employees should know where to access."><objectives><objective index="0" id="I0001-1"/></objectives><corr_resps><corr_resp index="0" pattern="true"/></corr_resps></interaction><interaction index="1" id="I0002-1" timestamp="2007-07-10T14:14:02" weighting="1" type="true-false" latency="PT3S" learner_response="false" result="incorrect" description=" does not sell used tires or any tire that has previously been mounted on a rim and driven on."><objectives><objective index="0" id="I0002-1"/></objectives><corr_resps><corr_resp index="0" pattern="true"/></corr_resps></interaction><interaction index="2" id="I0003-1" timestamp="2007-07-10T14:14:05" weighting="1" type="choice" latency="PT3S" learner_response="Taken" result="incorrect" description="Tire center supplies should not be:"><objectives><objective index="0" id="I0003-1"/></objectives><corr_resps><corr_resp index="0" pattern="All_apply"/></corr_resps></interaction><interaction index="3" id="I0004-1" timestamp="2007-07-10T14:14:08" weighting="1" type="true-false" latency="PT3S" learner_response="false" result="correct" description="Employees have the option to wear a supplied uniform.&#xA;"><objectives><objective index="0" id="I0004-1"/></objectives><corr_resps><corr_resp index="0" pattern="false"/></corr_resps></interaction><interaction index="4" id="I0005-1" timestamp="2007-07-10T14:14:11" weighting="1" type="true-false" latency="PT2S" learner_response="false" result="incorrect" description="Uniform shirts should be tucked in and belts worn all times.&#xA;"><objectives><objective index="0" id="I0005-1"/></objectives><corr_resps><corr_resp index="0" pattern="true"/></corr_resps></interaction></interactions>

Does SQL 2000 have an XML datatype? I don't think so.

I think what it does support is the OPENXML row set provider so for instance to read out the attributes of the interaction elements you could use the following:

Code Snippet

DECLARE @.x nvarchar(2000);

SET @.x = '<interactions><interaction index="0" id="I0001-1" timestamp="2007-07-10T14:14:00" weighting="1" type="true-false" latency="PT3S" learner_response="true" result="correct" description="Tire center employees should know where to access."><objectives><objective index="0" id="I0001-1"/></objectives><corr_resps><corr_resp index="0" pattern="true"/></corr_resps></interaction><interaction index="1" id="I0002-1" timestamp="2007-07-10T14:14:02" weighting="1" type="true-false" latency="PT3S" learner_response="false" result="incorrect" description=" does not sell used tires or any tire that has previously been mounted on a rim and driven on."><objectives><objective index="0" id="I0002-1"/></objectives><corr_resps><corr_resp index="0" pattern="true"/></corr_resps></interaction><interaction index="2" id="I0003-1" timestamp="2007-07-10T14:14:05" weighting="1" type="choice" latency="PT3S" learner_response="Taken" result="incorrect" description="Tire center supplies should not be:"><objectives><objective index="0" id="I0003-1"/></objectives><corr_resps><corr_resp index="0" pattern="All_apply"/></corr_resps></interaction><interaction index="3" id="I0004-1" timestamp="2007-07-10T14:14:08" weighting="1" type="true-false" latency="PT3S" learner_response="false" result="correct" description="Employees have the option to wear a supplied uniform.&#xA;"><objectives><objective index="0" id="I0004-1"/></objectives><corr_resps><corr_resp index="0" pattern="false"/></corr_resps></interaction><interaction index="4" id="I0005-1" timestamp="2007-07-10T14:14:11" weighting="1" type="true-false" latency="PT2S" learner_response="false" result="incorrect" description="Uniform shirts should be tucked in and belts worn all times.&#xA;"><objectives><objective index="0" id="I0005-1"/></objectives><corr_resps><corr_resp index="0" pattern="true"/></corr_resps></interaction></interactions>';

DECLARE @.iDoc int;

EXEC sp_xml_preparedocument @.iDoc OUTPUT, @.x;

SELECT *

FROM OPENXML (@.iDoc, '/interactions/interaction', 1)

WITH (

[index] int,

[id] nvarchar(10),

[timestamp] datetime,

[weighting] int,

[type] nvarchar(10),

[latency] nvarchar(5),

[learner_response] nvarchar(5),

[result] nvarchar(10),

[description] nvarchar(30),

);

EXEC sp_xml_removedocument @.iDoc;

|||Thanks for the reply. I've looked at OPENXML, but a bit confused by it when the data alread resides in a field. The column name is 'CntItmPrgs_Interactions'. Do I set @.x = the column name? i.e. Set @.x = CntItmPrgs.CntItmPrgs_Interactions|||

Yes, I think you need to use

SET @.x = (SELECT columnname FROM tablename WHERE somecondition)

|||

Okay, this is what I have:

DECLARE @.x nvarchar(2000);
SET @.x =(Select CntItmPrgs_Interactions from CntItmPrgs Where CntItmPrgs_PK = 406560)
DECLARE @.iDoc int;
EXEC sp_xml_preparedocument @.iDoc OUTPUT, @.x;
SELECT *
FROM OPENXML (@.iDoc, '/interactions/interaction', 1)
WITH (
[index] int,
[id] nvarchar(10),
[timestamp] datetime,
[weighting] int,
[type] nvarchar(10),
[latency] nvarchar(5),
[learner_response] nvarchar(5),
[result] nvarchar(10),
[description] nvarchar(30))
EXEC sp_xml_removedocument @.iDoc;

But, I'm getting this error:

Server: Msg 279, Level 16, State 3, Line 2
The text, ntext, and image data types are invalid in this subquery or aggregate expression.

Any ideas?

|||I don't have SQL Server 2000 and don't have enough experience with it to answer without testing. Maybe someone else can help if you provide details on the type of the CntItmPrgs_Interactions column.sql