How to insert data into xml column

I want to insert :

<column>
<column_name>feedbackloop</column_name>
<column_type>INT</column_type>
</column>

at the end of the existing xml.

Perhaps it will be easier if i proved the schema and existing xml:

Code Snippet

create xml schema collection email_column_xml_schema as
'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:t="grs" targetNamespace="grs" elementFormDefault="qualified">
<xsd:element name="object">
<xsd:complexType>
<xsd:complexContent>
<xsd:restriction base="xsd:anyType">
<xsd:sequence>
<xsd:element name="type" type="xsd:string"/>
<xsd:element name="name" type="xsd:string"/>
<xsd:element name="remote" type="xsd:integer"/>
<xsd:element name="provider" type="xsd:string" />
<xsd:element name="connectionstring" type="xsd:string" />
<xsd:element name="database_name" type="xsd:string" />
<xsd:element name="send_using" type="xsd:string"/>
<xsd:element name="ftp_server" type="xsd:string" />
<xsd:element name="ftp_user" type="xsd:string" />
<xsd:element name="ftp_password" type="xsd:string" />
<xsd:element name="ftp_port_number" type="xsd:decimal" />
<xsd:element name="message" type="xsd:string" />
<xsd:element name="subject" type="xsd:string" />
<xsd:element name="recipients" type="xsd:string" />
<xsd:element name="as_attachment" type="xsd:integer" />
<xsd:element name="file_name" type="xsd:string" />
<xsd:element name="delimeter" type="xsd:string" />
<xsd:element name="send_interval_days" type="xsd:integer"/>
<xsd:element name="AlwaysSend" type="xsd:integer"/>
<xsd:element name="enabled" type="xsd:integer"/>
<xsd:element name="column" maxOccurs="unbounded">
<xsd:complexType>
<xsd:complexContent>
<xsd:restriction base="xsd:anyType">
<xsd:sequence>
<xsd:element name="column_name" type="xsd:string" />
<xsd:element name="column_type" type="xsd:string" />
</xsd:sequence>
</xsd:restriction>
</xsd:complexContent>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:restriction>
</xsd:complexContent>
</xsd:complexType>
</xsd:element>
</xsd:schema>'
go

CREATE TABLE email_column (email_id INT PRIMARY KEY IDENTITY(1,1), xColumn XML(email_column_xml_schema))
go

insert email_column values( '<object xmlns="grs">
<type>VIEW</type>
<name>snapshot</name>
<remote>1</remote>
<provider>SQLNCLI</provider>
<connectionstring></connectionstring>
<database_name></database_name>
<send_using>email</send_using>
<ftp_server></ftp_server>
<ftp_user></ftp_user>
<ftp_password></ftp_password>
<ftp_port_number>0</ftp_port_number>
<message>Snapshot</message>
<subject>Snapshot</subject>
<recipients></recipients>
<as_attachment>0</as_attachment>
<file_name></file_name>
<delimeter></delimeter>
<send_interval_days>1</send_interval_days>
<AlwaysSend>0</AlwaysSend>
<enabled>1</enabled>
<column>
<column_name>day</column_name>
<column_type>DATETIME</column_type>
</column>
<column>
<column_name>domainname</column_name>
<column_type>VARCHAR(50)</column_type>
</column>
<column>
<column_name>ratio</column_name>
<column_type>DECIMAL(10,2)</column_type>
</column>
</object>' )
go

Your schema has a target namespace 'grs' while the XML you are trying to insert has the namespace 'grscorp'. So that sample above does not fit the schema. If I change the XML to have namespace 'grs' then it is possible to insert the XML data. And then the following UPDATE statement works for me to insert the column element:

Code Snippet

UPDATE email_column

SET xColumn.modify('

declare default element namespace "grs";

insert

<column>

<column_name>feedbackloop</column_name>

<column_type>INT</column_type>

</column>

into (/object)[1]

');

||| works perfectly, thanks.