1

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

  • 2
    The second error indicates that the XML does not conform to the XML Schema constraint on the target table. Can you post a sample XML document and the XML Schema required by the target table? – David Browne - Microsoft Sep 17 '18 at 18:12
  • Hi @DavidBrowne-Microsoft please see edits. Thanks – beehive Sep 17 '18 at 18:47
  • That's not the XML Schema that's bound to the target column. You would get that by scripting the XML Schema Collection with SSMS, or running a query like SELECT xsc.name AS [Name], sch.name AS [Schema], XML_SCHEMA_NAMESPACE(sch.Name, xsc.name) AS [Text] FROM sys.xml_schema_collections AS xsc INNER JOIN sys.schemas AS sch ON xsc.schema_id = sch.schema_id where xsc.name = 'ColumnNameSchemaCollection' – David Browne - Microsoft Sep 17 '18 at 19:04
  • I used your code to generate above. Thanks for your help so far @DavidBrowne-Microsoft – beehive Sep 17 '18 at 19:11
0

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
  • I tried that but The value of attribute 'name' does not conform to the type definition 'w3.org/2001/XMLSchema#NCName': 'microsoft.sqlserver.types, version=0.0.0.0, culture=neutral, publickeytoken=xxxxxxx, processorarchitecture=msil'. I really don't understand any of what I'm doing. I've cloned a database, now I want to migrate some data, any DDL structures should just match – beehive Sep 17 '18 at 19:43
  • The issue was that I used dbcc clonedatabase to create an empty database and push a subset of data into it. But the schema collection generated didn't match, hence the insert failed. No idea why clonedatabase wont generate the correct one. As mentioned above I had to drop and recreate the table and schema collection – beehive Sep 18 '18 at 8:56
  • Did you use the VERIFY_CLONEDB option? – David Browne - Microsoft Sep 18 '18 at 12:29
  • VERIFY_CLONEDB didn't help unfortunately – beehive Sep 18 '18 at 16:14
  • Hmm. Still can't repro. – David Browne - Microsoft Sep 18 '18 at 16:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.