CODEWITHSUNDEEP
asp.netsql-serverxml
Patrick Keane
Patrick Keane

Reputation: 663

INSERT data into a column of XML datatype

I have a SQL Server database with a column of type XML. I've been looking about for ways to populate it using an INSERT query / stored procedure from asp.net.

So far all I can find is ways to get (SELECT) the XML data and execute an SqlDataReader / XMLReader (such as this example)

How can I go about populating the column from ASP.NET? The xml will look something like the following:

<remarks>
    <remark>
        <author>...</author>
        <date>...</date>
        <content>...</content>
    </remark>
    <remark>
        <author>...</author>
        <date>...</date>
        <content>...</content>
    </remark>
</remarks>

The 'content' will be entered by a user in a textbox, and the other columns will be populated using DateTime.Now() and User.Identity.Name.

I am looking to be able to:

  1. Enter a first remark into an empty XML column.
  2. Enter additional remarks at a later time.

Can this be done? I can't manage to find any information on this.

Upvotes: 2

Views: 7341

Answers (1)

jbl
jbl

Reputation: 15413

This should do the trick for INSERT 

INSERT INTO YourTable(idCol,xmlCol)
values(1,'<?xml version="1.0" encoding="utf-8"?>
<remarks>
    <remark>
        <author>...</author>
        <date>...</date>
        <content>...</content>
    </remark>
    <remark>
        <author>...</author>
        <date>...</date>
        <content>...</content>
    </remark>
</remarks>')

For UPDATE, I won't give a better answer than https://stackoverflow.com/a/1440688/1236044

Upvotes: 3

Related Questions