SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 XML Bulk Load
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

keg1029
Starting Member

3 Posts

Posted - 05/09/2007 :  12:13:28  Show Profile  Send keg1029 an AOL message  Send keg1029 an ICQ Message  Click to see keg1029's MSN Messenger address  Send keg1029 a Yahoo! Message  Reply with Quote
I need to load an XML file for comparison into SQL Server 2000. I have worked through the examples and have successfully loaded to a table. My problem is that my XML isn't quite laid out the way the examples are. When I have a repeating element I receive and error. I need to know how to configure the data mapping schema to handle this. I've worked up an example of what I'm trying to do, including the table to be loaded the xml file, the schema, and the error I'm receiving.

Table:

CREATE TABLE [Customer] (
 [CustomerId] [int] NOT NULL ,
 [CompanyName] [nvarchar] (20),
 [City] [nvarchar] (20),
 [State] [nvarchar] (2))

GO




XML:

<ROOT>
  <Customers CustomerId="1111">
    <Company CompanyName="Sean Chai" />
    <CityName City="NY" State="NY"/>
    <CityName City="Buffalo" State="NY"/> <!-- repeat element that errors-->
  </Customers>
  <Customers CustomerId="1112">
    <Company CompanyName="Tom Johnston" />
    <CityName City="LA" State="CA"/>
  </Customers>
   <Customers CustomerId="1113">
   <Company CompanyName="Institute of Art"/>
   <CityName City="DC" State="DC"/>
  </Customers>
</ROOT>

XDR:

<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data" 
        xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"  
        xmlns:sql="urn:schemas-microsoft-com:xml-sql" > 

   <AttributeType name="CustomerId" dt:type="int" />
   <AttributeType name="CompanyName" dt:type="string" />
   <AttributeType name="City" dt:type="string" />
   <AttributeType name="State" dt:type="string" />

   <ElementType name="ROOT" sql:is-constant="1">
      <element type="Customers" />
   </ElementType>
      
   <ElementType name="Company" sql:relation="Customer">
     <attribute type="CompanyName"  />
   </ElementType>

   <ElementType name="CityName" sql:relation="Customer">
     <attribute type="City"  />
     <attribute type="State" />
   </ElementType>  
   
   <ElementType name="Customers"  sql:relation="Customer">
      <attribute type="CustomerId"  sql:field="CustomerId" /> 
      <element type="Company"    sql:field="CompanyName" />
      <element type="CityName"        sql:field="City" />
   </ElementType>
  

</Schema>

Error recieved from XML BULK LOAD:
Data mapping to column 'City' was already found in the data. Make sure that no two schema definitions map to the same column.

I'm sure that this is something simple to fix, I'm just not proficient with XML at all. Any help would be appreciated. It works perfectly as long as the CityName element only occurs once, but in the data I'm trying to load there may be 1 to n occurrence of an element within the parent.

Thanks for any assistance....

KEG...

cvraghu
Posting Yak Master

India
187 Posts

Posted - 05/09/2007 :  14:23:35  Show Profile  Reply with Quote
Your table is not normalized. It is not good to have multiple city/state values for same customer in a single table. You should move the address information to another table.

Look into the "Bulk loading XML data in multiple tables" section in
http://msdn2.microsoft.com/en-us/library/ms171806.aspx for examples on how to load two tables.
Go to Top of Page

keg1029
Starting Member

3 Posts

Posted - 05/09/2007 :  14:46:22  Show Profile  Send keg1029 an AOL message  Send keg1029 an ICQ Message  Click to see keg1029's MSN Messenger address  Send keg1029 a Yahoo! Message  Reply with Quote
I know that the table is not normalized but for the sake of argument and learning what is wrong with the Schema mapping, is there a way to change the mapping file to account for this?
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 05/09/2007 :  15:05:34  Show Profile  Reply with Quote
"is there a way to change the mapping file to account for this"

I doubt it - but if there is you will be pushing uphill!

We grab XML data into staging tables that mirror the structure of the XML, and then pump them into our tables - with whatever jiggery-pokery is needed to map between the XML/inbound schema and ours.

Please note that the other party's schema is always the one that is wrong!

Kristen
Go to Top of Page

keg1029
Starting Member

3 Posts

Posted - 05/09/2007 :  15:52:11  Show Profile  Send keg1029 an AOL message  Send keg1029 an ICQ Message  Click to see keg1029's MSN Messenger address  Send keg1029 a Yahoo! Message  Reply with Quote
Kristen....

So do you load the staging tables using the Multi table approach that Raghu mention from the MSDN example?

Thanks,

Ken...

KEG
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 05/09/2007 :  16:11:28  Show Profile  Reply with Quote
I haven't read the article in detail, but yes: we use SQLXMLBulkLoad to bulk load XML into staging tables, using an XML schema, and then process the data from there

Kristen
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000