XML Bulk Load
-
I am trying to import an XML file into SQL Server 2000 as in http://qa.sqlservercentral.com/columnists/smoore/importingxmlfilesintosqlserver.asp
I can work the examples just fine, but when I try it with my “real world” data I’m not having much luck. I think the problem is in my Schema Definition (XML.xsd) file. Unfortunately I don’t have any control over the format of the XML data, so I can’t change it to something more like one of the examples. The problem has to do with the way the Cat, CatID, and Zip’s are laid out. I would appreciate any help anyone could give me on this.
My table is
CREATE TABLE Buyers
( Buyer_ID INT NULL
, Name VARCHAR(60) NULL
, Address1 VARCHAR(100) NULL
, City VARCHAR(60) NULL
, State VARCHAR(2) NULL
, Buyer_Zip VARCHAR(10) NULL
, Contact VARCHAR(30) NULL
, Phone VARCHAR(16) NULL
, CatID INT NULL
, Cat VARCHAR(30) NULL
, Zip VARCHAR(10) NULL
)
Go
XML.xml is
<ROOT>
<retail_buyer>
<RetailBuyerID>1818</RetailBuyerID>
<name>Rick Tester</name>
<address>999 Nowhere st</address>
<city>999 Nowhere st</city>
<state>CA</state>
<zipcode>95624</zipcode>
<autorepcontact>Rick</autorepcontact>
<toll_free_number>8888888888</toll_free_number>
<coverage_entry>
<catalog>Catalog One</catalog>
<catalogID>1190</catalogID>
<zipcode>80001</zipcode>
<zipcode>80002</zipcode>
<zipcode>80003</zipcode>
</coverage_entry>
<coverage_entry>
<make>Catalog Two</catalog>
<makeID>1236</makeID>
<zipcode>10301</zipcode>
<zipcode>10302</zipcode>
<zipcode>10303</zipcode>
</coverage_entry>
</retail_buyer>
<retail_buyer>
<RetailBuyerID>2645</RetailBuyerID>
<name>Test of South Gate</name>
<address>
<city>
<state>CA</state>
<zipcode>90280</zipcode>
<autorepcontact>Daniel
<toll_free_number>8778888888</toll_free_number>
<coverage_entry>
<catalog>Catalog One</catalog>
<catalogID>1190</catalogID>
<zipcode>95501</zipcode>
<zipcode>95502</zipcode>
<zipcode>95503</zipcode>
</coverage_entry>
</retail_buyer>
</ROOT>
XML.xsd is
<xs:schema id="ROOT" xmlns=""
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xs:element name="ROOT" sql:relation="Buyers">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="retail_buyer" >
<xs:complexType>
<xs:sequence>
<xs:element name="RetailBuyerID"
type="xs:string"
minOccurs="0"
sql:field="Buyer_ID"/>
<xs:element name="name"
type="xs:string"
minOccurs="0"
sql:field="Name"/>
<xs:element name="address"
type="xs:string"
minOccurs="0"
sql:field="Address1"/>
<xs:element name="city"
type="xs:string"
minOccurs="0"
sql:field="City"/>
<xs:element name="state"
type="xs:string"
minOccurs="0"
sql:field="State"/>
<xs:element name="zipcode"
type="xs:string"
minOccurs="0"
sql:field="Buyer_Zip"/>
<xs:element name="autorepcontact"
type="xs:string"
minOccurs="0"
sql:field="Contact"/>
<xs:element name="toll_free_number"
type="xs:string"
minOccurs="0"
sql:field="Phone"/>
<xs:element name="coverage_entry"
minOccurs="0"
maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="catalog"
type="xs:string"
minOccurs="0"
sql:field="Cat"/>
<xs:element name="catalogID"
type="xs:string"
minOccurs="0"
sql:field="CatID"/>
<xs:element name="zipcode"
nillable="true"
minOccurs="0"
maxOccurs="unbounded"
sql:field="Zip"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
And the VBScript is
set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.3.0")
objBL.ConnectionString = "provider=SQLOLEDB;data source=SMONK2\SQL2000;database=Test;integrated security=SSPI"
objBL.ErrorLogFile = "error.log"
objBL.Execute "XML.xsd", "XML.xml"
set objBL=Nothing
-
The first thing I would look at is your table schema against the xml schema. Do you need two or three tables e.g. Buyer, CoverageEntry & ZipCode or are you definitely wanting to "flatten" the import in which case all record details will be repeated for each ZipCode entry as this is the lowest level.
I should be able to help with the schema once you've confirmed your destination import format.
-
I want to crawl before I walk. I know the table is "flat", but for now that is the way it needs to be.
-
Okay, I understand, but trying to flatten it into one table is actually more difficult that splitting it out. Keeping it in one table only makes the sql:relationship annotations more difficult to understand.
I've had a quick look at your samples and there are a few things:
1) The XML is invalid. The start tag make surrounding the text Catalog Two ends with the tag catalog (Retail buyer 1818, second coverage entry)
2) Is the structure of the coverage entries always catalog and catalogID followed by 3 zipcodes ?
3) Will you want two tables eventually with Retailbuyer and related coverage entries ?
Anyway, you can decide whether to look at these questions or for a quick, inelegant fix here is a schema that I have used to import all the items but it imports one duplicate and a null zipcode record for each Retailbuyer. You can obviously import this into a staging table, which I would recommend anyway, and SELECT DISTINCT....WHERE Zip IS NOT NULL to filter out the erroneous entries. Oh, it also relies on the fact that I amended the second coverageentry item to have catalog elements and not make elements. There was no mention of makes in your schema.
Hope it helps you get a bit further but obviously get back if not.
<?xml version="1.0" encoding="UTF-8"?>
<xs:schema id="ROOT" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xs:annotation>
<xs:appinfo>
<sql:relationship name="BuyersTable1" parent="Buyers" parent-key="Buyer_ID Name Address1 City State Buyer_Zip Contact Phone Cat CatID Zip" child="Buyers" child-key="Buyer_ID Name Address1 City State Buyer_Zip Contact Phone Cat CatID Zip"/>
</xs:appinfo>
</xs:annotation>
<xs:element name="ROOT" sql:is-constant="true">
<xs:complexType>
<xs:sequence>
<xs:element name="retail_buyer" minOccurs="0" maxOccurs="unbounded" sql:relation="Buyers" sql:relationship="BuyersTable1">
<xs:complexType>
<xs:sequence>
<xs:element name="RetailBuyerID" type="xs:string" minOccurs="0" sql:field="Buyer_ID"/>
<xs:element name="name" type="xs:string" minOccurs="0" sql:field="Name"/>
<xs:element name="address" type="xs:string" minOccurs="0" sql:field="Address1"/>
<xs:element name="city" type="xs:string" minOccurs="0" sql:field="City"/>
<xs:element name="state" type="xs:string" minOccurs="0" sql:field="State"/>
<xs:element name="zipcode" type="xs:string" minOccurs="0" sql:field="Buyer_Zip"/>
<xs:element name="autorepcontact" type="xs:string" minOccurs="0" sql:field="Contact"/>
<xs:element name="toll_free_number" type="xs:string" minOccurs="0" sql:field="Phone"/>
<xs:element name="coverage_entry" minOccurs="0" maxOccurs="unbounded" sql:relation="Buyers" sql:relationship="BuyersTable1" sql:key-fields="Buyer_ID Name Address1 City State Buyer_Zip Contact Phone Cat CatID Zip">
<xs:complexType>
<xs:sequence>
<xs:element name="catalog" type="xs:string" minOccurs="0" sql:field="Cat"/>
<xs:element name="catalogID" type="xs:string" minOccurs="0" sql:field="CatID"/>
<xs:element name="zipcode" type="xs:string" nillable="true" minOccurs="0" maxOccurs="unbounded" sql:field="Zip" sql:relation="Buyers" sql:relationship="BuyersTable1" sql:key-fields="Buyer_ID Name Address1 City State Buyer_Zip Contact Phone Cat CatID Zip"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
-
The XML is my bad, this is a significantly shortened and simplified version, and I changed some of the names to protect the guilty
, but obviously missed some.The number of zipcodes is indeterminate (0..n)
And yes, I will split it up eventualy, but I wanted to get it in a work table first as there is some other processing that needs to take place also.
Thank you very much for your help.
-
No problem. HTH.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply