how to insert into a table using a select from xml

Good Day,

I am passing some XML into a stored procedure:

<answers>
<answer id="60" text="" />
<answer id="65" text="A moderate form of learning disability" />
<answer id="68" text="We will keep ASD checked" />
<answer id="70" text="" />
</answers>

Along with a memberid and questionid.

I was wondering how I can get this into a table

CREATE TABLE [dbo].[Answers]([PrimaryKeyID] [int]NOT NULL,[MemberID] [int]NOT NULL,[QuestionID] [int]NOT NULL,[AnswerID] [int]NOT NULL,[FreText] [varchar](255) COLLATE Latin1_General_CI_ASNULL)ON [PRIMARY]

What I would also like to do is if the text attribute is empty then put a NULL in the FreText field.

I think I am looking for

 
Insert into MyTable (Select @.MemID, @.QuesID,'somexpathforanswer','somexpathfortext'-- if empty then NULLFrom @.MyXML )
  

Any ideas - places to to look - thoughts Aprreciated

Kal

you can try to use OPENXML see example from T_SQL help below

DECLARE @.idoc intDECLARE @.doc varchar(1000)SET @.doc ='<ROOT><Customer CustomerID="VINET" ContactName="Paul Henriot"> <Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00"> <OrderDetail OrderID="10248" ProductID="11" Quantity="12"/> <OrderDetail OrderID="10248" ProductID="42" Quantity="10"/> </Order></Customer><Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"> <Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00"> <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/> </Order></Customer></ROOT>'--Create an internal representation of the XML document.EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc-- Execute a SELECT statement that uses the OPENXML rowset provider.SELECT *FROM OPENXML (@.idoc, '/ROOT/Customer',1) WITH (CustomerID varchar(10), ContactName varchar(20))

How to Insert date in sql server database

How to insert date to the sql server database.

I am getting input from the HTML form and store it to database using
ASP.

how to store date field, what datatype needed and what conversion
needed.

Thanx & Regards,
SSGSSG (ssg14j@.gmail.com) writes:
> How to insert date to the sql server database.
> I am getting input from the HTML form and store it to database using
> ASP.
> how to store date field, what datatype needed and what conversion
> needed.

The data type to use datetime or smalldatetime. These always include
the time portion, but set it to midnight for dates only.

Conversion should occur in the client, by using parameterised statements.

Here is an example that I have canned of a parameterised statement in ADO
(it's VB6 and not ASP, but I believe that they are not too different):

Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cnn

cmd.CommandType = adCmdText
cmd.CommandText = " SELECT OrderID, OrderDate, CustomerID, ShipName " & _
" FROM dbo.Orders WHERE 1 = 1 "
If custid <> "" Then
cmd.CommandText = cmd.CommandText & " AND CustomerID LIKE ? "
cmd.Parameters.Append
cmd.CreateParameter("@.custid", adWChar, adParamInput, 5, custid)
End If

If shipname <> "" Then
cmd.CommandText = cmd.CommandText & " AND ShipName LIKE ? "
cmd.Parameters.Append cmd.CreateParameter("@.shipname", _
adVarWChar, adParamInput, 40, shipname)
End If

Set rs = cmd.Execute

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

How to insert date in database table in sql server

I have one form with fields name,date of birth , age.
I would like to get the input from user and store it to sql database
table.
After I stored , while viewing the table records, the dateof birth
field is 01.01.1900, how to store the exact date in database.
what datatype i have to store in database table.
Thanx & Regards,
SSGSSG wrote:
> I have one form with fields name,date of birth , age.
> I would like to get the input from user and store it to sql database
> table.
> After I stored , while viewing the table records, the dateof birth
> field is 01.01.1900, how to store the exact date in database.
> what datatype i have to store in database table.
> Thanx & Regards,
> SSG
Use datetime datatype and you can get plenty of information regarding
it in BOL.
Regards
Amish Shah

How to insert date in database table in sql server

I have one form with fields name,date of birth , age.
I would like to get the input from user and store it to sql database
table.
After I stored , while viewing the table records, the dateof birth
field is 01.01.1900, how to store the exact date in database.
what datatype i have to store in database table.
Thanx & Regards,
SSGSSG wrote:

> I have one form with fields name,date of birth , age.
> I would like to get the input from user and store it to sql database
> table.
> After I stored , while viewing the table records, the dateof birth
> field is 01.01.1900, how to store the exact date in database.
> what datatype i have to store in database table.
> Thanx & Regards,
> SSG
Use datetime datatype and you can get plenty of information regarding
it in BOL.
Regards
Amish Shah