Help! Two-Part SQL Update

I have a child table with semicolon-delimited data in a single
column. Based on what is in the first part of the semicolon-delimited
data, I need to write a value to a column in the parent table and
then remove that first part of the child-table column. I guess I want
to do a
two-part SQL Update, but am not sure where to begin. The tables
look like this:
Create Table #ParentInfo ( KeyCode VarChar(24) , Status VarChar(64)
)
Insert Into #ParentInfo( KeyCode )
Values( 'E1B296FCSYSTEM' )
Insert Into #ParentInfo( KeyCode )
Values( '85829EDESYSTEM' )
Insert Into #ParentInfo( KeyCode )
Values( 'A5CB9CF5SYSTEM' )
Insert Into #ParentInfo( KeyCode )
Values( '4CCF9C15SYSTEM' )
Insert Into #ParentInfo( KeyCode )
Values( '40B5DB72SYSTEM' )
Create Table #ChildInfo ( KeyCode VarChar(24) , Comments
VarChar(900) )
Insert Into #ChildInfo( KeyCode , Comments )
Values( 'E1B296FCSYSTEM','Demo Import ; InState:NY;Demo
Comments:')
Insert Into #ChildInfo( KeyCode , Comments )
Values( '85829EDESYSTEM','Demo Import ; InState:NY;Demo
Comments:')
Insert Into #ChildInfo( KeyCode , Comments )
Values( 'A5CB9CF5SYSTEM','Demo Import ; InState:NY;Demo
Comments:')
Insert Into #ChildInfo( KeyCode , Comments )
Values( '4CCF9C15SYSTEM','Demo Import ; InState:NY;Demo
Comments:')
Insert Into #ChildInfo( KeyCode , Comments )
Values( '40B5DB72SYSTEM','Demo Import ; InState:NY;Demo
Comments:')
So for "E1B296FCSYSTEM" in #ParentInfo, I need to write "Imported"
in the Status column if "Demo Import" is in #ChildInfo.Comments, and
then remove
"Demo Import" from row "E1B296FCSYSTEM" in #ChilInfo.
Thanks.something like this should do:
begin tran
update p
set status = case when c.comments like 'Demo Import%' then 'Imported' else
status end
from #ParentInfo p, #ChildInfo c
where p.KeyCode=c.KeyCode
and p.KeyCode='E1B296FCSYSTEM'
if @.@.error<>0 rollback tran
update #ChildInfo
set comments = replace(comments,'Demo Import','')
where KeyCode='E1B296FCSYSTEM'
if @.@.error=0 commit tran
else rollback tran
-oj
"xenophon" <xenophon@.online.nospam> wrote in message
news:vfp0a1t3mn9r022b5r96h79srejao9ec05@.
4ax.com...
>
> I have a child table with semicolon-delimited data in a single
> column. Based on what is in the first part of the semicolon-delimited
> data, I need to write a value to a column in the parent table and
> then remove that first part of the child-table column. I guess I want
> to do a
> two-part SQL Update, but am not sure where to begin. The tables
> look like this:
>
> Create Table #ParentInfo ( KeyCode VarChar(24) , Status VarChar(64)
> )
> Insert Into #ParentInfo( KeyCode )
> Values( 'E1B296FCSYSTEM' )
> Insert Into #ParentInfo( KeyCode )
> Values( '85829EDESYSTEM' )
> Insert Into #ParentInfo( KeyCode )
> Values( 'A5CB9CF5SYSTEM' )
> Insert Into #ParentInfo( KeyCode )
> Values( '4CCF9C15SYSTEM' )
> Insert Into #ParentInfo( KeyCode )
> Values( '40B5DB72SYSTEM' )
> Create Table #ChildInfo ( KeyCode VarChar(24) , Comments
> VarChar(900) )
> Insert Into #ChildInfo( KeyCode , Comments )
> Values( 'E1B296FCSYSTEM','Demo Import ; InState:NY;Demo
> Comments:')
> Insert Into #ChildInfo( KeyCode , Comments )
> Values( '85829EDESYSTEM','Demo Import ; InState:NY;Demo
> Comments:')
> Insert Into #ChildInfo( KeyCode , Comments )
> Values( 'A5CB9CF5SYSTEM','Demo Import ; InState:NY;Demo
> Comments:')
> Insert Into #ChildInfo( KeyCode , Comments )
> Values( '4CCF9C15SYSTEM','Demo Import ; InState:NY;Demo
> Comments:')
> Insert Into #ChildInfo( KeyCode , Comments )
> Values( '40B5DB72SYSTEM','Demo Import ; InState:NY;Demo
> Comments:')
>
> So for "E1B296FCSYSTEM" in #ParentInfo, I need to write "Imported"
> in the Status column if "Demo Import" is in #ChildInfo.Comments, and
> then remove
> "Demo Import" from row "E1B296FCSYSTEM" in #ChilInfo.
> Thanks.
>
>
>|||I hope those are not the real names. A data element can be a code, but
it is *used* as a key, so key_code is nonsense. You NEVER name a data
element for how it is used in the physical schema; you name it for what
it is in the data model.
ParentInfo is also weird -- singlular so we have only one parent and
are there tables that do not store information? Likewise, a name like
status does not tell us "status of what?" when we read it.
Try something like this and avoid dangerous proprietary UPDATE ..
FROM.. syntax.
BEGIN
UPDATE Parents
SET foobar_status
= 'Imported'
WHERE EXISTS
(SELECT *
FROM Children AS C
WHERE Partents.foobar_id = C.foobar_id
AND comments LIKE 'Demo Import ; ' + '%');
UPDATE Children
SET comments = REPLACE(comments, 'Demo Import ; ','');
END;
While we have no DDL or specs, this scares me. You are in violation of
First Normal Form and it look like you are physically moving data from
table to table. That is how we did it with punch cards in the
1950's. You might want to talk to someone with RDBMS design
experience.|||1.
These are not real names. All names were changed and stripped
down to protect the guilty. :)
2.
The data was originally a mess, and what I was looking for was
a streamlined way to clean it up - this is a large multi-multi-
step process.
So there it is.
Thanks.
On 3 Jun 2005 11:59:46 -0700, "--CELKO--" <jcelko212@.earthlink.net>
wrote:

>I hope those are not the real names. A data element can be a code, but
>it is *used* as a key, so key_code is nonsense. You NEVER name a data
>element for how it is used in the physical schema; you name it for what
>it is in the data model.
[snip]

HELP! More deleted records!

I have a replicated database and hundreds of records keep "disappearing" from
one table in particular. The table is a child table storing the answers to a
number of questions. Also;
1. The tables have referential integrity enforced, with cascade delete.
2. The relationships are enforced for replication.
3. There are no stored procedures which delete records from the offending
table.
4. There is no way of manually deleting records directly from the offending
table using the front-end application.
5. The parent tables have exactly the same records in as previously.
6. There are no replication conflicts.
Any ideas?
I am keeping transaction logs for 3 weeks. Is there any way that I can find
out from the transaction logs when the records were deleted? Is there any way
I can find out why the records would have been deleted?
Thanks,
Nigel Taylor
http://www.tinit.co.uk/
Nigel,
you don't say if this is on the publisher or on the subscriber, but I'm
guessing the latter. It could be that some updates are replicated as a
delete-insert pair for transactional replication in ceertain cases (see
http://support.microsoft.com/default...NoWebContent=1).
Perhaps this is your case?
Also I'm not clear why you're using cascading constraints for the subscriber
which is normally treated as RO - or maybe I have misunderstood?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi,
It's an ftp merge replication. I'm not sure where the records are
disappearing from first but the publisher is definately causing the other
subscribers (of which there are about 12) to lose the records too.
The relationships (i.e. cascade delete and enforce for replication) were
automatically created when the subscription was first created from the
publisher.
With the delete-insert pair the records would be recreated wouldn't they?
Thanks,
Nigel Taylor
http://www.tinit.co.uk/
|||In fact, when I use your spBrowseMergeChanges procedure on the offending
table, there are a load more records marked for deletion and without a
replacement!
Nigel Taylor
http://www.tinit.co.uk/
|||Nigel,
as you're using merge, and assuming you have all the related tables being
replicated, I'd modify all relationships to be not enforced for replication.
The delete-insert pairs are a problem for transactional and not merge, but
with merge there are other reasons the FK constraints are not applied (see
http://www.replicationanswers.com/Me...derArticle.asp). This
will simplify the setup somewhat and hopefully allow us to see if this is
the cause of the problems.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Okay, thanks Paul. I've removed the 'enforce relationship for replication'
flag and removed the cascade deletes on the offending tables. Hopefully it'll
work and I can get back to doing something else.
Cheers,
Nigel Taylor
http://www.tinit.co.uk/

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
>

Help with Update from Child table

I have two tables tblInvoice and tblInvoice Line.
Both tables have a column InvoiceID that is Primary / Foreign Key .
tblInvoice has a column TransType char(2) 'SI' or 'SC'
I have now added this column to tblInvoice and want to update the
values with the ones from tblInvoiceLine where the InvoiceID matches
before deleting the column from tblInvoiceLine.
tblInvoiceLine has many records for each invoice but all are the same
transaction type so any will do.
Can sonmeone show how to write this update?
Thanks
Hals_leftThis script should do the work.
update tblInvoice
set TransType = b.TransType
from tblInvoice a join
(select distinct InvoiceID, TransType from tblInvoiceLine) b
on a.invoiceid = b.invoiceid
"hals_left" wrote:

> I have two tables tblInvoice and tblInvoice Line.
> Both tables have a column InvoiceID that is Primary / Foreign Key .
> tblInvoice has a column TransType char(2) 'SI' or 'SC'
> I have now added this column to tblInvoice and want to update the
> values with the ones from tblInvoiceLine where the InvoiceID matches
> before deleting the column from tblInvoiceLine.
> tblInvoiceLine has many records for each invoice but all are the same
> transaction type so any will do.
> Can sonmeone show how to write this update?
> Thanks
> Hals_left
>|||On 11 Nov 2005 06:53:57 -0800, hals_left wrote:

>I have two tables tblInvoice and tblInvoice Line.
>Both tables have a column InvoiceID that is Primary / Foreign Key .
>tblInvoice has a column TransType char(2) 'SI' or 'SC'
>I have now added this column to tblInvoice and want to update the
>values with the ones from tblInvoiceLine where the InvoiceID matches
>before deleting the column from tblInvoiceLine.
>tblInvoiceLine has many records for each invoice but all are the same
>transaction type so any will do.
>Can sonmeone show how to write this update?
>Thanks
>Hals_left
Hi Hals_left,
Absar already posted a solution, but beware - if for some Invoice, the
Invoice lines do have different trnasaction type, it will just pick one
at random, without giving an error or even a warning.
The version below will fail (with an error message) if there are
invoices with more than one transactiontype, allowing you to check and
fix the problem before proceeding:
UPDATE Invoice
SET TransType = (SELECT b.TransType
FROM InvoiceLine AS b
WHERE b.InvoiceID = Invoice.InvoiceID)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it. If you were polite would we have posted something like:
CREATE TABLE Invoices -- assumes ther is more than one!!
(inv_nbr INTEGER NOT NULL PRIMARY KEY,
trans_type CHAR(2) NOT NULL
CHECK (trans_type IN ('SI', 'SC')),
.);
Never put that silly redundant "tbl-" prefix on names. You did not
know that you sell items and NOT the PHYSICAL lines on an order form.
Newbies often confuse the PAPER FORMS with a logical data elementr like
that.
CREATE TABLE InvoiceDetails
(inv_nbr INTEGER NOT NULL
REFERENCES Invoices(inv_nbr)
ON DELETE CASCADE
ON UPDATE CASCADE,
upc CHAR(13) NOT NULL
REFERENCES Inventory(upc)
ON UPDATE CASCADE,
item_qty INTEGER NOT NULL
CHECK (item_qty > 0),
.);
So I have an order with two details; one is 'SI' and one is 'SC';
which one goes into the Invoices table? Do not assume that all details
will have the trans_type.
Based on 20+ years of writing SQL standards and cleaning up bad design,
I think your DDL is screwed up beyond repair. You need a full review
and rules for scrubbing the data.