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
>