SQLXML Bulk Load error: "Schema: the parent/child table relationship on 'call' does not match error 80004005 Source: Schema Mapping

Question

Hi All,
   I have attempted to create a schema file for my xml data but am receiving the above error message. I have not been able to pinpoint the problem.

Here is my schema:

<?xml version="1.0"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema"
           xmlns:sql="urn:schemas-microsoft-com:mapping-schema">

<xs:annotation>
   <xs:appinfo>
  
   <!-- Calls / Events Table Relationships -->
  
      <sql:relationship name="CallEventsCallnum"
         parent="Calls"
      parent-key="callnum"
         child="Events"
      child-key="callnum" />

      <sql:relationship name="CallEventsDate"
         parent="Calls"
    parent-key="date"
         child="Events"
    child-key="date" />
   
   <!-- Events / EventExtensions Table Relationships-->
    <sql:relationship name="EventsExtDate"
         parent="Events"
    parent-key="date"
         child="EventExtensions"
    child-key="date" />

      <sql:relationship name="EventsExtCallnum"
         parent="Events"
    parent-key="callnum"
         child="EventExtensions"
    child-key="callnum" />

      <sql:relationship name="EventsExtEventnum"
         parent="Events"
    parent-key="eventnum"
         child="EventExtensions"
    child-key="eventnum" />
   
   <!-- Events / EventAgents Table Relationships-->
    <sql:relationship name="EventsAgentsDate"
         parent="Events"
    parent-key="date"
         child="EventAgents"
    child-key="date" />

      <sql:relationship name="EventsAgentsCallnum"
         parent="Events"
    parent-key="callnum"
         child="EventAgents"
    child-key="callnum" />

      <sql:relationship name="EventsAgentsEventnum"
         parent="Events"
    parent-key="eventnum"
         child="EventAgents"
    child-key="eventnum" />
   
   <!-- Events / EventQueues Table Relationships-->
    <sql:relationship name="EventsQueuesDate"
         parent="Events"
    parent-key="date"
         child="EventQueues"
    child-key="date" />

      <sql:relationship name="EventsQueuesCallnum"
         parent="Events"
    parent-key="callnum"
         child="EventQueues"
    child-key="callnum" />

      <sql:relationship name="EventsQueuesEventnum"
         parent="Events"
    parent-key="eventnum"
         child="EventQueues"
    child-key="eventnum" />
   
   <!-- Events / EventTrunks Table Relationships-->
    <sql:relationship name="EventsTrunksDate"
         parent="Events"
    parent-key="date"
         child="EventTrunks"
    child-key="date" />

      <sql:relationship name="EventsTrunksCallnum"
         parent="Events"
    parent-key="callnum"
         child="EventTrunks"
    child-key="callnum" />

      <sql:relationship name="EventsTrunksEventnum"
         parent="Events"
    parent-key="eventnum"
         child="EventTrunks"
    child-key="eventnum" />
   </xs:appinfo>
</xs:annotation>
        
   <xs:element name="call_filter_results" >
    <xs:complexType>
      <xs:sequence>
        <xs:element  name="calls" sql:is-constant="1"  >
          <xs:complexType>
            <xs:sequence> 
              <xs:element  name="call" sql:relation="Call" sql:relationship="CallEventsCallnum CallEventsDate" >
                 <xs:complexType>
                  <xs:sequence>
                    <xs:element  name="events" >
                      <xs:complexType>
                        <xs:sequence>
                          <xs:element   name="event" sql:relation="Events"
              sql:relationship="CallEventsCallnum CallEventsDate">
                            <xs:complexType>
                              <xs:sequence >
                                <xs:choice >
                                  <xs:element   name="ext" sql:relation="EventExtensions"
                                 sql:relationship="EventsExtDate EventsExtCallnum EventsExtEventnum">
         
                                    <xs:complexType>
                                      <xs:attribute name="key" type="xs:string" use="optional" />
                                      <xs:attribute name="id" type="xs:unsignedShort" use="optional" />
                                      <xs:attribute name="name" type="xs:string" use="optional" />
                                      <xs:attribute name="nick" type="xs:string" use="optional" />
                                      <xs:attribute name="vport" type="xs:unsignedByte" use="optional" />
                                      <xs:attribute name="autoattend" type="xs:unsignedByte" use="optional" />
                                      <xs:attribute name="qcc" type="xs:unsignedByte" use="optional" />
                                    </xs:complexType>
                                  </xs:element>
                                  <xs:element  name="trnk" sql:relation="EventTrunks"
                                sql:relationship="EventsTrunksDate EventsTrunksCallnum EventsTrunksEventnum ">
                                    <xs:complexType>
                                      <xs:attribute name="key" type="xs:string" use="optional" />
                                      <xs:attribute name="id" type="xs:string" use="optional" />
                                      <xs:attribute name="name" type="xs:string" use="optional" />
                                      <xs:attribute name="nick" type="xs:string" use="optional" />
                                    </xs:complexType>
                                  </xs:element>
                                  <xs:element  name="queue" sql:relation="EventQueues"
                                sql:relationship="EventsQueuesDate EventsQueuesCallnum EventsQueuesEventnum ">
                                    <xs:complexType>
                                      <xs:attribute name="key" type="xs:string" use="optional" />
                                      <xs:attribute name="id" type="xs:unsignedShort" use="optional" />
                                      <xs:attribute name="name" type="xs:string" use="optional" />
                                      <xs:attribute name="nick" type="xs:string" use="optional" />
                                    </xs:complexType>
                                  </xs:element>
                                  <xs:element   name="agent" sql:relation="EventAgents"
                               sql:relationship="EventsAgentsDate EventsAgentsCallnum EventsAgentsEventnum ">
                                    <xs:complexType>
                                      <xs:attribute name="key" type="xs:string" use="optional" />
                                      <xs:attribute name="id" type="xs:unsignedShort" use="optional" />
                                      <xs:attribute name="name" type="xs:string" use="optional" />
                                      <xs:attribute name="nick" type="xs:string" use="optional" />
                                    </xs:complexType>
                                  </xs:element>
                                </xs:choice>
                              </xs:sequence>
                              <xs:attribute name="starttime" type="xs:unsignedShort" use="optional" />
                              <xs:attribute name="callkey" type="xs:unsignedByte" use="optional" />
                              <xs:attribute name="endtime" type="xs:unsignedShort" use="optional" />
                              <xs:attribute name="type" type="xs:unsignedByte" use="optional" />
                              <xs:attribute name="ani" type="xs:string" use="optional" />
                              <xs:attribute name="dnis" type="xs:string" use="optional" />
                              <xs:attribute name="digits" type="xs:string" use="optional" />
                              <xs:attribute name="cause" type="xs:unsignedByte" use="optional" />
                              <xs:attribute name="montd" type="xs:unsignedByte" use="optional" />
                              <xs:attribute name="rqud" type="xs:unsignedByte" use="optional" />
                              <xs:attribute name="intf" type="xs:unsignedByte" use="optional" />
                              <xs:attribute name="overflowed" type="xs:unsignedByte" use="optional" />
                              <xs:attribute name="ani_prefix" type="xs:unsignedShort" use="optional" />
                              <xs:attribute name="ani_areacode" type="xs:unsignedShort" use="optional" />
                              <xs:attribute name="digits_prefix" type="xs:unsignedShort" use="optional" />
                              <xs:attribute name="digits_areacode" type="xs:unsignedShort" use="optional" />
                              <xs:attribute name="dnis_prefix" type="xs:unsignedShort" use="optional" />
                              <xs:attribute name="dnis_areacode" type="xs:unsignedShort" use="optional" />
                            </xs:complexType>
                          </xs:element>
                         </xs:sequence>
                      </xs:complexType>
                    </xs:element>
                  </xs:sequence>
                  <xs:attribute name="id" type="xs:string" use="optional" />
                  <xs:attribute name="callkey" type="xs:unsignedByte" use="optional" />
                  <xs:attribute name="date" type="xs:date" use="optional" />
                  <xs:attribute name="acd" type="xs:unsignedByte" use="optional" />
                  <xs:attribute name="starttime" type="xs:unsignedShort" use="optional" />
                  <xs:attribute name="endtime" type="xs:unsignedShort" use="optional" />
                  <xs:attribute name="type" type="xs:unsignedByte" use="optional" />
                  <xs:attribute name="result" type="xs:unsignedByte" use="optional" />
                  <xs:attribute name="from" type="xs:string" use="optional" />
                  <xs:attribute name="to" type="xs:string" use="optional" />
                  <xs:attribute name="dnis" type="xs:string" use="optional" />
                  <xs:attribute name="ansby" type="xs:string" use="optional" />
                  <xs:attribute name="requeues" type="xs:unsignedByte" use="optional" />
                  <xs:attribute name="intf" type="xs:unsignedByte" use="optional" />
                  <xs:attribute name="overflowed" type="xs:unsignedByte" use="optional" />
                  <xs:attribute name="transfd" type="xs:unsignedByte" use="optional" />
                  <xs:attribute name="held" type="xs:unsignedByte" use="optional" />
                  <xs:attribute name="confd" type="xs:unsignedByte" use="optional" />
                  <xs:attribute name="timetoabn" type="xs:unsignedShort" use="optional" />
                  <xs:attribute name="timetoans" type="xs:unsignedShort" use="optional" />
                  <xs:attribute name="tottalktm" type="xs:unsignedShort" use="optional" />
                  <xs:attribute name="totconftm" type="xs:unsignedByte" use="optional" />
                  <xs:attribute name="totholdtm" type="xs:unsignedShort" use="optional" />
                  <xs:attribute name="ucid" type="xs:unsignedLong" use="optional" />
                 </xs:complexType>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
       </xs:element>
      </xs:sequence>
      <xs:attribute name="ver" type="xs:decimal" use="optional" />
    </xs:complexType>
  </xs:element> 
</xs:schema>

Any help would be greatly appreciated.

Thank you in advance.

Answer 1

The relationships you've defined don't exist, eg CallEventsCallnum CallEventsDate does not exist.

A relationship specified in an element should be a single relationship, eg

sql:relationship="CallEventsCallnum" - no spaces and refer to a relationship you have defined in the annotation.

Answer 2

I was trying to show that there were multiple relationships. When I tried your suggestion of having a single relationship, I still received the same error.

Answer 3

Got a small piece of sample XML?  Presumably the data-types match on the columns in your relationships.

Answer 4

 Hope this piece works for you:

<call_filter_results ver="1.0">

- <calls>

- <call id="023C" callkey="1" date="10132009" acd="0" starttime="27925" endtime="27977" type="3" result="2" from="ext123" to="" dnis="" ansby="" requeues="0" intf="0" overflowed="0" transfd="0" held="0" confd="0" timetoabn="52" timetoans="0" tottalktm="0" totconftm="0" totholdtm="0" ucid="08275005728569326">

- <events>

- <event starttime="27925" callkey="1" endtime="27925" type="1" ani="" dnis="" digits="5999" cause="0" montd="0" rqud="0" intf="0" overflowed="0">

  <ext key="default" id="6123" name="a name" nick="" vport="0" autoattend="0" qcc="0" />

  </event>

- <event starttime="27925" callkey="1" endtime="27927" type="2" ani="" dnis="" digits="" cause="22" montd="0" rqud="0" intf="0" overflowed="0">

  <ext key="from" id="6123" name="a name" nick="" vport="0" autoattend="0" qcc="0" />

  <ext key="default" id="5003" name="A3" nick="" vport="1" autoattend="0" qcc="0" />

  </event>

- <event starttime="27927" callkey="1" endtime="27977" type="3" ani="" dnis="" digits="" cause="22" montd="0" rqud="0" intf="0" overflowed="0">

  <ext key="from" id="6123" name="a name" nick="" vport="0" autoattend="0" qcc="0" />

  <ext key="default" id="5003" name="A3" nick="" vport="1" autoattend="0" qcc="0" />

  <queue key="default" id="5999" name="" nick="" />

  </event>

  </events>

  </call>

  </calls>

  </call_filter_results>

Answer 5

I got this to work:

<?xml version="1.0" encoding="Windows-1252"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema"
           xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
  <xs:annotation>
    <xs:appinfo>
      <sql:relationship name="call__events" parent="call"
                        parent-key="id" child="events" child-key="id" />
      <sql:relationship name="events__event" parent="events"
                        parent-key="id" child="event" child-key="id" />
      <sql:relationship name="event__ext" parent="event"
                        parent-key="id" child="ext" child-key="id" />
      <sql:relationship name="event__queue" parent="event"
                        parent-key="id" child="queue" child-key="id" />
    </xs:appinfo>
  </xs:annotation>

  <xs:element name="call" >
    <xs:complexType>
      <xs:sequence>
        <xs:element name="events" sql:relationship="call__events">
          <xs:complexType>
            <xs:sequence>
              <xs:element maxOccurs="unbounded" name="event" sql:relationship="events__event">
                <xs:complexType>
                  <xs:sequence>
                    <xs:element maxOccurs="unbounded" name="ext" sql:relationship="event__ext">
                      <xs:complexType>
                        <xs:attribute name="key" sql:field="xkey" type="xs:string" use="required" />
                        <xs:attribute name="id" type="xs:unsignedShort" use="required" />
                        <xs:attribute name="name" type="xs:string" use="required" />
                        <xs:attribute name="nick" type="xs:string" use="required" />
                        <xs:attribute name="vport" type="xs:unsignedByte" use="required" />
                        <xs:attribute name="autoattend" type="xs:unsignedByte" use="required" />
                        <xs:attribute name="qcc" type="xs:unsignedByte" use="required" />
                      </xs:complexType>
                    </xs:element>
                    <xs:element minOccurs="0" name="queue"  sql:relationship="event__queue">
                      <xs:complexType>
                        <xs:attribute name="key" sql:field="xkey" type="xs:string" use="required" />
                        <xs:attribute name="id" type="xs:unsignedShort" use="required" />
                        <xs:attribute name="name" type="xs:string" use="required" />
                        <xs:attribute name="nick" type="xs:string" use="required" />
                      </xs:complexType>
                    </xs:element>
                  </xs:sequence>
                  <xs:attribute name="starttime" type="xs:unsignedShort" use="required" />
                  <xs:attribute name="callkey" type="xs:unsignedByte" use="required" />
                  <xs:attribute name="endtime" type="xs:unsignedShort" use="required" />
                  <xs:attribute name="type" type="xs:unsignedByte" use="required" />
                  <xs:attribute name="ani" type="xs:string" use="required" />
                  <xs:attribute name="dnis" type="xs:string" use="required" />
                  <xs:attribute name="digits" type="xs:string" use="required" />
                  <xs:attribute name="cause" type="xs:unsignedByte" use="required" />
                  <xs:attribute name="montd" type="xs:unsignedByte" use="required" />
                  <xs:attribute name="rqud" type="xs:unsignedByte" use="required" />
                  <xs:attribute name="intf" type="xs:unsignedByte" use="required" />
                  <xs:attribute name="overflowed" type="xs:unsignedByte" use="required" />
                </xs:complexType>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
      <xs:attribute name="id" type="xs:string" use="required" />
      <xs:attribute name="callkey" type="xs:unsignedByte" use="required" />
      <xs:attribute name="date" type="xs:unsignedInt" use="required" />
      <xs:attribute name="acd" type="xs:unsignedByte" use="required" />
      <xs:attribute name="starttime" type="xs:unsignedShort" use="required" />
      <xs:attribute name="endtime" type="xs:unsignedShort" use="required" />
      <xs:attribute name="type" type="xs:unsignedByte" use="required" />
      <xs:attribute name="result" type="xs:unsignedByte" use="required" />
      <xs:attribute name="from" sql:field="where_from" type="xs:string" use="required" />
      <xs:attribute name="to" sql:field="where_to" type="xs:string" use="required" />
      <xs:attribute name="dnis" type="xs:string" use="required" />
      <xs:attribute name="ansby" type="xs:string" use="required" />
      <xs:attribute name="requeues" type="xs:unsignedByte" use="required" />
      <xs:attribute name="intf" type="xs:unsignedByte" use="required" />
      <xs:attribute name="overflowed" type="xs:unsignedByte" use="required" />
      <xs:attribute name="transfd" type="xs:unsignedByte" use="required" />
      <xs:attribute name="held" type="xs:unsignedByte" use="required" />
      <xs:attribute name="confd" type="xs:unsignedByte" use="required" />
      <xs:attribute name="timetoabn" type="xs:unsignedByte" use="required" />
      <xs:attribute name="timetoans" type="xs:unsignedByte" use="required" />
      <xs:attribute name="tottalktm" type="xs:unsignedByte" use="required" />
      <xs:attribute name="totconftm" type="xs:unsignedByte" use="required" />
      <xs:attribute name="totholdtm" type="xs:unsignedByte" use="required" />
      <xs:attribute name="ucid" type="xs:unsignedLong" use="required" />
    </xs:complexType>
  </xs:element>
</xs:schema>

And this VBScript:

Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.4.0")
objBL.ConnectionString = "provider=SQLOLEDB;data source=.\sql2005;database=tempdb;integrated security=SSPI"
objBL.SGDropTables = True
objBL.SchemaGen = True

objBL.Execute "test.xsd", "test.xml"
Set objBL = Nothing

Answer 6

Thank you for your reply. I used your modified schema and now I get an error on the events relationship: "Schema: the parent/child table relationship on 'events' does not match error 80004005 Source: Schema Mapping"

What do I seem to be missing? It worked for you but doesn't want to work for me...

Thank you in advance.

Answer 7

Did you get this working?  That XSD worked for the XML you supplied, so if you've got some slightly different XML you may need to change the XSD.

Answer 8

Not yet. Now I have another error to contend with. "Invalid character value for CAST specification".

Answer 9

If I comment out the call elements. The vbscript works and populates the event table. I get the CAST error seemingly due to the call element / table. These are the attributes for the call element.:

        <xs:attribute name="id" type="xs:string" use="required" />
       <xs:attribute name="callkey" sql:field="callnum" type="xs:integer" use="required" />
       <xs:attribute name="date" type="xs:dateTime" use="required" />
      <xs:attribute name="starttime" type="xs:integer" use="required" />
      <xs:attribute name="endtime" type="xs:integer" use="required" />
      <xs:attribute name="type" type="xs:integer" use="required" />
      <xs:attribute name="result" type="xs:integer" use="required" />
     <xs:attribute name="from" sql:field="where_from" type="xs:string" use="required" />
      <xs:attribute name="to" sql:field="where_to" type="xs:string" use="required" />
      <xs:attribute name="dnis" type="xs:integer" use="required" />
      <xs:attribute name="ansby" type="xs:string" use="required" />
      <xs:attribute name="requeues" type="xs:integer" use="required" />
      <xs:attribute name="intf" type="xs:integer" use="required" />
      <xs:attribute name="overflowed" type="xs:integer" use="required" />
      <xs:attribute name="transfd" type="xs:integer" use="required" />
      <xs:attribute name="held" type="xs:integer" use="required" />
      <xs:attribute name="confd" type="xs:integer" use="required" />
      <xs:attribute name="timetoabn" type="xs:integer" use="required" />
      <xs:attribute name="timetoans" type="xs:integer" use="required" />
      <xs:attribute name="tottalktm" type="xs:integer" use="required" />
      <xs:attribute name="totconftm" type="xs:integer" use="required" />
   <xs:attribute name="acd" type="xs:integer" use="required" />
      <xs:attribute name="totholdtm" type="xs:integer" use="required" />
      <xs:attribute name="ucid" type="xs:unsignedLong" use="required" />

Here's my call table:

CREATE

 

TABLE [dbo].[call](

[id] [nvarchar]

(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[callnum] [int]

NULL,

[date] [datetime]

NULL,

[starttime] [int]

NULL,

[endtime] [int]

NULL,

[type] [int]

NULL,

[result] [int]

NULL,

[where_from] [nvarchar]

(1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[where_to] [nvarchar]

(1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[dnis] [int]

NULL,

[ansby] [nvarchar]

(1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[requeues] [int]

NULL,

[intf] [int]

NULL,

[overflowed] [int]

NULL,

[transfd] [int]

NULL,

[held] [int]

NULL,

[confd] [int]

NULL,

[timetoabn] [int]

NULL,

[timetoans] [int]

NULL,

[tottalktm] [int]

NULL,

[totconftm] [int]

NULL,

[totholdtm] [int]

NULL,

[ucid] [bigint]

NULL,

[acd] [int]

NULL

Do you see something wrong with the relation between the element attributes and the table?


Thank you in advance.

Answer 10

This is most likely your datetime data.  Change the following line:

 <xs:attribute name="date" type="xs:dateTime" use="required" /> 

to

 <xs:attribute name="date" type="xs:string" use="required" />


Otherwise, comment out individual attributes one by one to see which one is failing; an XML comment is like this:

<!--

 

<xs:attribute name="date" type="xs:string" use="required" />-->

Answer 11

Thanks. I commented them as you suggested. It seems all of the call attributes produce the CAST error.

Answer 12

This is still not working for me. I'm back at the original error but on events: the the parent/child table relationship on 'events' does not match error 80004005 Source: Schema Mapping.

Answer 13

Antonie,

Can you please make sure whatever tablenames, element names you use in your xsd schema are exactly matching. (Also in the sql:relationship)

It is case sensitive in nature and I am seeing lot of mismatches over there in the schema.

Please try it n let us know if its still giving problem

Regards
Shiv

Answer 14

Still no go.

Answer 15

Antoine,

as suggested above, please start with the example I provided which I believe to be in working order.  Make sure it works for you.  If it doesn't, report back and we'll go from there.

If it does, modify it incrementally until it meets your needs.

Keep trying!

Answer 16

I used openrowset bulk along with openxml and

sp_xml_preparedocument

 

 

 instead and works beautifully! Thank you for you support. Maybe someday SQLXML will work for me...

Answer 17

Ok, make sure you call sp_xml_removedocument as soon as possible.  SQLXML is great for really big documents too.

NB If you're in SQL 2005 you should be moving away from OPENXML.

Stop Using OPENXML (Please...)
https://www.sqlservercentral.com/blogs/michael_coles/archive/2008/01/20/stop-using-openxml-please.aspx

Answer 18

Thanks for the tip.