 Propagate Identity Column Multiple Tables Using SQLXMLBulkLoad
Hi All,
It's been a long time since I was here. Hope you are all well and making a good living.
I have hit a bit of brick wall with some XML / SQL Server 2000 issue and wondered if there were any "Experts" out there that could provide that nugget of information to help me solve a problem.
Here is some background.....
I stumbled across your entry "Propagating identity values in multiple tables using SQLXMLBulkLoad" (http://blogs.msdn.com/monicafrintu/archive/2007/06/14/propagating-identity-values-in-multiple-tables-using-sqlxmlbulkload.aspx)
And I thought it was the answer to my prayers.
Sadly, I cannot get it to work when there is a slightly more complicated example than the one shown.
No matter what I try I get the failure :-
CREATE DEFAULT must be the first statement in a query batch
My XML Structure is as follows :-
<?xml version="1.0" encoding="ISO-8859-1" ?>
<calls xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="data.xsd">
<call>
<interface_process_id>1508</interface_process_id>
<mode><![CDATA[NEW]]></mode>
<job><![CDATA[90000001]]></job>
<visit><![CDATA[2008-02-18]]></visit>
<fru><![CDATA[CDAM]]></fru>
<allday><![CDATA[ALL DAY]]></allday>
<commentscus><![CDATA[TEST]]></commentscus>
<commentseng><![CDATA[]]></commentseng>
<bib><![CDATA[N]]></bib>
<removestb><![CDATA[N]]></removestb>
<fasttrack><![CDATA[N]]></fasttrack>
<stbcount><![CDATA[1]]></stbcount>
<diaryslots><![CDATA[85.0]]></diaryslots>
<engineer>
<id><![CDATA[CDA1001]]></id>
</engineer>
<customer>
<id><![CDATA[59302678]]></id>
<subscriber><![CDATA[240000000135]]></subscriber>
<vip><![CDATA[N]]></vip>
<title><![CDATA[MR]]></title>
<initials><![CDATA[M]]></initials>
<surname><![CDATA[XXXXXXX]]></surname>
<street><![CDATA[XXXXXXXXXXX]]></street>
<city><![CDATA[XXXXXXXXXXXXXXXXXXX]]></city>
<county><![CDATA[XXXXXXXXXXXXXXXXXXXXX]]></county>
<phonehome><![CDATA[0000000000000000]]></phonehome>
<phonework><![CDATA[]]></phonework>
<postcode><![CDATA[XXX XXX]]></postcode>
</customer>
<contact>
<name><![CDATA[SIDDALL]]></name>
<phone><![CDATA[01501744130]]></phone>
<salutation><![CDATA[MR]]></salutation>
</contact>
<contract>
<name><![CDATA[DIX002]]></name>
<description><![CDATA[NS - Dixon Stores Group]]></description>
</contract>
<service>
<name><![CDATA[ID1]]></name>
<description><![CDATA[Standard Digital Install & Phone Link]]></description>
</service>
<product>
<name><![CDATA[AMS100Z2]]></name>
<description><![CDATA[PRODUCT 1]]></description>
</product>
<cost>
<total><![CDATA[ 117.50]]></total>
<prepaid><![CDATA[ 0.00]]></prepaid>
<outstanding><![CDATA[ 117.50]]></outstanding>
</cost>
</call>
<visitHistorys>
<visitHistory>
<visitNumber>30068347</visitNumber>
<visitDate>2007-11-01 00:00:00.0 GMT</visitDate>
<visitType>Install</visitType>
<engineerID>80100</engineerID>
<visitStatus>CP</visitStatus>
<statusCodes>
<statusCode>
<type>Cause</type>
<code>38</code>
<desc>Broken Connectors</desc>
</statusCode>
</statusCodes>
</visitHistory>
<contactDetails>
<contactNumber>
<description>XXXXXXXXXX</description>
<number>XXXXXXXXXX</number>
</contactNumber>
</contactDetails>
<maxNoVisit>6</maxNoVisit>
<timePeriodMonths>12</timePeriodMonths>
</visitHistorys>
</calls>
And have devised an XSD :-
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns="http://tempuri.org/XMLSchema.xsd"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="History"
parent="tbl_Job_Raw_XML"
child="tbl_Job_Raw_XML_History"
parent-key="Job_Raw_XML_Id"
child-key="Job_Raw_XML_Id" />
<sql:relationship name="Visit"
parent="tbl_Job_Raw_XML_History"
child="tbl_Job_Raw_XML_Visit"
parent-key="Job_Raw_XML_History_Id"
child-key="Job_Raw_XML_History_Id" />
<sql:relationship name="Status"
parent="tbl_Job_Raw_XML_Visit"
child="tbl_Job_Raw_XML_Status"
parent-key="Job_Raw_XML_Visit_Id"
child-key="Job_Raw_XML_Visit_Id" />
<sql:relationship name="Contact"
parent="tbl_Job_Raw_XML_History"
child="tbl_Job_Raw_XML_Contact"
parent-key="Job_Raw_XML_History_Id"
child-key="Job_Raw_XML_History_Id" />
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="calls" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="call" sql:relation="tbl_Job_Raw_XML">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="interface_process_id" type="xsd:integer" sql:field="Interface_Process_Id" minOccurs="1" maxOccurs="1" />
<xsd:element name="mode" type="xsd:token" sql:field="Mode" minOccurs="1" maxOccurs="1" />
<xsd:element name="fru" type="xsd:token" sql:field="FRU" />
<xsd:element name="job" type="xsd:token" sql:field="Job_No" />
<xsd:element name="reference" type="xsd:token" sql:field="Job_Reference" />
<xsd:element name="visit" type="xsd:token" sql:field="Job_Date" />
<xsd:element name="allday" type="xsd:token" sql:field="All_Day" />
<xsd:element name="commentscus" type="xsd:token" sql:field="Comments_Customer" />
<xsd:element name="commentseng" type="xsd:token" sql:field="Comments_Resource" />
<xsd:element name="bib" type="xsd:token" sql:field="BIB_Ind" />
<xsd:element name="removestb" type="xsd:token" sql:field="Remove_STB_Ind" />
<xsd:element name="fasttrack" type="xsd:token" sql:field="Fast_Track_Ind" />
<xsd:element name="sit" type="xsd:token" sql:field="SIT_Ind" />
<xsd:element name="lip" type="xsd:token" sql:field="LIP_Ind" />
<xsd:element name="stbcount" type="xsd:token" sql:field="STB_Count" />
<xsd:element name="diaryslots" type="xsd:token" sql:field="Diary_Slots" />
<xsd:element name="engineer" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="id" type="xsd:token" sql:field="Engineer_Id" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="customer" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="id" type="xsd:token" sql:field="SKY_Customer_Id" />
<xsd:element name="subscriber" type="xsd:token" sql:field="SKY_Subscriber_No" />
<xsd:element name="vip" type="xsd:token" sql:field="Customer_VIP" />
<xsd:element name="title" type="xsd:token" sql:field="Customer_Title" />
<xsd:element name="initials" type="xsd:token" sql:field="Customer_Initials" />
<xsd:element name="surname" type="xsd:token" sql:field="Customer_Surname" />
<xsd:element name="street" type="xsd:token" sql:field="Customer_Street" />
<xsd:element name="city" type="xsd:token" sql:field="Customer_City" />
<xsd:element name="county" type="xsd:token" sql:field="Customer_County" />
<xsd:element name="phonehome" type="xsd:token" sql:field="Customer_Phone_Home" />
<xsd:element name="phonework" type="xsd:token" sql:field="Customer_Phone_Work" />
<xsd:element name="postcode" type="xsd:token" sql:field="Customer_Post_Code" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="contact" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="name" type="xsd:token" sql:field="Customer_Contact_Name" />
<xsd:element name="phone" type="xsd:token" sql:field="Customer_Contact_Phone" />
<xsd:element name="salutation" type="xsd:token" sql:field="Customer_Contact_Salutation" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="contract" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="name" type="xsd:token" sql:field="Contract_Name" />
<xsd:element name="description" type="xsd:token" sql:field="Contract_Descr" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="service" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="name" type="xsd:token" sql:field="Service_Name" />
<xsd:element name="description" type="xsd:token" sql:field="Service_Descr" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="product" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="name" type="xsd:token" sql:field="Product_Name" />
<xsd:element name="description" type="xsd:token" sql:field="Product_Descr" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="warranty" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="reference" type="xsd:token" sql:field="Warranty_Reference" />
<xsd:element name="date" type="xsd:token" sql:field="Warranty_Date" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="fault" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="name" type="xsd:token" sql:field="Fault_Name" />
<xsd:element name="description" type="xsd:token" sql:field="Fault_Descr" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="history" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="engineerOrig" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="id" type="xsd:token" sql:field="Engineer_Orig_Id" />
<xsd:element name="name" type="xsd:token" sql:field="Engineer_Orig_Name" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="engineerLast" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="id" type="xsd:token" sql:field="Engineer_Last_Id" />
<xsd:element name="name" type="xsd:token" sql:field="Engineer_Last_Name" />
<xsd:element name="type" type="xsd:token" sql:field="Engineer_Last_Type" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="jobcount" type="xsd:token" sql:field="Job_Count" />
<xsd:element name="installdate" type="xsd:token" sql:field="Installation_Date" />
<xsd:element name="lastdate" type="xsd:token" sql:field="Last_Date" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="enquiry" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="enqreference" type="xsd:token" sql:field="Enquiry_Ref" />
<xsd:element name="enqreason" type="xsd:token" sql:field="Enquiry_Reason" />
<xsd:element name="enqsreason" type="xsd:token" sql:field="Enquiry_Sub_Reason" />
<xsd:element name="enqdescription" type="xsd:token" sql:field="Enquiry_Descr" />
<xsd:element name="enqcomments1" type="xsd:token" sql:field="Enquiry_Comments_1" />
<xsd:element name="enqcomments2" type="xsd:token" sql:field="Enquiry_Comments_2" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="cost" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="total" type="xsd:token" sql:field="Cost_Total" />
<xsd:element name="prepaid" type="xsd:token" sql:field="Cost_Pre_Paid" />
<xsd:element name="outstanding" type="xsd:token" sql:field="Cost_Outstanding" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="summary" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="count" type="xsd:token" sql:field="Call_Count" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="cancel_code" type="xsd:token" sql:field="Cancel_Code" />
<xsd:element name="cancel_reason" type="xsd:token" sql:field="Cancel_Reason" />
<xsd:element name="visitHistorys" sql:relation="tbl_Job_Raw_XML_History" sql:relationship="History">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="visitHistory" sql:relation="tbl_Job_Raw_XML_Visit" sql:relationship="Visit">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="visitNumber" type="xsd:token" sql:field="Visit_Number" />
<xsd:element name="visitDate" type="xsd:date" sql:field="Visit_Date" />
<xsd:element name="visitType" type="xsd:token" sql:field="Visit_Type" />
<xsd:element name="engineerID" type="xsd:token" sql:field="Engineer_Id" />
<xsd:element name="visitStatus" type="xsd:token" sql:field="Visit_Status" />
<xsd:element name="statusCodes" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="statusCode" sql:relation="tbl_Job_Raw_XML_Status" sql:relationship="Status">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="type" type="xsd:token" sql:field="Type" />
<xsd:element name="code" type="xsd:token" sql:field="Code" />
<xsd:element name="desc" type="xsd:token" sql:field="Desc" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="contactDetails" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="contactNumber" sql:relation="tbl_Job_Raw_XML_Contact" sql:relationship="Contact">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="description" type="xsd:token" sql:field="Description" />
<xsd:element name="number" type="xsd:token" sql:field="Number" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="maxNoVisit" type="xsd:token" sql:field="Max_No_Visit" />
<xsd:element name="timePeriodMonths" type="xsd:token" sql:field="Time_Period_Months" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
In "English", I have 4 tables :-
tbl_Job_Raw_XML
tbl_Job_Raw_XML_History
tbl_Job_Raw_XML_Visit
tbl_Job_Raw_XML_Status
tbl_Job_Raw_XML_Contact
tbl_Job_Raw_XML_History is a child of tbl_Job_Raw_XML
tbl_Job_Raw_XML_Visit is a child of tbl_Job_Raw_XML_History
tbl_Job_Raw_XML_Status is a child of tbl_Job_Raw_XML_Visit
tbl_Job_Raw_XML_Contact is a child of tbl_Job_Raw_XML_History
(NOT MY DESIGN!)
I was praying that by following the example I could extend it to cater with this more complicated example.
Just in case this was too complicated, I changed the XML and the XSD to only refer to 2 tables :-
tbl_Job_Raw_XML
tbl_Job_Raw_XML_History
And no matter what I do, I cannot get the thing to work.
What I am expecting is to propogate the Identity Column from tbl_Job_Raw_XML down onto tbl_Job_Raw_XML_History. This just does not happen.
If I am doing something wrong, or the SQLXMLBulkLoad'er cannot support this complexity, I would very much appreciate your guidance.
So that you know, I am using :-
Windows 2000 SP4
SQL Server 2000 SP3
SQLXML 4.0
Sorry this is such a long post!
Yours.....
Simple Simon Started By swjs on Feb 18, 2008 at 9:46:24 AM | 
