Help! Crunch time here

I use OpenXML() to bulk load data into a table from C# 2003. It works
beautifully until yesterday. In order to run this successfully and
work with Parent to Child relationships, I add a column on each bulk
load called XmlId type Int.
Anyway, so now I have an error that states:
Msg 1714, Level 16, State 1, Line 1
Alter table failed because unique column IDs have been exhausted for
table 'promotionhistory'.
I only have 17 million records in the table and besides, I'm not adding
a new row..just a column.
So now this alter table statement fails:
alter table PromotionHistory add xmlId int
Someone please help!nnelson wrote:
> I use OpenXML() to bulk load data into a table from C# 2003. It works
> beautifully until yesterday. In order to run this successfully and
> work with Parent to Child relationships, I add a column on each bulk
> load called XmlId type Int.
> Anyway, so now I have an error that states:
> Msg 1714, Level 16, State 1, Line 1
> Alter table failed because unique column IDs have been exhausted for
> table 'promotionhistory'.
> I only have 17 million records in the table and besides, I'm not adding
> a new row..just a column.
> So now this alter table statement fails:
> alter table PromotionHistory add xmlId int
> Someone please help!
>
How many columns are in the table? You may have reached the SQL Server maximum.
Run this:
select max(b.colid)
from sysobjects as a
join syscolumns as b on a.id = b.id
where a.[name] = N'promotionhistory'
This will give you the maximum column ID on the table.

Help! Crunch time here

I use OpenXML() to bulk load data into a table from C# 2003. It works
beautifully until yesterday. In order to run this successfully and
work with Parent to Child relationships, I add a column on each bulk
load called XmlId type Int.
Anyway, so now I have an error that states:
Msg 1714, Level 16, State 1, Line 1
Alter table failed because unique column IDs have been exhausted for
table 'promotionhistory'.
I only have 17 million records in the table and besides, I'm not adding
a new row..just a column.
So now this alter table statement fails:
alter table PromotionHistory add xmlId int
Someone please help!nnelson wrote:
> I use OpenXML() to bulk load data into a table from C# 2003. It works
> beautifully until yesterday. In order to run this successfully and
> work with Parent to Child relationships, I add a column on each bulk
> load called XmlId type Int.
> Anyway, so now I have an error that states:
> Msg 1714, Level 16, State 1, Line 1
> Alter table failed because unique column IDs have been exhausted for
> table 'promotionhistory'.
> I only have 17 million records in the table and besides, I'm not adding
> a new row..just a column.
> So now this alter table statement fails:
> alter table PromotionHistory add xmlId int
> Someone please help!
>
How many columns are in the table? You may have reached the SQL Server maxi
mum.
Run this:
select max(b.colid)
from sysobjects as a
join syscolumns as b on a.id = b.id
where a.[name] = N'promotionhistory'
This will give you the maximum column ID on the table.

Help! - BCP / Bulk Copy

I need top update several tables with new values from an external text file. That would notrmally be a no brainer, (even for me) :) But, what is important this time around, is I can NOT fire the triggers, or every user would get notified of a 150,000 updates.

The text file contains 4 fields
KEYNUM|UpdateField1|UpdateField2|Class

Class will be either 1 or 2, if class = 1 then table 1 contains the record, if class = 2 then table 2 contains the record...

Any help, as always is greatly appreciatedHi,

By default BCP does not fire triggers, it will only fire triggers if you use the FIRE_TRIGGERS hint.

Does that change anything or help?|||Originally posted by bmalar
Hi,

By default BCP does not fire triggers, it will only fire triggers if you use the FIRE_TRIGGERS hint.

Does that change anything or help?

That Part I knew (but thanks). How can I use bcp to update selected records? (i.e. update records based on values in the KEYNUM field)|||Originally posted by GregCrossan
That Part I knew (but thanks). How can I use bcp to update selected records? (i.e. update records based on values in the KEYNUM field)

Sorry I didn't read your post correctly.. I thought you were inserting new records.

Help with XML Bulk Load

Hi All,
I am trying to load an XML file to SQL Server using SQLXML3.0 Bulk Load.
The XML has several child tags. Here is the sample XML.
<?xml version="1.0"?>
<batch>
<batchheader>
<merchant>Merchant Name</merchant>
<batchnumber>7735</batchnumber>
<date>2005-03-26</date>
<time>144407</time>
</batchheader>
<order>
<ordernumber>240420012591</ordernumber>
<orderdate>20050325</orderdate>
<POnumber>123456</POnumber>
</order>
<BillingAddress>
<Address1>123 Main Street</Address1>
<Address2>Apt 3</Address2>
<City>Los Angeles</City>
<State>CA</State>
<Zip>90007</Zip>
<BillingAddress>
</batch>
The Schema defined is:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="merchant" type="xsd:string" sql:field="Merchant">
</xsd:element>
<xsd:element name="batchnumber" type="xsd:string"
sql:field="BatchNumber">
</xsd:element>
<xsd:element name="date" type="xsd:string" sql:field="BatchDate">
</xsd:element>
<xsd:element name="time" type="xsd:string" sql:field="BatchTime">
</xsd:element>
<xsd:element name="batchheader">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="merchant"/>
<xsd:element ref="batchnumber"/>
<xsd:element ref="date"/>
<xsd:element ref="time"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="ordernumber" type="xsd:string"
sql:field="OrderNumber">
</xsd:element>
<xsd:element name="orderdate" type="xsd:string" sql:field="OrderDate">
</xsd:element>
<xsd:element name="POnumber" type="xsd:string" sql:field="PONumber">
</xsd:element>
<xsd:element name="order">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="ordernumber"/>
<xsd:element ref="orderdate"/>
<xsd:element ref="POnumber"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="Address1" type="xsd:string" sql:field="Address1">
</xsd:element>
<xsd:element name="Address2" type="xsd:string" sql:field="Address2">
</xsd:element>
<xsd:element name="City" type="xsd:string" sql:field="City">
</xsd:element>
<xsd:element name="State" type="xsd:string" sql:field="State">
</xsd:element>
<xsd:element name="Zip" type="xsd:string" sql:field="Zip">
</xsd:element>
<xsd:element name="BillingAddress">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="Address1"/>
<xsd:element ref="Address2"/>
<xsd:element ref="City"/>
<xsd:element ref="State"/>
<xsd:element ref="Zip"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="batch" sql:relation="xmlStaging">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="batchheader"/>
<xsd:element ref="order"/>
<xsd:element ref="BillingAddress"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
My problem is that I am loading ALL of the element in one table. I have a
table 'xmlStaging' already defined with all of the simple elements. But
when I load the above file, I get the following error:
*** Schema: relationship expected on 'batchheader' ***
If I use the sql:relation="xmlStaging" on <batchheader>, <order> and
<BillingAddress> separately, but not on the batch, I get two records
inserted into the table, that is, it treats <batchheader> as one row with
NULLS on <order> elements, and 2nd row has elements from <order> with NULLS on
<batchherader>, .. so for Billing Address
All I want is that my XML docuemnt above is inserted into only one row.
The above XML document will have multiple records, that is, it will be
something like...
<batch>
<batchheader>
<element... >
<element... >
<element... >
</batchheader>
<order>
<element... >
<element... >
<element... >
</order>
<BillingAddress>
<element... >
<element... >
<element... > ...
</BillingAddress>
<order>
<element... >
<element... >
<element... >
</order>
<BillingAddress>
<element... >
<element... >
<element... > ...
</BillingAddress>
<order>
<element... >
<element... >
<element... >
</order>
...
...
...
</batch>
Please help,
Thanks,
Aleem
Please try putting sql:is-constant="1" annotation on 'batchheader' element
declaration in XSD like this:
<xsd:element name="batch" sql:relation="xmlStaging">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="batchheader" sql:is-constant="1"/>
<xsd:element ref="order"/>
<xsd:element ref="BillingAddress"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
That should fix your problem. Apply the same technique to other elements if
you get similar errors.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aleem Rana" <arana@.aludra.usc.edu> wrote in message
news:Pine.GSO.4.33.0504121559290.13331-100000@.aludra.usc.edu...
> Hi All,
> I am trying to load an XML file to SQL Server using SQLXML3.0 Bulk Load.
> The XML has several child tags. Here is the sample XML.
>
> <?xml version="1.0"?>
> <batch>
> <batchheader>
> <merchant>Merchant Name</merchant>
> <batchnumber>7735</batchnumber>
> <date>2005-03-26</date>
> <time>144407</time>
> </batchheader>
> <order>
> <ordernumber>240420012591</ordernumber>
> <orderdate>20050325</orderdate>
> <POnumber>123456</POnumber>
> </order>
> <BillingAddress>
> <Address1>123 Main Street</Address1>
> <Address2>Apt 3</Address2>
> <City>Los Angeles</City>
> <State>CA</State>
> <Zip>90007</Zip>
> <BillingAddress>
> </batch>
>
> The Schema defined is:
>
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
>
> <xsd:element name="merchant" type="xsd:string" sql:field="Merchant">
> </xsd:element>
> <xsd:element name="batchnumber" type="xsd:string"
> sql:field="BatchNumber">
> </xsd:element>
> <xsd:element name="date" type="xsd:string" sql:field="BatchDate">
> </xsd:element>
> <xsd:element name="time" type="xsd:string" sql:field="BatchTime">
> </xsd:element>
>
> <xsd:element name="batchheader">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element ref="merchant"/>
> <xsd:element ref="batchnumber"/>
> <xsd:element ref="date"/>
> <xsd:element ref="time"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
>
> <xsd:element name="ordernumber" type="xsd:string"
> sql:field="OrderNumber">
> </xsd:element>
> <xsd:element name="orderdate" type="xsd:string" sql:field="OrderDate">
> </xsd:element>
> <xsd:element name="POnumber" type="xsd:string" sql:field="PONumber">
> </xsd:element>
> <xsd:element name="order">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element ref="ordernumber"/>
> <xsd:element ref="orderdate"/>
> <xsd:element ref="POnumber"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
>
>
> <xsd:element name="Address1" type="xsd:string" sql:field="Address1">
> </xsd:element>
> <xsd:element name="Address2" type="xsd:string" sql:field="Address2">
> </xsd:element>
> <xsd:element name="City" type="xsd:string" sql:field="City">
> </xsd:element>
> <xsd:element name="State" type="xsd:string" sql:field="State">
> </xsd:element>
> <xsd:element name="Zip" type="xsd:string" sql:field="Zip">
> </xsd:element>
>
> <xsd:element name="BillingAddress">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element ref="Address1"/>
> <xsd:element ref="Address2"/>
> <xsd:element ref="City"/>
> <xsd:element ref="State"/>
> <xsd:element ref="Zip"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
>
> <xsd:element name="batch" sql:relation="xmlStaging">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element ref="batchheader"/>
> <xsd:element ref="order"/>
> <xsd:element ref="BillingAddress"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
>
> My problem is that I am loading ALL of the element in one table. I have a
> table 'xmlStaging' already defined with all of the simple elements. But
> when I load the above file, I get the following error:
> *** Schema: relationship expected on 'batchheader' ***
> If I use the sql:relation="xmlStaging" on <batchheader>, <order> and
> <BillingAddress> separately, but not on the batch, I get two records
> inserted into the table, that is, it treats <batchheader> as one row with
> NULLS on <order> elements, and 2nd row has elements from <order> with
NULLS on
> <batchherader>, .. so for Billing Address
>
> All I want is that my XML docuemnt above is inserted into only one row.
> The above XML document will have multiple records, that is, it will be
> something like...
> <batch>
> <batchheader>
> <element... >
> <element... >
> <element... >
> </batchheader>
> <order>
> <element... >
> <element... >
> <element... >
> </order>
> <BillingAddress>
> <element... >
> <element... >
> <element... > ...
> </BillingAddress>
> <order>
> <element... >
> <element... >
> <element... >
> </order>
> <BillingAddress>
> <element... >
> <element... >
> <element... > ...
> </BillingAddress>
> <order>
> <element... >
> <element... >
> <element... >
> </order>
> ...
> ...
> ...
> </batch>
>
> Please help,
> Thanks,
> Aleem
>
>
>
|||Hi Bertan,
I tried what you suggested, but got the following error:
<Source>Microsoft OLE DB Provider for SQL
Server</Source><Description><![CDATA[Invalid object name 'batchheader'.]]>
...
Let me write down my complete XML and Schema.
XML:
<?xml version="1.0"?>
<batch>
<batchheader>
<merchant>Merchant Name</merchant>
<batchnumber>7735</batchnumber>
<date>2005-03-26</date>
<time>144407</time>
</batchheader>
<order>
<ordernumber>240420012591</ordernumber>
<orderdate>20050325</orderdate>
<POnumber>123456</POnumber>
</order>
</batch>
The Schema:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:simpleType name ="FiftyCharacterNonNullString">
<xsd:restriction base="xsd:string">
<xsd:minLength value="1"/>
<xsd:maxLength value="50"/>
</xsd:restriction>
</xsd:simpleType>
<xsd:element name="merchant" type="FiftyCharacterNonNullString"
sql:field="Merchant">
</xsd:element>
<xsd:element name="batchnumber" type="FiftyCharacterNonNullString"
sql:field="BatchNumber">
</xsd:element>
<xsd:element name="date" type="xsd:string" sql:field="BatchDate">
</xsd:element>
<xsd:element name="time" type="xsd:string" sql:field="BatchTime">
</xsd:element>
<xsd:element name="batchheader">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="merchant"/>
<xsd:element ref="batchnumber"/>
<xsd:element ref="date"/>
<xsd:element ref="time"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="ordernumber" type="FiftyCharacterNonNullString"
sql:field="OrderNumber">
</xsd:element>
<xsd:element name="orderdate" type="xsd:string" sql:field="OrderDate">
</xsd:element>
<xsd:element name="POnumber" type="FiftyCharacterNonNullString"
sql:field="PONumber">
</xsd:element>
<xsd:element name="order">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="ordernumber"/>
<xsd:element ref="orderdate"/>
<xsd:element ref="POnumber"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="batch" sql:relation="xmlStaging">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="batchheader" sql:is-constant="1"/>
<xsd:element ref="order" sql:is-constant="1"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
*****
I have validated the Schema using Stylus Studio XML and there are no
errors.
Can you please help why am I getting the error? My xml file has the
batchheader element in it, and I also have the annotation
sql:is-constant="1" as you suggested.
Please help,
Thanks a lot,
Aleem
On Wed, 13 Apr 2005, Bertan ARI [MSFT] wrote:

> Please try putting sql:is-constant="1" annotation on 'batchheader' element
> declaration in XSD like this:
> <xsd:element name="batch" sql:relation="xmlStaging">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element ref="batchheader" sql:is-constant="1"/>
> <xsd:element ref="order"/>
> <xsd:element ref="BillingAddress"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> That should fix your problem. Apply the same technique to other elements if
> you get similar errors.
> --
> Bertan ARI
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Aleem Rana" <arana@.aludra.usc.edu> wrote in message
> news:Pine.GSO.4.33.0504121559290.13331-100000@.aludra.usc.edu...
> NULLS on
>
>
|||I fixed one problem. The annotation sql:is-constant="1" needs to be
defined at both places, when declaring the element and when refering it to
as well. But now I have a new problem which I can not find a way to solve.
It may be my schema, but not sure. Here it is.
If my XML is something like,
<order>
<salestax>
<amount>1.23</amount>
<rate>8.25</rate>
</salestax>
<itemlist>
<item>
<name>DVD</name>
<price>12.00</price>
<partnumber>12345</partnumber>
</item>
<item>
<name>DVD</name>
<price>12.00</price>
<partnumber>12345</partnumber>
</item>
... more <item> </item>
</itemlist>
</order>
<order>
....
....
</order>
In my Schema, I have relation on <order .. sql:relation="xmlStaging">
and all other has <element ... sql:is-constant="1">
But when it comes to inserting Item in the table, it fails. The error I
get is that the ItemName column was already found; make sure that no two
columns has the same schema. What I need is when it finds a second Item,
it inserts in a new row.
Part of my Schema is below:
<xsd:element name="type" type="xsd:string" sql:field="Type">
</xsd:element>
<xsd:element name="sku" type="xsd:string" sql:field="SKU">
</xsd:element>
<xsd:element name="partnumber" type="xsd:string" sql:field="Number">
</xsd:element>
<xsd:element name="description" type="xsd:string"
sql:field="Description">
</xsd:element>
<xsd:element name="unitprice" type="xsd:string" sql:field="UnitPrice">
</xsd:element>
<xsd:element name="quantity" type="xsd:string" sql:field="Quantity">
</xsd:element>
<xsd:element name="item" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="type"/>
<xsd:element ref="sku"/>
<xsd:element ref="partnumber"/>
<xsd:element ref="description"/>
<xsd:element ref="unitprice"/>
<xsd:element ref="quantity"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="lineitemlist" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence minOccurs="0" maxOccurs="unbounded">
<xsd:element ref="item"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
......
......
......
<xsd:element name="order" sql:relation="xmlStaging">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="ordernumber"/>
<xsd:element ref="orderdate"/>
<xsd:element ref="POnumber"/>
<xsd:element ref="salestax" sql:is-constant="1"/>
<xsd:element ref="shippingandhandling" sql:is-constant="1"/>
<xsd:element ref="lineitemlist" sql:is-constant="1"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
Please help with the problem. Is there something in the schema I need to
change? How can I make sure that anytime it finds a new <item>, it should
go to a new row.
Any help would be really appreciated,
Thanks,
Aleem
On Wed, 13 Apr 2005, Aleem Rana wrote:

> Hi Bertan,
> I tried what you suggested, but got the following error:
> <Source>Microsoft OLE DB Provider for SQL
> Server</Source><Description><![CDATA[Invalid object name 'batchheader'.]]>
> ...
> Let me write down my complete XML and Schema.
> XML:
> <?xml version="1.0"?>
> <batch>
> <batchheader>
> <merchant>Merchant Name</merchant>
> <batchnumber>7735</batchnumber>
> <date>2005-03-26</date>
> <time>144407</time>
> </batchheader>
> <order>
> <ordernumber>240420012591</ordernumber>
> <orderdate>20050325</orderdate>
> <POnumber>123456</POnumber>
> </order>
> </batch>
>
> The Schema:
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
>
> <xsd:simpleType name ="FiftyCharacterNonNullString">
> <xsd:restriction base="xsd:string">
> <xsd:minLength value="1"/>
> <xsd:maxLength value="50"/>
> </xsd:restriction>
> </xsd:simpleType>
>
>
> <xsd:element name="merchant" type="FiftyCharacterNonNullString"
> sql:field="Merchant">
> </xsd:element>
> <xsd:element name="batchnumber" type="FiftyCharacterNonNullString"
> sql:field="BatchNumber">
> </xsd:element>
> <xsd:element name="date" type="xsd:string" sql:field="BatchDate">
> </xsd:element>
> <xsd:element name="time" type="xsd:string" sql:field="BatchTime">
> </xsd:element>
>
> <xsd:element name="batchheader">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element ref="merchant"/>
> <xsd:element ref="batchnumber"/>
> <xsd:element ref="date"/>
> <xsd:element ref="time"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
>
> <xsd:element name="ordernumber" type="FiftyCharacterNonNullString"
> sql:field="OrderNumber">
> </xsd:element>
> <xsd:element name="orderdate" type="xsd:string" sql:field="OrderDate">
> </xsd:element>
> <xsd:element name="POnumber" type="FiftyCharacterNonNullString"
> sql:field="PONumber">
> </xsd:element>
>
> <xsd:element name="order">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element ref="ordernumber"/>
> <xsd:element ref="orderdate"/>
> <xsd:element ref="POnumber"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
>
> <xsd:element name="batch" sql:relation="xmlStaging">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element ref="batchheader" sql:is-constant="1"/>
> <xsd:element ref="order" sql:is-constant="1"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
>
> *****
> I have validated the Schema using Stylus Studio XML and there are no
> errors.
> Can you please help why am I getting the error? My xml file has the
> batchheader element in it, and I also have the annotation
> sql:is-constant="1" as you suggested.
> Please help,
> Thanks a lot,
> Aleem
>
>
> On Wed, 13 Apr 2005, Bertan ARI [MSFT] wrote:
>
>
|||Unfortunately, you cannot do that. We do not support denormalized tables.
You need to create a second table and map the Items element to this second
table.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aleem Rana" <arana@.aludra.usc.edu> wrote in message
news:Pine.GSO.4.33.0504131838130.4478-100000@.aludra.usc.edu...[vbcol=seagreen]
> I fixed one problem. The annotation sql:is-constant="1" needs to be
> defined at both places, when declaring the element and when refering it to
> as well. But now I have a new problem which I can not find a way to solve.
> It may be my schema, but not sure. Here it is.
>
> If my XML is something like,
> <order>
> <salestax>
> <amount>1.23</amount>
> <rate>8.25</rate>
> </salestax>
> <itemlist>
> <item>
> <name>DVD</name>
> <price>12.00</price>
> <partnumber>12345</partnumber>
> </item>
> <item>
> <name>DVD</name>
> <price>12.00</price>
> <partnumber>12345</partnumber>
> </item>
> ... more <item> </item>
> </itemlist>
> </order>
> <order>
> ...
> ...
> </order>
>
> In my Schema, I have relation on <order .. sql:relation="xmlStaging">
> and all other has <element ... sql:is-constant="1">
> But when it comes to inserting Item in the table, it fails. The error I
> get is that the ItemName column was already found; make sure that no two
> columns has the same schema. What I need is when it finds a second Item,
> it inserts in a new row.
> Part of my Schema is below:
>
> <xsd:element name="type" type="xsd:string" sql:field="Type">
> </xsd:element>
> <xsd:element name="sku" type="xsd:string" sql:field="SKU">
> </xsd:element>
> <xsd:element name="partnumber" type="xsd:string" sql:field="Number">
> </xsd:element>
> <xsd:element name="description" type="xsd:string"
> sql:field="Description">
> </xsd:element>
> <xsd:element name="unitprice" type="xsd:string" sql:field="UnitPrice">
> </xsd:element>
> <xsd:element name="quantity" type="xsd:string" sql:field="Quantity">
> </xsd:element>
>
> <xsd:element name="item" sql:is-constant="1">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element ref="type"/>
> <xsd:element ref="sku"/>
> <xsd:element ref="partnumber"/>
> <xsd:element ref="description"/>
> <xsd:element ref="unitprice"/>
> <xsd:element ref="quantity"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
>
> <xsd:element name="lineitemlist" sql:is-constant="1">
> <xsd:complexType>
> <xsd:sequence minOccurs="0" maxOccurs="unbounded">
> <xsd:element ref="item"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
>
> .....
> .....
> .....
>
> <xsd:element name="order" sql:relation="xmlStaging">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element ref="ordernumber"/>
> <xsd:element ref="orderdate"/>
> <xsd:element ref="POnumber"/>
> <xsd:element ref="salestax" sql:is-constant="1"/>
> <xsd:element ref="shippingandhandling" sql:is-constant="1"/>
> <xsd:element ref="lineitemlist" sql:is-constant="1"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
>
> Please help with the problem. Is there something in the schema I need to
> change? How can I make sure that anytime it finds a new <item>, it should
> go to a new row.
>
> Any help would be really appreciated,
> Thanks,
> Aleem
>
>
> On Wed, 13 Apr 2005, Aleem Rana wrote:
'batchheader'.]]>[vbcol=seagreen]
element[vbcol=seagreen]
elements if[vbcol=seagreen]
rights.[vbcol=seagreen]
Load.[vbcol=seagreen]
sql:field="Merchant">[vbcol=seagreen]
sql:field="OrderDate">[vbcol=seagreen]
sql:field="PONumber">[vbcol=seagreen]
sql:field="Address1">[vbcol=seagreen]
sql:field="Address2">[vbcol=seagreen]
have a[vbcol=seagreen]
But[vbcol=seagreen]
with[vbcol=seagreen]
with[vbcol=seagreen]
row.[vbcol=seagreen]
be
>

Help with XML Bulk Load

Hi All,
I am trying to load an XML file to SQL Server using SQLXML3.0 Bulk Load.
The XML has several child tags. Here is the sample XML.
<?xml version="1.0"?>
<batch>
<batchheader>
<merchant>Merchant Name</merchant>
<batchnumber>7735</batchnumber>
<date>2005-03-26</date>
<time>144407</time>
</batchheader>
<order>
<ordernumber>240420012591</ordernumber>
<orderdate>20050325</orderdate>
<POnumber>123456</POnumber>
</order>
<BillingAddress>
<Address1>123 Main Street</Address1>
<Address2>Apt 3</Address2>
<City>Los Angeles</City>
<State>CA</State>
<Zip>90007</Zip>
<BillingAddress>
</batch>
The Schema defined is:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="merchant" type="xsd:string" sql:field="Merchant">
</xsd:element>
<xsd:element name="batchnumber" type="xsd:string"
sql:field="BatchNumber">
</xsd:element>
<xsd:element name="date" type="xsd:string" sql:field="BatchDate">
</xsd:element>
<xsd:element name="time" type="xsd:string" sql:field="BatchTime">
</xsd:element>
<xsd:element name="batchheader">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="merchant"/>
<xsd:element ref="batchnumber"/>
<xsd:element ref="date"/>
<xsd:element ref="time"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="ordernumber" type="xsd:string"
sql:field="OrderNumber">
</xsd:element>
<xsd:element name="orderdate" type="xsd:string" sql:field="OrderDate">
</xsd:element>
<xsd:element name="POnumber" type="xsd:string" sql:field="PONumber">
</xsd:element>
<xsd:element name="order">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="ordernumber"/>
<xsd:element ref="orderdate"/>
<xsd:element ref="POnumber"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="Address1" type="xsd:string" sql:field="Address1">
</xsd:element>
<xsd:element name="Address2" type="xsd:string" sql:field="Address2">
</xsd:element>
<xsd:element name="City" type="xsd:string" sql:field="City">
</xsd:element>
<xsd:element name="State" type="xsd:string" sql:field="State">
</xsd:element>
<xsd:element name="Zip" type="xsd:string" sql:field="Zip">
</xsd:element>
<xsd:element name="BillingAddress">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="Address1"/>
<xsd:element ref="Address2"/>
<xsd:element ref="City"/>
<xsd:element ref="State"/>
<xsd:element ref="Zip"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="batch" sql:relation="xmlStaging">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="batchheader"/>
<xsd:element ref="order"/>
<xsd:element ref="BillingAddress"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
My problem is that I am loading ALL of the element in one table. I have a
table 'xmlStaging' already defined with all of the simple elements. But
when I load the above file, I get the following error:
*** Schema: relationship expected on 'batchheader' ***
If I use the sql:relation="xmlStaging" on <batchheader>, <order> and
<BillingAddress> separately, but not on the batch, I get two records
inserted into the table, that is, it treats <batchheader> as one row with
NULLS on <order> elements, and 2nd row has elements from <order> with NULLS
on
<batchherader>, .. so for Billing Address
All I want is that my XML docuemnt above is inserted into only one row.
The above XML document will have multiple records, that is, it will be
something like...
<batch>
<batchheader>
<element... >
<element... >
<element... >
</batchheader>
<order>
<element... >
<element... >
<element... >
</order>
<BillingAddress>
<element... >
<element... >
<element... > ...
</BillingAddress>
<order>
<element... >
<element... >
<element... >
</order>
<BillingAddress>
<element... >
<element... >
<element... > ...
</BillingAddress>
<order>
<element... >
<element... >
<element... >
</order>
...
...
...
</batch>
Please help,
Thanks,
AleemPlease try putting sql:is-constant="1" annotation on 'batchheader' element
declaration in XSD like this:
<xsd:element name="batch" sql:relation="xmlStaging">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="batchheader" sql:is-constant="1"/>
<xsd:element ref="order"/>
<xsd:element ref="BillingAddress"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
That should fix your problem. Apply the same technique to other elements if
you get similar errors.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aleem Rana" <arana@.aludra.usc.edu> wrote in message
news:Pine.GSO.4.33.0504121559290.13331-100000@.aludra.usc.edu...
> Hi All,
> I am trying to load an XML file to SQL Server using SQLXML3.0 Bulk Load.
> The XML has several child tags. Here is the sample XML.
>
> <?xml version="1.0"?>
> <batch>
> <batchheader>
> <merchant>Merchant Name</merchant>
> <batchnumber>7735</batchnumber>
> <date>2005-03-26</date>
> <time>144407</time>
> </batchheader>
> <order>
> <ordernumber>240420012591</ordernumber>
> <orderdate>20050325</orderdate>
> <POnumber>123456</POnumber>
> </order>
> <BillingAddress>
> <Address1>123 Main Street</Address1>
> <Address2>Apt 3</Address2>
> <City>Los Angeles</City>
> <State>CA</State>
> <Zip>90007</Zip>
> <BillingAddress>
> </batch>
>
> The Schema defined is:
>
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
>
> <xsd:element name="merchant" type="xsd:string" sql:field="Merchant">
> </xsd:element>
> <xsd:element name="batchnumber" type="xsd:string"
> sql:field="BatchNumber">
> </xsd:element>
> <xsd:element name="date" type="xsd:string" sql:field="BatchDate">
> </xsd:element>
> <xsd:element name="time" type="xsd:string" sql:field="BatchTime">
> </xsd:element>
>
> <xsd:element name="batchheader">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element ref="merchant"/>
> <xsd:element ref="batchnumber"/>
> <xsd:element ref="date"/>
> <xsd:element ref="time"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
>
> <xsd:element name="ordernumber" type="xsd:string"
> sql:field="OrderNumber">
> </xsd:element>
> <xsd:element name="orderdate" type="xsd:string" sql:field="OrderDate">
> </xsd:element>
> <xsd:element name="POnumber" type="xsd:string" sql:field="PONumber">
> </xsd:element>
> <xsd:element name="order">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element ref="ordernumber"/>
> <xsd:element ref="orderdate"/>
> <xsd:element ref="POnumber"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
>
>
> <xsd:element name="Address1" type="xsd:string" sql:field="Address1">
> </xsd:element>
> <xsd:element name="Address2" type="xsd:string" sql:field="Address2">
> </xsd:element>
> <xsd:element name="City" type="xsd:string" sql:field="City">
> </xsd:element>
> <xsd:element name="State" type="xsd:string" sql:field="State">
> </xsd:element>
> <xsd:element name="Zip" type="xsd:string" sql:field="Zip">
> </xsd:element>
>
> <xsd:element name="BillingAddress">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element ref="Address1"/>
> <xsd:element ref="Address2"/>
> <xsd:element ref="City"/>
> <xsd:element ref="State"/>
> <xsd:element ref="Zip"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
>
> <xsd:element name="batch" sql:relation="xmlStaging">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element ref="batchheader"/>
> <xsd:element ref="order"/>
> <xsd:element ref="BillingAddress"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
>
> My problem is that I am loading ALL of the element in one table. I have a
> table 'xmlStaging' already defined with all of the simple elements. But
> when I load the above file, I get the following error:
> *** Schema: relationship expected on 'batchheader' ***
> If I use the sql:relation="xmlStaging" on <batchheader>, <order> and
> <BillingAddress> separately, but not on the batch, I get two records
> inserted into the table, that is, it treats <batchheader> as one row with
> NULLS on <order> elements, and 2nd row has elements from <order> with
NULLS on
> <batchherader>, .. so for Billing Address
>
> All I want is that my XML docuemnt above is inserted into only one row.
> The above XML document will have multiple records, that is, it will be
> something like...
> <batch>
> <batchheader>
> <element... >
> <element... >
> <element... >
> </batchheader>
> <order>
> <element... >
> <element... >
> <element... >
> </order>
> <BillingAddress>
> <element... >
> <element... >
> <element... > ...
> </BillingAddress>
> <order>
> <element... >
> <element... >
> <element... >
> </order>
> <BillingAddress>
> <element... >
> <element... >
> <element... > ...
> </BillingAddress>
> <order>
> <element... >
> <element... >
> <element... >
> </order>
> ...
> ...
> ...
> </batch>
>
> Please help,
> Thanks,
> Aleem
>
>
>|||Hi Bertan,
I tried what you suggested, but got the following error:
<Source>Microsoft OLE DB Provider for SQL
Server</Source><Description><![CDATA[Invalid object name 'batchheader'.]]>
...
Let me write down my complete XML and Schema.
XML:
<?xml version="1.0"?>
<batch>
<batchheader>
<merchant>Merchant Name</merchant>
<batchnumber>7735</batchnumber>
<date>2005-03-26</date>
<time>144407</time>
</batchheader>
<order>
<ordernumber>240420012591</ordernumber>
<orderdate>20050325</orderdate>
<POnumber>123456</POnumber>
</order>
</batch>
The Schema:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:simpleType name ="FiftyCharacterNonNullString">
<xsd:restriction base="xsd:string">
<xsd:minLength value="1"/>
<xsd:maxLength value="50"/>
</xsd:restriction>
</xsd:simpleType>
<xsd:element name="merchant" type="FiftyCharacterNonNullString"
sql:field="Merchant">
</xsd:element>
<xsd:element name="batchnumber" type="FiftyCharacterNonNullString"
sql:field="BatchNumber">
</xsd:element>
<xsd:element name="date" type="xsd:string" sql:field="BatchDate">
</xsd:element>
<xsd:element name="time" type="xsd:string" sql:field="BatchTime">
</xsd:element>
<xsd:element name="batchheader">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="merchant"/>
<xsd:element ref="batchnumber"/>
<xsd:element ref="date"/>
<xsd:element ref="time"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="ordernumber" type="FiftyCharacterNonNullString"
sql:field="OrderNumber">
</xsd:element>
<xsd:element name="orderdate" type="xsd:string" sql:field="OrderDate">
</xsd:element>
<xsd:element name="POnumber" type="FiftyCharacterNonNullString"
sql:field="PONumber">
</xsd:element>
<xsd:element name="order">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="ordernumber"/>
<xsd:element ref="orderdate"/>
<xsd:element ref="POnumber"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="batch" sql:relation="xmlStaging">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="batchheader" sql:is-constant="1"/>
<xsd:element ref="order" sql:is-constant="1"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
*****
I have validated the Schema using Stylus Studio XML and there are no
errors.
Can you please help why am I getting the error? My xml file has the
batchheader element in it, and I also have the annotation
sql:is-constant="1" as you suggested.
Please help,
Thanks a lot,
Aleem
On Wed, 13 Apr 2005, Bertan ARI [MSFT] wrote:

> Please try putting sql:is-constant="1" annotation on 'batchheader' element
> declaration in XSD like this:
> <xsd:element name="batch" sql:relation="xmlStaging">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element ref="batchheader" sql:is-constant="1"/>
> <xsd:element ref="order"/>
> <xsd:element ref="BillingAddress"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> That should fix your problem. Apply the same technique to other elements i
f
> you get similar errors.
> --
> Bertan ARI
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
> "Aleem Rana" <arana@.aludra.usc.edu> wrote in message
> news:Pine.GSO.4.33.0504121559290.13331-100000@.aludra.usc.edu...
> NULLS on
>
>|||I fixed one problem. The annotation sql:is-constant="1" needs to be
defined at both places, when declaring the element and when refering it to
as well. But now I have a new problem which I can not find a way to solve.
It may be my schema, but not sure. Here it is.
If my XML is something like,
<order>
<salestax>
<amount>1.23</amount>
<rate>8.25</rate>
</salestax>
<itemlist>
<item>
<name>DVD</name>
<price>12.00</price>
<partnumber>12345</partnumber>
</item>
<item>
<name>DVD</name>
<price>12.00</price>
<partnumber>12345</partnumber>
</item>
... more <item> </item>
</itemlist>
</order>
<order>
...
...
</order>
In my Schema, I have relation on <order .. sql:relation="xmlStaging">
and all other has <element ... sql:is-constant="1">
But when it comes to inserting Item in the table, it fails. The error I
get is that the ItemName column was already found; make sure that no two
columns has the same schema. What I need is when it finds a second Item,
it inserts in a new row.
Part of my Schema is below:
<xsd:element name="type" type="xsd:string" sql:field="Type">
</xsd:element>
<xsd:element name="sku" type="xsd:string" sql:field="SKU">
</xsd:element>
<xsd:element name="partnumber" type="xsd:string" sql:field="Number">
</xsd:element>
<xsd:element name="description" type="xsd:string"
sql:field="Description">
</xsd:element>
<xsd:element name="unitprice" type="xsd:string" sql:field="UnitPrice">
</xsd:element>
<xsd:element name="quantity" type="xsd:string" sql:field="Quantity">
</xsd:element>
<xsd:element name="item" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="type"/>
<xsd:element ref="sku"/>
<xsd:element ref="partnumber"/>
<xsd:element ref="description"/>
<xsd:element ref="unitprice"/>
<xsd:element ref="quantity"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="lineitemlist" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence minOccurs="0" maxOccurs="unbounded">
<xsd:element ref="item"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
.....
.....
.....
<xsd:element name="order" sql:relation="xmlStaging">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="ordernumber"/>
<xsd:element ref="orderdate"/>
<xsd:element ref="POnumber"/>
<xsd:element ref="salestax" sql:is-constant="1"/>
<xsd:element ref="shippingandhandling" sql:is-constant="1"/>
<xsd:element ref="lineitemlist" sql:is-constant="1"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
Please help with the problem. Is there something in the schema I need to
change? How can I make sure that anytime it finds a new <item>, it should
go to a new row.
Any help would be really appreciated,
Thanks,
Aleem
On Wed, 13 Apr 2005, Aleem Rana wrote:

> Hi Bertan,
> I tried what you suggested, but got the following error:
> <Source>Microsoft OLE DB Provider for SQL
> Server</Source><Description><![CDATA[Invalid object name 'batchheader'.]]>
> ...
> Let me write down my complete XML and Schema.
> XML:
> <?xml version="1.0"?>
> <batch>
> <batchheader>
> <merchant>Merchant Name</merchant>
> <batchnumber>7735</batchnumber>
> <date>2005-03-26</date>
> <time>144407</time>
> </batchheader>
> <order>
> <ordernumber>240420012591</ordernumber>
> <orderdate>20050325</orderdate>
> <POnumber>123456</POnumber>
> </order>
> </batch>
>
> The Schema:
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
>
> <xsd:simpleType name ="FiftyCharacterNonNullString">
> <xsd:restriction base="xsd:string">
> <xsd:minLength value="1"/>
> <xsd:maxLength value="50"/>
> </xsd:restriction>
> </xsd:simpleType>
>
>
> <xsd:element name="merchant" type="FiftyCharacterNonNullString"
> sql:field="Merchant">
> </xsd:element>
> <xsd:element name="batchnumber" type="FiftyCharacterNonNullString"
> sql:field="BatchNumber">
> </xsd:element>
> <xsd:element name="date" type="xsd:string" sql:field="BatchDate">
> </xsd:element>
> <xsd:element name="time" type="xsd:string" sql:field="BatchTime">
> </xsd:element>
>
> <xsd:element name="batchheader">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element ref="merchant"/>
> <xsd:element ref="batchnumber"/>
> <xsd:element ref="date"/>
> <xsd:element ref="time"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
>
> <xsd:element name="ordernumber" type="FiftyCharacterNonNullString"
> sql:field="OrderNumber">
> </xsd:element>
> <xsd:element name="orderdate" type="xsd:string" sql:field="OrderDate">
> </xsd:element>
> <xsd:element name="POnumber" type="FiftyCharacterNonNullString"
> sql:field="PONumber">
> </xsd:element>
>
> <xsd:element name="order">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element ref="ordernumber"/>
> <xsd:element ref="orderdate"/>
> <xsd:element ref="POnumber"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
>
> <xsd:element name="batch" sql:relation="xmlStaging">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element ref="batchheader" sql:is-constant="1"/>
> <xsd:element ref="order" sql:is-constant="1"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
>
> *****
> I have validated the Schema using Stylus Studio XML and there are no
> errors.
> Can you please help why am I getting the error? My xml file has the
> batchheader element in it, and I also have the annotation
> sql:is-constant="1" as you suggested.
> Please help,
> Thanks a lot,
> Aleem
>
>
> On Wed, 13 Apr 2005, Bertan ARI [MSFT] wrote:
>
>|||Unfortunately, you cannot do that. We do not support denormalized tables.
You need to create a second table and map the Items element to this second
table.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aleem Rana" <arana@.aludra.usc.edu> wrote in message
news:Pine.GSO.4.33.0504131838130.4478-100000@.aludra.usc.edu...
> I fixed one problem. The annotation sql:is-constant="1" needs to be
> defined at both places, when declaring the element and when refering it to
> as well. But now I have a new problem which I can not find a way to solve.
> It may be my schema, but not sure. Here it is.
>
> If my XML is something like,
> <order>
> <salestax>
> <amount>1.23</amount>
> <rate>8.25</rate>
> </salestax>
> <itemlist>
> <item>
> <name>DVD</name>
> <price>12.00</price>
> <partnumber>12345</partnumber>
> </item>
> <item>
> <name>DVD</name>
> <price>12.00</price>
> <partnumber>12345</partnumber>
> </item>
> ... more <item> </item>
> </itemlist>
> </order>
> <order>
> ...
> ...
> </order>
>
> In my Schema, I have relation on <order .. sql:relation="xmlStaging">
> and all other has <element ... sql:is-constant="1">
> But when it comes to inserting Item in the table, it fails. The error I
> get is that the ItemName column was already found; make sure that no two
> columns has the same schema. What I need is when it finds a second Item,
> it inserts in a new row.
> Part of my Schema is below:
>
> <xsd:element name="type" type="xsd:string" sql:field="Type">
> </xsd:element>
> <xsd:element name="sku" type="xsd:string" sql:field="SKU">
> </xsd:element>
> <xsd:element name="partnumber" type="xsd:string" sql:field="Number">
> </xsd:element>
> <xsd:element name="description" type="xsd:string"
> sql:field="Description">
> </xsd:element>
> <xsd:element name="unitprice" type="xsd:string" sql:field="UnitPrice">
> </xsd:element>
> <xsd:element name="quantity" type="xsd:string" sql:field="Quantity">
> </xsd:element>
>
> <xsd:element name="item" sql:is-constant="1">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element ref="type"/>
> <xsd:element ref="sku"/>
> <xsd:element ref="partnumber"/>
> <xsd:element ref="description"/>
> <xsd:element ref="unitprice"/>
> <xsd:element ref="quantity"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
>
> <xsd:element name="lineitemlist" sql:is-constant="1">
> <xsd:complexType>
> <xsd:sequence minOccurs="0" maxOccurs="unbounded">
> <xsd:element ref="item"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
>
> .....
> .....
> .....
>
> <xsd:element name="order" sql:relation="xmlStaging">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element ref="ordernumber"/>
> <xsd:element ref="orderdate"/>
> <xsd:element ref="POnumber"/>
> <xsd:element ref="salestax" sql:is-constant="1"/>
> <xsd:element ref="shippingandhandling" sql:is-constant="1"/>
> <xsd:element ref="lineitemlist" sql:is-constant="1"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
>
> Please help with the problem. Is there something in the schema I need to
> change? How can I make sure that anytime it finds a new <item>, it should
> go to a new row.
>
> Any help would be really appreciated,
> Thanks,
> Aleem
>
>
> On Wed, 13 Apr 2005, Aleem Rana wrote:
>
'batchheader'.]]>
element
elements if
rights.
Load.
sql:field="Merchant">
sql:field="OrderDate">
sql:field="PONumber">
sql:field="Address1">
sql:field="Address2">
have a
But
with
with
row.
be
>