RE: SQLXMLBulkLoad and Keys
- From: "Trillium" <Trillium@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 30 Dec 2005 17:24:03 -0800
In the XML, Sales_Price is a child of Sales_Price_Key_Info, which is in turn
a child of Sales_Prices, and this hierarchy is not represented in the schema.
For me it is easier to see it with all the indentations:
<PAW_Item xsi:type="paw:item">
<ID xsi:type="paw:id">#114-11</ID>
<Description>14 GAUGE BLACK WIRE</Description>
<Description_for_Sales>14 GAUGE BLACK WIRE</Description_for_Sales>
<Sales_Prices>
<Sales_Price_Info Key="1">
<Sales_Price>0.14000</Sales_Price>
</Sales_Price_Info>
</Sales_Prices>
<Type>70</Type>
</PAW_Item>
So it looks like there would/could be multiple Sales_Prices per item - which
would imply a second table with a foreign key on the temp_Inventory ID. To
get that, the prices part of the xsd would have to include the 2 other
levels, more like this - which does not include the annotations:
<xsd:element name="Sales_Prices" sql:is-constant="1" />
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Sales_Price_Info"
sql:relation="temp_Some_Table_Name">
<xsd:annotation><!-- xsd:appinfo keys relationship to the id info
would be here--></xsd:annotation>
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Sales_Price" type="xsd:float"
sql:field="PriceLevel1" />
</xsd:sequence>
<xsd:attribute name="Key" type="xsd:integer" sql:field="Key"
sql:datatype="int" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
But it looks like you are trying to put it all into one table - maybe with
different column names for each Key? I have done that in past by creating
the two tables for the bulkload, then updating the separate "many" columns
with update queries...
"somuch" wrote:
> I've created a DTS package to bulk import an XML file into SQL Server
> using SQLXMLBulkLoad.SQLXMLBulkLoad.3.0. The first 4 fields (ItemID,
> ItemDescription, DescriptionforSales, ItemType) import but PriceLevel1
> doesn't and is just set to null. I admit I don't know much about XML
> and this is my first attempt at importing it. I know the problem lies
> in the XSD file having something to do with the Sales_Price_Info key
> but can't figure out how to go about it. Any help would be appreciated.
>
> thanks,
>
>
> Below is part of the XML file
> -----------------------------
>
> <PAW_Item xsi:type="paw:item">
> <ID xsi:type="paw:id">#114-11</ID>
> <Description>14 GAUGE BLACK WIRE</Description>
> <Description_for_Sales>14 GAUGE BLACK WIRE</Description_for_Sales>
> <Sales_Prices>
> <Sales_Price_Info Key="1">
> <Sales_Price>0.14000</Sales_Price>
> </Sales_Price_Info>
> </Sales_Prices> <Type>70</Type>
> </PAW_Item>
>
>
> Below is the XSD File
> ---------------------
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema";
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="PAW_Item" sql:relation="temp_Inventory" >
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="ID" type="xsd:string" sql:field="ItemID" />
> <xsd:element name="Description" type="xsd:string"
> sql:field="ItemDescription" />
> <xsd:element name="Description_for_Sales" type="xsd:string"
> sql:field="DescriptionforSales" />
> <xsd:element name="Type" type="xsd:string" sql:field="ItemType" />
> <xsd:element name="Sales_Price" type="xsd:float"
> sql:field="PriceLevel1" />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
>
>
.
- References:
- SQLXMLBulkLoad and Keys
- From: somuch
- SQLXMLBulkLoad and Keys
- Prev by Date: Re: DataSet Xml DateTime incompatible with Sql 2000
- Next by Date: Re: DataSet Xml DateTime incompatible with Sql 2000
- Previous by thread: SQLXMLBulkLoad and Keys
- Next by thread: Quick FOR XML EXPLICIT question - newbie
- Index(es):
Relevant Pages
|
