XML Bulk Import Schema - 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 Import Schema

Author

Topic

Kristen
Test

22859 Posts

Posted - 2006-11-14 : 04:31:23

Anyone know about XML Schema for Bulk Import [SQLXMLBulkLoad] (or any other way to get XML into SQL Server 2000 in a reasonably efficient manner)?

I've got an XML file that looks like this:


<STOCK_ITEMS>
  <STOCK_ITEM>
    <STOCK_CODE>ABC123</STOCK_CODE>  <!-- Stock Code Level 1 -->
    <STOCK_DESC>Z-RAP LEATHER KIDS</STOCK_DESC>
    <SELL_PRICE>35</SELL_PRICE>
    <SCS>
      <STYLE>
        <DESCRIPTION>Pink/White</DESCRIPTION>  <!-- Description Level 1 -->
        <STYLE>
          <DESCRIPTION>10</DESCRIPTION>  <!-- Description Level 2 -->
          <SCS_ITEM>
            <STOCK_CODE>ABC123-PINK-10-</STOCK_CODE>  <!-- Stock Code Level 2 -->
            <DESCRIPTION>Z-Rap Leather Kids Pink/White / 10 /</DESCRIPTION>
            <SELL_PRICE>35</SELL_PRICE>

The Level 2 Stock Code needs to know what Parent (Level 1) Stock Code it is associated with, but also the Level 1 and level 2 Descriptions.

I can get the Parent (Level 1) Stock Code:


<sql:relationship 
	name="STOCK_SCS1"
	parent="staging_STOCK_ITEM"
	parent-key="STOCK_CODE"
	child="staging_SCS_ITEM"
	child-key="ASSOCIATED_MODULE" />
...
<xs:element name="SCS_ITEM" 
	minOccurs="0" 
	maxOccurs="unbounded" 
	sql:relation="staging_SCS_ITEM" 
	sql:relationship="STOCK_SCS1"
	sql:overflow-field="T_Overflow">

But I can't work out how to get the descriptions - I can easily import then with their Parent ID, but I actually need to import them as part of the Child records - so that I know which Descriptions the Child Stock Code should associate with.

Thanks

Kristen

Kristen
Test

22859 Posts

Posted - 2006-11-14 : 10:38:10

OK, plan B.

Anyone know a resource, forums preferably, where I might be able to get an answer?

All my Googling has only turned up relatively straightforward relationships, and I need some help forum someone who does this sort of thing for a living as I can't get my head around it, and there is no debugging tools of course - so you have to try-fail=try-fail ... and that takes WAY too long