How to insert data into xml column

i want insert :

  <column>
    <column_name>feedbackloop</column_name>
    <column_type>int</column_type>
  </column>

at end of existing xml.

perhaps easier if proved schema , 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 target namespace 'grs' while xml trying insert has namespace 'grscorp'. sample above not fit schema. if change xml have namespace 'grs' possible insert xml data. , following update statement works me insert 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]

');

 

 

SQL Server  >  SQL Server XML