Showing posts with label datatype. Show all posts
Showing posts with label datatype. Show all posts

Friday, March 23, 2012

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

Monday, March 12, 2012

Retrieving data types is slow

Hello,

i'm using SMO to retrieve information from various databases. It works
well except for one thing. When I call the Column.DataType property to
get the SQL type of a column it takes a very long tine. I have a few
databases, each with some tables. There are about 75 columns I think.
If I just browse all columns with SMO and write each name if it not a
system one, it takes about 15 seconds (pretty much anyway). If I also retrieve the data type
it takes about one minute. Any ideas why? The server is a local one.

Thanksstarted reading Michiel's articles. The issue will be fixed shortly I think. Thanks anyway.

Wednesday, March 7, 2012

retrieve the GUID for inserted record

I am using this code to insert a record in my table where i have assigned a guid datatype field to generate an automatic guid for each record. but now i need to retrieve the guid to use it to send a confirmation email to the user.

SqlConnection sql_connection = new SqlConnection("Server=xxx.xxx.xx.xxx;uid=xxxxxxxx;password=xxxxxxx;database=xxxxxxx;");
SqlCommand sql_command = new SqlCommand("INSERT INTO members (member_sex, member_cpr, member_nationality, member_block, member_gov, member_daaera, member_email, member_mobile, member_created_ip) Values (@.member_sex, @.member_cpr, @.member_nationality, @.member_block, @.member_gov, @.member_daaera, @.member_email, @.member_mobile, @.member_created_ip)", sql_connection);

sql_command.Parameters.Add(new SqlParameter("@.member_sex", Session["member_sex"].ToString()));
sql_command.Parameters.Add(new SqlParameter("@.member_cpr", Session["member_cpr"]));
sql_command.Parameters.Add(new SqlParameter("@.member_nationality", Session["member_nationality"].ToString()));
sql_command.Parameters.Add(new SqlParameter("@.member_block", Session["member_block"].ToString()));
sql_command.Parameters.Add(new SqlParameter("@.member_gov", "GOV"));
sql_command.Parameters.Add(new SqlParameter("@.member_daaera", 6));
sql_command.Parameters.Add(new SqlParameter("@.member_email", Session["member_email"].ToString().ToLower()));
sql_command.Parameters.Add(new SqlParameter("@.member_mobile", Session["member_mobile"].ToString()));
sql_command.Parameters.Add(new SqlParameter("@.member_created_ip", Request.UserHostAddress.ToString()));

sql_connection.Open();
sql_command.ExecuteNonQuery();
sql_connection.Close();

Create the GUID yourself, and pass it into the insert.