XML Bulk Load - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

XML Bulk Load

Author

Topic

keg1029
Starting Member

3 Posts

Posted - 2007-05-09 : 12:13:28

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

187 Posts

Posted - 2007-05-09 : 14:23:35

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.

keg1029
Starting Member

3 Posts

Posted - 2007-05-09 : 14:46:22

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?

Kristen
Test

22859 Posts

Posted - 2007-05-09 : 15:05:34

"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

keg1029
Starting Member

3 Posts

Posted - 2007-05-09 : 15:52:11

Kristen....

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

Thanks,

Ken...

KEG

Kristen
Test

22859 Posts

Posted - 2007-05-09 : 16:11:28

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources