Sql-server – INSERT/SELECT xml column from one table to another

sql servert-sqlxml

First off I apologize for asking a question I'm sure is well answered, however I cannot quite understand what I'm reading.
I have an existing table with an xml column. I have a second table with the exact same structure and I want to insert all the data from one to the other

If I INSERT INTO <the new table> SELECT * FROM <the old table>, I get

Implicit conversion between XML types constrained by different XML schema collections is not allowed. Use the CONVERT function to run this query.

I've tried converting to nvarchar(max) & xml, both yield:

XML Validation: Declaration not found for element 'Table'. Location: /*:Table[1]

I really don't understand, I just want to blindly copy all data.
I shouldn't need to declare anything. If I look at the column its declared as (XML(dbo.ColumnNameSchemaCollection), null)
If need to provide more let me know, thanks for your help!

Further Edits

--Genereate XML schema from table
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <xsd:element name="Table">
    <xsd:complexType>
  <xsd:complexContent>
    <xsd:restriction base="xsd:anyType">
      <xsd:choice minOccurs="0" maxOccurs="unbounded">
        <xsd:element name="Rows">
          <xsd:complexType>
            <xsd:complexContent>
              <xsd:restriction base="xsd:anyType">
                <xsd:sequence>
                  <xsd:element name="Row" minOccurs="0" maxOccurs="unbounded">
                    <xsd:complexType>
                      <xsd:complexContent>
                        <xsd:restriction base="xsd:anyType">
                          <xsd:sequence>
                            <xsd:element name="Field" minOccurs="0" maxOccurs="unbounded">
                              <xsd:complexType>
                                <xsd:complexContent>
                                  <xsd:restriction base="xsd:anyType">
                                    <xsd:sequence>
                                      <xsd:element name="Name" type="xsd:string" minOccurs="0" />
                                      <xsd:element name="AnotherColumn" type="xsd:string" minOccurs="0" />
                                      <xsd:element name="Value" type="xsd:string" minOccurs="0" />
                                    </xsd:sequence>
                                  </xsd:restriction>
                                </xsd:complexContent>
                              </xsd:complexType>
                            </xsd:element>
                          </xsd:sequence>
                        </xsd:restriction>
                      </xsd:complexContent>
                    </xsd:complexType>
                  </xsd:element>
                </xsd:sequence>
              </xsd:restriction>
            </xsd:complexContent>
          </xsd:complexType>
        </xsd:element>
      </xsd:choice>
    </xsd:restriction>
  </xsd:complexContent>
</xsd:complexType>

Attempting to insert value like so

<Table xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Rows>
<Row>
  <Field>
    <Name>SomeField</Name>
    <AnotherColumn>2345</AnotherColumn>
    <Value>Some Text</Value>
  </Field>
  <Field>
    <Name>SomeField</Name>
    <AnotherColumn>3456</AnotherColumn>
    <Value>Some Text</Value>
  </Field>
  <Field>
    <Name>SomeField</Name>
    <AnotherColumn>4567</AnotherColumn>
    <Value>Some Text </Value>
  </Field>
</Row>

Also just to add, If the data already exists in source table, it should blindly migrate to the copy table. I have tried making the copy table an nvarchar(max), casting the select on the source table as nvarchar(max) and inserting however I can't cast it back to xml, it throws the same error

Best Answer

That document conforms to that schema. And I was able to insert the XML into a cloned db using CONVERT() as suggested by the initial error:

Can you edit your question or the below snippet to create a repro?

use master
drop database a
drop database a_clone
go
create database a

go
use a


declare @schema xml = 
'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <xsd:element name="Table">
    <xsd:complexType>
      <xsd:complexContent>
        <xsd:restriction base="xsd:anyType">
          <xsd:choice minOccurs="0" maxOccurs="unbounded">
            <xsd:element name="Rows">
              <xsd:complexType>
                <xsd:complexContent>
                  <xsd:restriction base="xsd:anyType">
                    <xsd:sequence>
                      <xsd:element name="Row" minOccurs="0" maxOccurs="unbounded">
                        <xsd:complexType>
                          <xsd:complexContent>
                            <xsd:restriction base="xsd:anyType">
                              <xsd:sequence>
                                <xsd:element name="Field" minOccurs="0" maxOccurs="unbounded">
                                  <xsd:complexType>
                                    <xsd:complexContent>
                                      <xsd:restriction base="xsd:anyType">
                                        <xsd:sequence>
                                          <xsd:element name="Name" type="xsd:string" minOccurs="0" />
                                          <xsd:element name="AnotherColumn" type="xsd:string" minOccurs="0" />
                                          <xsd:element name="Value" type="xsd:string" minOccurs="0" />
                                        </xsd:sequence>
                                      </xsd:restriction>
                                    </xsd:complexContent>
                                  </xsd:complexType>
                                </xsd:element>
                              </xsd:sequence>
                            </xsd:restriction>
                          </xsd:complexContent>
                        </xsd:complexType>
                      </xsd:element>
                    </xsd:sequence>
                  </xsd:restriction>
                </xsd:complexContent>
              </xsd:complexType>
            </xsd:element>
          </xsd:choice>
        </xsd:restriction>
      </xsd:complexContent>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>'



CREATE XML SCHEMA COLLECTION ColumnNameSchemaCollection AS @schema
go
create table t(id int, doc xml(content dbo.ColumnNameSchemaCollection))

go

declare @doc xml = 
'<Table xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Rows>
    <Row>
      <Field>
        <Name>SomeField</Name>
        <AnotherColumn>2345</AnotherColumn>
        <Value>Some Text</Value>
      </Field>
      <Field>
        <Name>SomeField</Name>
        <AnotherColumn>3456</AnotherColumn>
        <Value>Some Text</Value>
      </Field>
      <Field>
        <Name>SomeField</Name>
        <AnotherColumn>4567</AnotherColumn>
        <Value>Some Text </Value>
      </Field>
    </Row>
  </Rows>
</Table>'

insert into t(id,doc) values (1,@doc)

go

DBCC CLONEDATABASE (a, a_clone) WITH VERIFY_CLONEDB;    
GO 
alter database a_clone set read_write
go

insert into a_clone.dbo.t(id,doc) select id,doc from t
--Msg 527, Level 16, State 2, Line 96
--Implicit conversion between XML types constrained by different XML schema collections is not allowed. Use the CONVERT function to run this query.

insert into a_clone.dbo.t(id,doc) select id,convert(xml, doc) from t --works
Related Question