Under the CAL user licensing model, does each user that has a login to a SQL
Server 2005 database need a CAL, or is one needed for each simultaneous
login? For instance if I have 100 users with access to a SQL database, but
at any given time there are only 25 users accessing a database, do I need 10
0
user CAL's or 25 user CAL's?Wade Bart wrote:
> Under the CAL user licensing model, does each user that has a login to a S
QL
> Server 2005 database need a CAL, or is one needed for each simultaneous
> login? For instance if I have 100 users with access to a SQL database, bu
t
> at any given time there are only 25 users accessing a database, do I need
100
> user CAL's or 25 user CAL's?
It is per user or device, not per concurrent connection.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Showing posts with label model. Show all posts
Showing posts with label model. Show all posts
Sunday, March 11, 2012
CAL License Question
Under the CAL user licensing model, does each user that has a login to a SQL
Server 2005 database need a CAL, or is one needed for each simultaneous
login? For instance if I have 100 users with access to a SQL database, but
at any given time there are only 25 users accessing a database, do I need 100
user CAL's or 25 user CAL's?Wade Bart wrote:
> Under the CAL user licensing model, does each user that has a login to a SQL
> Server 2005 database need a CAL, or is one needed for each simultaneous
> login? For instance if I have 100 users with access to a SQL database, but
> at any given time there are only 25 users accessing a database, do I need 100
> user CAL's or 25 user CAL's?
It is per user or device, not per concurrent connection.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Server 2005 database need a CAL, or is one needed for each simultaneous
login? For instance if I have 100 users with access to a SQL database, but
at any given time there are only 25 users accessing a database, do I need 100
user CAL's or 25 user CAL's?Wade Bart wrote:
> Under the CAL user licensing model, does each user that has a login to a SQL
> Server 2005 database need a CAL, or is one needed for each simultaneous
> login? For instance if I have 100 users with access to a SQL database, but
> at any given time there are only 25 users accessing a database, do I need 100
> user CAL's or 25 user CAL's?
It is per user or device, not per concurrent connection.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Cacluated Field
I am designing an object model and DB and I can't decide where to put
calculated fields... Should it be in the database or the middle-tier?
calculated fields... Should it be in the database or the middle-tier?
In other words, if I have an OrderItem on an Order and there are two
columns called "Quantity" and "Cost" I also will want "TotalCost" =
"Quantity x "Cost"... Do I have the "GET" stored proc return this
caculcated value or just create the property in my middle-tier and have
it calculate it there?
Opinions anyone?<josh@.nautilusnet.com> wrote in message
news:1111427709.765197.314850@.f14g2000cwb.googlegr oups.com...
>I am designing an object model and DB and I can't decide where to put
> calculated fields... Should it be in the database or the middle-tier?
> In other words, if I have an OrderItem on an Order and there are two
> columns called "Quantity" and "Cost" I also will want "TotalCost" =
> "Quantity x "Cost"... Do I have the "GET" stored proc return this
> caculcated value or just create the property in my middle-tier and have
> it calculate it there?
> Opinions anyone?
For such a simple calculation, I would do it in either the stored procedure
or perhaps a view - the view is useful if you want to have the value
available to other clients which may not always call your proc, and for ad
hoc queries. You could use a computed column as well, but I haven't used
them much, so I don't really know how they perform with large data sets.
More complex calculations may be better in the middle tier, especially if
you only calculate for relatively small data sets, and if you need to use
mathematical functions which aren't available in TSQL, then you may have no
choice. As in many cases, the best way to get a good answer is to test it
yourself with your own data and see which solution works out better for you.
Simon|||josh@.nautilusnet.com wrote:
> I am designing an object model and DB and I can't decide where to put
> calculated fields... Should it be in the database or the middle-tier?
> In other words, if I have an OrderItem on an Order and there are two
> columns called "Quantity" and "Cost" I also will want "TotalCost" =
> "Quantity x "Cost"... Do I have the "GET" stored proc return this
> caculcated value or just create the property in my middle-tier and have
> it calculate it there?
> Opinions anyone?
First off, I would highly suggest that you have all of these calculated
fields defined in some sort of data dictionary so that you can use a code
generator of some sort to generate it. This lets you change your mind
about implementation details after the fact.
Calculated fields can come down to these types:
1) EXTEND, most common, extended = price * qty
2) FETCH, pull price from items table into orders, trigger action is change
of value of order_detail.item_code.
3) AGGREGATE, any sum, avg, min, max or count() from detail to header
4) DISTRIBUTE, like a fetch, in that a value goes from header to detail,
but triggering action is a change in value in header, and it is pushed to
*all* rows in child table that match on pk/fk. Included for completeness
but considered evil.
All approaches boil down to either materializing in the tables, or not doing
it.
The simplest approach if you don't put them into tables is to create views.
I've done this with a view generator and it is pretty nifty. The danger is
that the very simplicity of the views will obscure very deeply nested
subqueries, which may not be discovered until the system comes under heavy
load.
The other option is to materialize them into the tables. This is considered
evil by relational theorists, but the only real requirement if you do this
is that you not let a casual user update the automated columns. So a
straight command "UPDATE ... SET TotalCost=5 " should fail with an error.
--
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth@.(Sec)ure(Dat)a(.com)|||I am just now getting back to this thread. In my opinion the problem
with putting calculations into the the sproc is that it's not possible
to no the calculation until call the sproc again. This feels unnatural
when working with an object model, for example:
OrderItem item = new OrderItem();
item.Quantity = 5;
item.Cost = 10.00;
Response.write(item.Total);
For the above code to work using the "calculations in sproc" method I
would have to hit the database again for item.Total to have a value...|||If you want to see the calculated total immediately, then a view is
probably better than a procedure:
create view dbo.OrdersWithTotalCost
as
select
OrderID,
OrderItemID,
...
/* Other columns from Orders */
...
Quantity,
Cost,
Quantity * Cost as 'Total'
from
dbo.Orders
I'm not sure I understand your concern about hitting the database again
- since your calculation is so simple, you will know the value of Total
before you even INSERT the new order item, and you may not need to
retrieve it again (unless there's further processing in the database,
of course).
If you really want to avoid another query, then one option is to create
an InsertOrderItem stored procedure, which INSERTs the new item and
then returns the total as an output parameter.
Simon|||Simon Hayes wrote:
> If you want to see the calculated total immediately, then a view is
> probably better than a procedure:
> create view dbo.OrdersWithTotalCost
> as
> select
> OrderID,
> OrderItemID,
> ...
> /* Other columns from Orders */
> ...
> Quantity,
> Cost,
> Quantity * Cost as 'Total'
> from
> dbo.Orders
> I'm not sure I understand your concern about hitting the database again
> - since your calculation is so simple, you will know the value of Total
> before you even INSERT the new order item, and you may not need to
> retrieve it again (unless there's further processing in the database,
> of course).
> If you really want to avoid another query, then one option is to create
> an InsertOrderItem stored procedure, which INSERTs the new item and
> then returns the total as an output parameter.
My original suggestion held that the definitions should be stored in a data
dictionary and any implemention, views or sprocs, should be generated from
that.
If you do that, the client (some OO code) can read the dictionary, or you
can generate code for classes, and they can do their own calculations on
the fly for user convenience. You then can independently decide how to
implement the same formulas on the server.
If the implementation is based on a dd, you can try different methods and
change your mind rather painlessly.
--
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth@.(Sec)ure(Dat)a(.com)
Thursday, March 8, 2012
Caching in TimeSeries
Hi,
I have prepared a time series model. The model works well with few cases. Answering time raises extremly after processing the model with all cases.
In a book I read sth. about a cahing possibilty. But how can this be defined in Visual Studio ?
Thanks in advance 
Achim
Since the time series algorithm does not do predictions on new input data (it uses the historical data that the model was trained with), it returns the same result each time you request a prediction for the next N steps, for a given N. You can take advantage of this by getting the results for the maximum number of steps you're likely to request in one query, saving the results on the client (in an in-memory list or on disk) and then using the saved results for future requests against that model instead of sending the prediction query to the server. You would only need to refresh the cached results if the model gets reprocessed with new data.|||
The algorithm has a certain built in mechanism for caching. For instance, if you ask for the next 50 forecasted values, the first query will be relatively slow. Subsequent queries for less than 50 values should be much faster. However, asking for the first time for 51 values will also take a longer time.
Friday, February 24, 2012
C# UDF project call C++ model (SQL Server 2005)?
I have some legacy C++ code and I am creating a C# project for UDF function
and another project for C++ classes. I always got error message when I am
trying to add reference to the class lib project:
A reference to 'classModel' could not be added. SQL Server projects can
reference only other SQL Server projects.
I tried to create the C++ project as SQL Server project too and the error
message is the same.examnotes <nick@.discussions.microsoft.com> wrote in
news:E39C7050-FC85-425C-91B3-B76040D2B164@.microsoft.com:
> I have some legacy C++ code and I am creating a C# project for UDF
> function and another project for C++ classes. I always got error
> message when I am trying to add reference to the class lib project:
> A reference to 'classModel' could not be added. SQL Server projects
> can reference only other SQL Server projects.
That is because the VS SQL Server Project doesn't allow you to reference
any other project types (or assemblies already defined in the database).
You can instead use my project type for this:
http://staff.develop.com/nielsb/Per...b8d3-4ace-a54e-
26411f9eac09.aspx (watch out for linebreaks). However, in this scenario
I wonder if that is the real problem, see below.
> I tried to create the C++ project as SQL Server project too and the
> error message is the same.
OK, so is the C++ project managed code? If not you can not use it inside
SQL Server. In that case you have to either do COM interop against the
C++ classes, or P/Invoke.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb at develop dot com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||IC, thanks.
I avoid to create the UDF in C++(Managed) because not much example, support
information about C++ user defined function programming. And I am not famila
r
with managed C++ syntax.
"Niels Berglund" wrote:
> examnotes <nick@.discussions.microsoft.com> wrote in
> news:E39C7050-FC85-425C-91B3-B76040D2B164@.microsoft.com:
>
> That is because the VS SQL Server Project doesn't allow you to reference
> any other project types (or assemblies already defined in the database).
> You can instead use my project type for this:
> http://staff.develop.com/nielsb/Per...b8d3-4ace-a54e-
> 26411f9eac09.aspx (watch out for linebreaks). However, in this scenario
> I wonder if that is the real problem, see below.
>
> OK, so is the C++ project managed code? If not you can not use it inside
> SQL Server. In that case you have to either do COM interop against the
> C++ classes, or P/Invoke.
> Niels
>
> --
> ****************************************
**********
> * Niels Berglund
> * http://staff.develop.com/nielsb
> * nielsb at develop dot com
> * "A First Look at SQL Server 2005 for Developers"
> * http://www.awprofessional.com/title/0321180593
> ****************************************
**********
>
and another project for C++ classes. I always got error message when I am
trying to add reference to the class lib project:
A reference to 'classModel' could not be added. SQL Server projects can
reference only other SQL Server projects.
I tried to create the C++ project as SQL Server project too and the error
message is the same.examnotes <nick@.discussions.microsoft.com> wrote in
news:E39C7050-FC85-425C-91B3-B76040D2B164@.microsoft.com:
> I have some legacy C++ code and I am creating a C# project for UDF
> function and another project for C++ classes. I always got error
> message when I am trying to add reference to the class lib project:
> A reference to 'classModel' could not be added. SQL Server projects
> can reference only other SQL Server projects.
That is because the VS SQL Server Project doesn't allow you to reference
any other project types (or assemblies already defined in the database).
You can instead use my project type for this:
http://staff.develop.com/nielsb/Per...b8d3-4ace-a54e-
26411f9eac09.aspx (watch out for linebreaks). However, in this scenario
I wonder if that is the real problem, see below.
> I tried to create the C++ project as SQL Server project too and the
> error message is the same.
OK, so is the C++ project managed code? If not you can not use it inside
SQL Server. In that case you have to either do COM interop against the
C++ classes, or P/Invoke.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb at develop dot com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||IC, thanks.
I avoid to create the UDF in C++(Managed) because not much example, support
information about C++ user defined function programming. And I am not famila
r
with managed C++ syntax.
"Niels Berglund" wrote:
> examnotes <nick@.discussions.microsoft.com> wrote in
> news:E39C7050-FC85-425C-91B3-B76040D2B164@.microsoft.com:
>
> That is because the VS SQL Server Project doesn't allow you to reference
> any other project types (or assemblies already defined in the database).
> You can instead use my project type for this:
> http://staff.develop.com/nielsb/Per...b8d3-4ace-a54e-
> 26411f9eac09.aspx (watch out for linebreaks). However, in this scenario
> I wonder if that is the real problem, see below.
>
> OK, so is the C++ project managed code? If not you can not use it inside
> SQL Server. In that case you have to either do COM interop against the
> C++ classes, or P/Invoke.
> Niels
>
> --
> ****************************************
**********
> * Niels Berglund
> * http://staff.develop.com/nielsb
> * nielsb at develop dot com
> * "A First Look at SQL Server 2005 for Developers"
> * http://www.awprofessional.com/title/0321180593
> ****************************************
**********
>
Sunday, February 12, 2012
Business Intelligence Development Studio for Visual Studio 03?
The goal is to get Report Writer to work but need to create the model first.
Here is my environmental info - we have SQL 2000 installed with RS 2000. I
wish to keep SQL2000. I have sucessfully set RS2005 next to RS 2000 and can
swtich between them fine. We have Visual studio 2003 and do not know if it is
possible to get "Business Intelligence Development Studio" set up on VS03 or
if we are forced to install Visual Studio 2005 as well? If so, can Visual
Studio 03 and 05 both be installed without issue?Business Intelligence Studio will install a version of VS 2005 if you do not
have it installed. Unless you are creating your own extensions or custom
assemblies then you do not need to purchase or install VS 2005. Just install
the BI Studio. It will function fine side by side with VS 2003 (I am doing
that today).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"James" <James@.discussions.microsoft.com> wrote in message
news:1FE2AD85-721B-4F78-979A-D7D0A105BE58@.microsoft.com...
> The goal is to get Report Writer to work but need to create the model
> first.
> Here is my environmental info - we have SQL 2000 installed with RS 2000.
> I
> wish to keep SQL2000. I have sucessfully set RS2005 next to RS 2000 and
> can
> swtich between them fine. We have Visual studio 2003 and do not know if it
> is
> possible to get "Business Intelligence Development Studio" set up on VS03
> or
> if we are forced to install Visual Studio 2005 as well? If so, can Visual
> Studio 03 and 05 both be installed without issue?|||Sound good Bruce - but which DVD do I request from myIT group to load just
Business Intelligence Development Studio? Also, any guidance as to the menu
pick just to get Business Intelligence Development Studio loaded once I get
the DVD mounted? Sometimes getting just a single componets it not to easy
to do, is it obvious? ;) Thanks Bruce!
"Bruce L-C [MVP]" wrote:
> Business Intelligence Studio will install a version of VS 2005 if you do not
> have it installed. Unless you are creating your own extensions or custom
> assemblies then you do not need to purchase or install VS 2005. Just install
> the BI Studio. It will function fine side by side with VS 2003 (I am doing
> that today).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "James" <James@.discussions.microsoft.com> wrote in message
> news:1FE2AD85-721B-4F78-979A-D7D0A105BE58@.microsoft.com...
> > The goal is to get Report Writer to work but need to create the model
> > first.
> > Here is my environmental info - we have SQL 2000 installed with RS 2000.
> > I
> > wish to keep SQL2000. I have sucessfully set RS2005 next to RS 2000 and
> > can
> > swtich between them fine. We have Visual studio 2003 and do not know if it
> > is
> > possible to get "Business Intelligence Development Studio" set up on VS03
> > or
> > if we are forced to install Visual Studio 2005 as well? If so, can Visual
> > Studio 03 and 05 both be installed without issue?
>
>|||Unlike RS 2000, RS 2005 is on the SQL Server CD/DVD. I don't remember the
exact menu option.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"James" <James@.discussions.microsoft.com> wrote in message
news:0692C421-99CA-4A91-883F-A0A12CCDDCD2@.microsoft.com...
> Sound good Bruce - but which DVD do I request from myIT group to load just
> Business Intelligence Development Studio? Also, any guidance as to the
> menu
> pick just to get Business Intelligence Development Studio loaded once I
> get
> the DVD mounted? Sometimes getting just a single componets it not to
> easy
> to do, is it obvious? ;) Thanks Bruce!
> "Bruce L-C [MVP]" wrote:
>> Business Intelligence Studio will install a version of VS 2005 if you do
>> not
>> have it installed. Unless you are creating your own extensions or custom
>> assemblies then you do not need to purchase or install VS 2005. Just
>> install
>> the BI Studio. It will function fine side by side with VS 2003 (I am
>> doing
>> that today).
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "James" <James@.discussions.microsoft.com> wrote in message
>> news:1FE2AD85-721B-4F78-979A-D7D0A105BE58@.microsoft.com...
>> > The goal is to get Report Writer to work but need to create the model
>> > first.
>> > Here is my environmental info - we have SQL 2000 installed with RS
>> > 2000.
>> > I
>> > wish to keep SQL2000. I have sucessfully set RS2005 next to RS 2000
>> > and
>> > can
>> > swtich between them fine. We have Visual studio 2003 and do not know if
>> > it
>> > is
>> > possible to get "Business Intelligence Development Studio" set up on
>> > VS03
>> > or
>> > if we are forced to install Visual Studio 2005 as well? If so, can
>> > Visual
>> > Studio 03 and 05 both be installed without issue?
>>
Here is my environmental info - we have SQL 2000 installed with RS 2000. I
wish to keep SQL2000. I have sucessfully set RS2005 next to RS 2000 and can
swtich between them fine. We have Visual studio 2003 and do not know if it is
possible to get "Business Intelligence Development Studio" set up on VS03 or
if we are forced to install Visual Studio 2005 as well? If so, can Visual
Studio 03 and 05 both be installed without issue?Business Intelligence Studio will install a version of VS 2005 if you do not
have it installed. Unless you are creating your own extensions or custom
assemblies then you do not need to purchase or install VS 2005. Just install
the BI Studio. It will function fine side by side with VS 2003 (I am doing
that today).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"James" <James@.discussions.microsoft.com> wrote in message
news:1FE2AD85-721B-4F78-979A-D7D0A105BE58@.microsoft.com...
> The goal is to get Report Writer to work but need to create the model
> first.
> Here is my environmental info - we have SQL 2000 installed with RS 2000.
> I
> wish to keep SQL2000. I have sucessfully set RS2005 next to RS 2000 and
> can
> swtich between them fine. We have Visual studio 2003 and do not know if it
> is
> possible to get "Business Intelligence Development Studio" set up on VS03
> or
> if we are forced to install Visual Studio 2005 as well? If so, can Visual
> Studio 03 and 05 both be installed without issue?|||Sound good Bruce - but which DVD do I request from myIT group to load just
Business Intelligence Development Studio? Also, any guidance as to the menu
pick just to get Business Intelligence Development Studio loaded once I get
the DVD mounted? Sometimes getting just a single componets it not to easy
to do, is it obvious? ;) Thanks Bruce!
"Bruce L-C [MVP]" wrote:
> Business Intelligence Studio will install a version of VS 2005 if you do not
> have it installed. Unless you are creating your own extensions or custom
> assemblies then you do not need to purchase or install VS 2005. Just install
> the BI Studio. It will function fine side by side with VS 2003 (I am doing
> that today).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "James" <James@.discussions.microsoft.com> wrote in message
> news:1FE2AD85-721B-4F78-979A-D7D0A105BE58@.microsoft.com...
> > The goal is to get Report Writer to work but need to create the model
> > first.
> > Here is my environmental info - we have SQL 2000 installed with RS 2000.
> > I
> > wish to keep SQL2000. I have sucessfully set RS2005 next to RS 2000 and
> > can
> > swtich between them fine. We have Visual studio 2003 and do not know if it
> > is
> > possible to get "Business Intelligence Development Studio" set up on VS03
> > or
> > if we are forced to install Visual Studio 2005 as well? If so, can Visual
> > Studio 03 and 05 both be installed without issue?
>
>|||Unlike RS 2000, RS 2005 is on the SQL Server CD/DVD. I don't remember the
exact menu option.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"James" <James@.discussions.microsoft.com> wrote in message
news:0692C421-99CA-4A91-883F-A0A12CCDDCD2@.microsoft.com...
> Sound good Bruce - but which DVD do I request from myIT group to load just
> Business Intelligence Development Studio? Also, any guidance as to the
> menu
> pick just to get Business Intelligence Development Studio loaded once I
> get
> the DVD mounted? Sometimes getting just a single componets it not to
> easy
> to do, is it obvious? ;) Thanks Bruce!
> "Bruce L-C [MVP]" wrote:
>> Business Intelligence Studio will install a version of VS 2005 if you do
>> not
>> have it installed. Unless you are creating your own extensions or custom
>> assemblies then you do not need to purchase or install VS 2005. Just
>> install
>> the BI Studio. It will function fine side by side with VS 2003 (I am
>> doing
>> that today).
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "James" <James@.discussions.microsoft.com> wrote in message
>> news:1FE2AD85-721B-4F78-979A-D7D0A105BE58@.microsoft.com...
>> > The goal is to get Report Writer to work but need to create the model
>> > first.
>> > Here is my environmental info - we have SQL 2000 installed with RS
>> > 2000.
>> > I
>> > wish to keep SQL2000. I have sucessfully set RS2005 next to RS 2000
>> > and
>> > can
>> > swtich between them fine. We have Visual studio 2003 and do not know if
>> > it
>> > is
>> > possible to get "Business Intelligence Development Studio" set up on
>> > VS03
>> > or
>> > if we are forced to install Visual Studio 2005 as well? If so, can
>> > Visual
>> > Studio 03 and 05 both be installed without issue?
>>
Friday, February 10, 2012
BulkLoad and .xsd problem
I would like to load a xml file to a database. For reasons this I use the
BulkLoad
COM object model. The elements of the project are following:
The XML file:
--
<?xml version="1.0" encoding="ISO8859-2" ?>
<export>
<ceg id="0000000147">
<rovat id = "0">
<alrovat id = "1">
<mezo id = "bir">piros</mezo>
<mezo id = "cf">tarka</mezo>
</alrovat>
</rovat>
<rovat id = "2">
<alrovat id = "1">
<mezo id = "nev">FA-MAG Ipari Kisszovetkezet</mezo>
</alrovat>
</rovat>
</ceg>
<ceg id="0000000153">
etc.
</ceg>
</export>
The columns of the tblExport table in the DB:
---
ceg_id char(10)
rovat_id varchar(10)
alrovat_id varchar(10)
mezo_id varchar(50)
mezo_text varchar(1000)
The following xsd file has been used:
--
<?xml version="1.0"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="ceg" sql:relation="[tblExport]">
<xsd:complexType>
<xsd:choice>
<xsd:element name="rovat" minOccurs="0" maxOccurs="unbounded">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="alrovat" minOccurs="0"
maxOccurs="unbounded">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="mezo" sql:field="mezo_text"
minOccurs="0" maxOccurs="unbounded">
<xsd:complexType>
<xsd:attribute name="id"
sql:field="mezo_id" type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="id" sql:field="alrovat_id"
type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="id" sql:field="rovat_id"
type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:choice>
<xsd:attribute name="id" sql:field="ceg_id" type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:schema>
I've expected the following result set (and I would like to get same):
ceg_id rovat_id alrovat_id mezo_id mezo
_text
----
0000000147 0 1 bir piros
0000000147 0 1 cf tarka
0000000147 2 1 nev FA-MAG Ipari Kisszovetkezet
0000000153 etc.
But I've got this:
ceg_id rovat_id alrovat_id mezo_id mezo
_text
----
0000000147 Null Null Null Null
0000000153 Null Null Null Null
etc.
Why? I tried to change the xsd file at many places and many times. For
example I changed <xsd:choice> to <xsd:sequence> but the BulkLoad
expected a reletionship on 'rovat'. But I don't want to take an extra table.
If I use sql:is-constant annotation an error will be raised saying that
constant element has no attribute. In most cases I don't get error but no
records will be generated.
Has anybody a good suggestion? I would be grateful for any help.
Thanks.
D. AttilaYou have to use xsd:sequence instead of xsd:choice and specify
sql:is-constant="1" on the elements 'rovat' and 'alrovat'.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Danyi, Attila" <DanyiAttila@.discussions.microsoft.com> wrote in message
news:B08BA56A-FAD0-4DDE-A0B7-1FA57EA7C27B@.microsoft.com...
> I would like to load a xml file to a database. For reasons this I use the
> BulkLoad
> COM object model. The elements of the project are following:
> The XML file:
> --
> <?xml version="1.0" encoding="ISO8859-2" ?>
> <export>
> <ceg id="0000000147">
> <rovat id = "0">
> <alrovat id = "1">
> <mezo id = "bir">piros</mezo>
> <mezo id = "cf">tarka</mezo>
> </alrovat>
> </rovat>
> <rovat id = "2">
> <alrovat id = "1">
> <mezo id = "nev">FA-MAG Ipari Kisszovetkezet</mezo>
> </alrovat>
> </rovat>
> </ceg>
> <ceg id="0000000153">
> etc.
> </ceg>
> </export>
> The columns of the tblExport table in the DB:
> ---
> ceg_id char(10)
> rovat_id varchar(10)
> alrovat_id varchar(10)
> mezo_id varchar(50)
> mezo_text varchar(1000)
> The following xsd file has been used:
> --
> <?xml version="1.0"?>
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="ceg" sql:relation="[tblExport]">
> <xsd:complexType>
> <xsd:choice>
> <xsd:element name="rovat" minOccurs="0" maxOccurs="unbounded">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="alrovat" minOccurs="0"
> maxOccurs="unbounded">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="mezo"
sql:field="mezo_text"
> minOccurs="0" maxOccurs="unbounded">
> <xsd:complexType>
> <xsd:attribute name="id"
> sql:field="mezo_id" type="xsd:string" />
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> <xsd:attribute name="id" sql:field="alrovat_id"
> type="xsd:string" />
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> <xsd:attribute name="id" sql:field="rovat_id"
> type="xsd:string" />
> </xsd:complexType>
> </xsd:element>
> </xsd:choice>
> <xsd:attribute name="id" sql:field="ceg_id" type="xsd:string" />
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> I've expected the following result set (and I would like to get same):
> ceg_id rovat_id alrovat_id mezo_id mezo_text
> ----
> 0000000147 0 1 bir piros
> 0000000147 0 1 cf tarka
> 0000000147 2 1 nev FA-MAG Ipari Kisszovetkezet
> 0000000153 etc.
> But I've got this:
> ceg_id rovat_id alrovat_id mezo_id mezo_text
> ----
> 0000000147 Null Null Null Null
> 0000000153 Null Null Null Null
> etc.
> Why? I tried to change the xsd file at many places and many times. For
> example I changed <xsd:choice> to <xsd:sequence> but the BulkLoad
> expected a reletionship on 'rovat'. But I don't want to take an extra
table.
> If I use sql:is-constant annotation an error will be raised saying that
> constant element has no attribute. In most cases I don't get error but no
> records will be generated.
> Has anybody a good suggestion? I would be grateful for any help.
> Thanks.
> D. Attila
>
>|||Thanks, but I have tried it. I get the following error:
.....constant/fixed element cannot have attributes.....
But I need these attributes.
D.A.
"Bertan ARI [MSFT]" wrote:
> You have to use xsd:sequence instead of xsd:choice and specify
> sql:is-constant="1" on the elements 'rovat' and 'alrovat'.
> --
> Bertan ARI
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
> "Danyi, Attila" <DanyiAttila@.discussions.microsoft.com> wrote in message
> news:B08BA56A-FAD0-4DDE-A0B7-1FA57EA7C27B@.microsoft.com...
> sql:field="mezo_text"
> table.
>
>|||Sorry my mistake. I didn't see the attributes.
Unfortunately, your scenario is currently not supported by Bulkload.
Currently we do not allow attributes on constant elements and there are no
future plans to support it.
You may use XSLT to transform the Xml into a shape Bulkload can support or
you may use OpenXml which doesn't have this limitation.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Danyi, Attila" <DanyiAttila@.discussions.microsoft.com> wrote in message
news:CFB3D497-86E8-4207-97EA-F3EC42D2DEE5@.microsoft.com...
> Thanks, but I have tried it. I get the following error:
> .....constant/fixed element cannot have attributes.....
> But I need these attributes.
> D.A.
> "Bertan ARI [MSFT]" wrote:
>
rights.
the
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
maxOccurs="unbounded">
sql:field="alrovat_id"
/>
> ----
> ----
that
no|||Thanks for your response.
D.A.
"Bertan ARI [MSFT]" wrote:
> Sorry my mistake. I didn't see the attributes.
> Unfortunately, your scenario is currently not supported by Bulkload.
> Currently we do not allow attributes on constant elements and there are no
> future plans to support it.
> You may use XSLT to transform the Xml into a shape Bulkload can support or
> you may use OpenXml which doesn't have this limitation.
> --
> Bertan ARI
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
> "Danyi, Attila" <DanyiAttila@.discussions.microsoft.com> wrote in message
> news:CFB3D497-86E8-4207-97EA-F3EC42D2DEE5@.microsoft.com...
> rights.
> the
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> maxOccurs="unbounded">
> sql:field="alrovat_id"
> />
> that
> no
>
>
BulkLoad
COM object model. The elements of the project are following:
The XML file:
--
<?xml version="1.0" encoding="ISO8859-2" ?>
<export>
<ceg id="0000000147">
<rovat id = "0">
<alrovat id = "1">
<mezo id = "bir">piros</mezo>
<mezo id = "cf">tarka</mezo>
</alrovat>
</rovat>
<rovat id = "2">
<alrovat id = "1">
<mezo id = "nev">FA-MAG Ipari Kisszovetkezet</mezo>
</alrovat>
</rovat>
</ceg>
<ceg id="0000000153">
etc.
</ceg>
</export>
The columns of the tblExport table in the DB:
---
ceg_id char(10)
rovat_id varchar(10)
alrovat_id varchar(10)
mezo_id varchar(50)
mezo_text varchar(1000)
The following xsd file has been used:
--
<?xml version="1.0"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="ceg" sql:relation="[tblExport]">
<xsd:complexType>
<xsd:choice>
<xsd:element name="rovat" minOccurs="0" maxOccurs="unbounded">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="alrovat" minOccurs="0"
maxOccurs="unbounded">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="mezo" sql:field="mezo_text"
minOccurs="0" maxOccurs="unbounded">
<xsd:complexType>
<xsd:attribute name="id"
sql:field="mezo_id" type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="id" sql:field="alrovat_id"
type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="id" sql:field="rovat_id"
type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:choice>
<xsd:attribute name="id" sql:field="ceg_id" type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:schema>
I've expected the following result set (and I would like to get same):
ceg_id rovat_id alrovat_id mezo_id mezo
_text
----
0000000147 0 1 bir piros
0000000147 0 1 cf tarka
0000000147 2 1 nev FA-MAG Ipari Kisszovetkezet
0000000153 etc.
But I've got this:
ceg_id rovat_id alrovat_id mezo_id mezo
_text
----
0000000147 Null Null Null Null
0000000153 Null Null Null Null
etc.
Why? I tried to change the xsd file at many places and many times. For
example I changed <xsd:choice> to <xsd:sequence> but the BulkLoad
expected a reletionship on 'rovat'. But I don't want to take an extra table.
If I use sql:is-constant annotation an error will be raised saying that
constant element has no attribute. In most cases I don't get error but no
records will be generated.
Has anybody a good suggestion? I would be grateful for any help.
Thanks.
D. AttilaYou have to use xsd:sequence instead of xsd:choice and specify
sql:is-constant="1" on the elements 'rovat' and 'alrovat'.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Danyi, Attila" <DanyiAttila@.discussions.microsoft.com> wrote in message
news:B08BA56A-FAD0-4DDE-A0B7-1FA57EA7C27B@.microsoft.com...
> I would like to load a xml file to a database. For reasons this I use the
> BulkLoad
> COM object model. The elements of the project are following:
> The XML file:
> --
> <?xml version="1.0" encoding="ISO8859-2" ?>
> <export>
> <ceg id="0000000147">
> <rovat id = "0">
> <alrovat id = "1">
> <mezo id = "bir">piros</mezo>
> <mezo id = "cf">tarka</mezo>
> </alrovat>
> </rovat>
> <rovat id = "2">
> <alrovat id = "1">
> <mezo id = "nev">FA-MAG Ipari Kisszovetkezet</mezo>
> </alrovat>
> </rovat>
> </ceg>
> <ceg id="0000000153">
> etc.
> </ceg>
> </export>
> The columns of the tblExport table in the DB:
> ---
> ceg_id char(10)
> rovat_id varchar(10)
> alrovat_id varchar(10)
> mezo_id varchar(50)
> mezo_text varchar(1000)
> The following xsd file has been used:
> --
> <?xml version="1.0"?>
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="ceg" sql:relation="[tblExport]">
> <xsd:complexType>
> <xsd:choice>
> <xsd:element name="rovat" minOccurs="0" maxOccurs="unbounded">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="alrovat" minOccurs="0"
> maxOccurs="unbounded">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="mezo"
sql:field="mezo_text"
> minOccurs="0" maxOccurs="unbounded">
> <xsd:complexType>
> <xsd:attribute name="id"
> sql:field="mezo_id" type="xsd:string" />
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> <xsd:attribute name="id" sql:field="alrovat_id"
> type="xsd:string" />
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> <xsd:attribute name="id" sql:field="rovat_id"
> type="xsd:string" />
> </xsd:complexType>
> </xsd:element>
> </xsd:choice>
> <xsd:attribute name="id" sql:field="ceg_id" type="xsd:string" />
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> I've expected the following result set (and I would like to get same):
> ceg_id rovat_id alrovat_id mezo_id mezo_text
> ----
> 0000000147 0 1 bir piros
> 0000000147 0 1 cf tarka
> 0000000147 2 1 nev FA-MAG Ipari Kisszovetkezet
> 0000000153 etc.
> But I've got this:
> ceg_id rovat_id alrovat_id mezo_id mezo_text
> ----
> 0000000147 Null Null Null Null
> 0000000153 Null Null Null Null
> etc.
> Why? I tried to change the xsd file at many places and many times. For
> example I changed <xsd:choice> to <xsd:sequence> but the BulkLoad
> expected a reletionship on 'rovat'. But I don't want to take an extra
table.
> If I use sql:is-constant annotation an error will be raised saying that
> constant element has no attribute. In most cases I don't get error but no
> records will be generated.
> Has anybody a good suggestion? I would be grateful for any help.
> Thanks.
> D. Attila
>
>|||Thanks, but I have tried it. I get the following error:
.....constant/fixed element cannot have attributes.....
But I need these attributes.
D.A.
"Bertan ARI [MSFT]" wrote:
> You have to use xsd:sequence instead of xsd:choice and specify
> sql:is-constant="1" on the elements 'rovat' and 'alrovat'.
> --
> Bertan ARI
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
> "Danyi, Attila" <DanyiAttila@.discussions.microsoft.com> wrote in message
> news:B08BA56A-FAD0-4DDE-A0B7-1FA57EA7C27B@.microsoft.com...
> sql:field="mezo_text"
> table.
>
>|||Sorry my mistake. I didn't see the attributes.
Unfortunately, your scenario is currently not supported by Bulkload.
Currently we do not allow attributes on constant elements and there are no
future plans to support it.
You may use XSLT to transform the Xml into a shape Bulkload can support or
you may use OpenXml which doesn't have this limitation.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Danyi, Attila" <DanyiAttila@.discussions.microsoft.com> wrote in message
news:CFB3D497-86E8-4207-97EA-F3EC42D2DEE5@.microsoft.com...
> Thanks, but I have tried it. I get the following error:
> .....constant/fixed element cannot have attributes.....
> But I need these attributes.
> D.A.
> "Bertan ARI [MSFT]" wrote:
>
rights.
the
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
maxOccurs="unbounded">
sql:field="alrovat_id"
/>
> ----
> ----
that
no|||Thanks for your response.
D.A.
"Bertan ARI [MSFT]" wrote:
> Sorry my mistake. I didn't see the attributes.
> Unfortunately, your scenario is currently not supported by Bulkload.
> Currently we do not allow attributes on constant elements and there are no
> future plans to support it.
> You may use XSLT to transform the Xml into a shape Bulkload can support or
> you may use OpenXml which doesn't have this limitation.
> --
> Bertan ARI
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
> "Danyi, Attila" <DanyiAttila@.discussions.microsoft.com> wrote in message
> news:CFB3D497-86E8-4207-97EA-F3EC42D2DEE5@.microsoft.com...
> rights.
> the
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> maxOccurs="unbounded">
> sql:field="alrovat_id"
> />
> that
> no
>
>
BulkLoad and .xsd problem
I would like to load a xml file to a database. For reasons this I use the
BulkLoad
COM object model. The elements of the project are following:
The XML file:
<?xml version="1.0" encoding="ISO8859-2" ?>
<export>
<ceg id="0000000147">
<rovat id = "0">
<alrovat id = "1">
<mezo id = "bir">piros</mezo>
<mezo id = "cf">tarka</mezo>
</alrovat>
</rovat>
<rovat id = "2">
<alrovat id = "1">
<mezo id = "nev">FA-MAG Ipari Kisszovetkezet</mezo>
</alrovat>
</rovat>
</ceg>
<ceg id="0000000153">
etc.
</ceg>
</export>
The columns of the tblExport table in the DB:
ceg_idchar(10)
rovat_idvarchar(10)
alrovat_idvarchar(10)
mezo_idvarchar(50)
mezo_textvarchar(1000)
The following xsd file has been used:
<?xml version="1.0"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="ceg" sql:relation="[tblExport]">
<xsd:complexType>
<xsd:choice>
<xsd:element name="rovat" minOccurs="0" maxOccurs="unbounded">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="alrovat" minOccurs="0"
maxOccurs="unbounded">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="mezo" sql:field="mezo_text"
minOccurs="0" maxOccurs="unbounded">
<xsd:complexType>
<xsd:attribute name="id"
sql:field="mezo_id" type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="id" sql:field="alrovat_id"
type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="id" sql:field="rovat_id"
type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:choice>
<xsd:attribute name="id" sql:field="ceg_id" type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:schema>
I've expected the following result set (and I would like to get same):
ceg_idrovat_idalrovat_idmezo_idmezo_text
000000014701birpiros
000000014701cftarka
000000014721nevFA-MAG Ipari Kisszovetkezet
0000000153etc.
But I've got this:
ceg_idrovat_idalrovat_idmezo_idmezo_text
0000000147NullNullNullNull
0000000153NullNullNullNull
etc.
Why? I tried to change the xsd file at many places and many times. For
example I changed <xsd:choice> to <xsd:sequence> but the BulkLoad
expected a reletionship on 'rovat'. But I don't want to take an extra table.
If I use sql:is-constant annotation an error will be raised saying that
constant element has no attribute. In most cases I don't get error but no
records will be generated.
Has anybody a good suggestion? I would be grateful for any help.
Thanks.
D. Attila
You have to use xsd:sequence instead of xsd:choice and specify
sql:is-constant="1" on the elements 'rovat' and 'alrovat'.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Danyi, Attila" <DanyiAttila@.discussions.microsoft.com> wrote in message
news:B08BA56A-FAD0-4DDE-A0B7-1FA57EA7C27B@.microsoft.com...
> I would like to load a xml file to a database. For reasons this I use the
> BulkLoad
> COM object model. The elements of the project are following:
> The XML file:
> --
> <?xml version="1.0" encoding="ISO8859-2" ?>
> <export>
> <ceg id="0000000147">
> <rovat id = "0">
> <alrovat id = "1">
> <mezo id = "bir">piros</mezo>
> <mezo id = "cf">tarka</mezo>
> </alrovat>
> </rovat>
> <rovat id = "2">
> <alrovat id = "1">
> <mezo id = "nev">FA-MAG Ipari Kisszovetkezet</mezo>
> </alrovat>
> </rovat>
> </ceg>
> <ceg id="0000000153">
> etc.
> </ceg>
> </export>
> The columns of the tblExport table in the DB:
> ceg_id char(10)
> rovat_id varchar(10)
> alrovat_id varchar(10)
> mezo_id varchar(50)
> mezo_text varchar(1000)
> The following xsd file has been used:
> --
> <?xml version="1.0"?>
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="ceg" sql:relation="[tblExport]">
> <xsd:complexType>
> <xsd:choice>
> <xsd:element name="rovat" minOccurs="0" maxOccurs="unbounded">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="alrovat" minOccurs="0"
> maxOccurs="unbounded">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="mezo"
sql:field="mezo_text"
> minOccurs="0" maxOccurs="unbounded">
> <xsd:complexType>
> <xsd:attribute name="id"
> sql:field="mezo_id" type="xsd:string" />
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> <xsd:attribute name="id" sql:field="alrovat_id"
> type="xsd:string" />
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> <xsd:attribute name="id" sql:field="rovat_id"
> type="xsd:string" />
> </xsd:complexType>
> </xsd:element>
> </xsd:choice>
> <xsd:attribute name="id" sql:field="ceg_id" type="xsd:string" />
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> I've expected the following result set (and I would like to get same):
> ceg_id rovat_id alrovat_id mezo_id mezo_text
> ----
> 0000000147 0 1 bir piros
> 0000000147 0 1 cf tarka
> 0000000147 2 1 nev FA-MAG Ipari Kisszovetkezet
> 0000000153 etc.
> But I've got this:
> ceg_id rovat_id alrovat_id mezo_id mezo_text
> ----
> 0000000147 Null Null Null Null
> 0000000153 Null Null Null Null
> etc.
> Why? I tried to change the xsd file at many places and many times. For
> example I changed <xsd:choice> to <xsd:sequence> but the BulkLoad
> expected a reletionship on 'rovat'. But I don't want to take an extra
table.
> If I use sql:is-constant annotation an error will be raised saying that
> constant element has no attribute. In most cases I don't get error but no
> records will be generated.
> Has anybody a good suggestion? I would be grateful for any help.
> Thanks.
> D. Attila
>
>
|||Thanks, but I have tried it. I get the following error:
......constant/fixed element cannot have attributes.....
But I need these attributes.
D.A.
"Bertan ARI [MSFT]" wrote:
> You have to use xsd:sequence instead of xsd:choice and specify
> sql:is-constant="1" on the elements 'rovat' and 'alrovat'.
> --
> Bertan ARI
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Danyi, Attila" <DanyiAttila@.discussions.microsoft.com> wrote in message
> news:B08BA56A-FAD0-4DDE-A0B7-1FA57EA7C27B@.microsoft.com...
> sql:field="mezo_text"
> table.
>
>
|||Sorry my mistake. I didn't see the attributes.
Unfortunately, your scenario is currently not supported by Bulkload.
Currently we do not allow attributes on constant elements and there are no
future plans to support it.
You may use XSLT to transform the Xml into a shape Bulkload can support or
you may use OpenXml which doesn't have this limitation.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Danyi, Attila" <DanyiAttila@.discussions.microsoft.com> wrote in message
news:CFB3D497-86E8-4207-97EA-F3EC42D2DEE5@.microsoft.com...[vbcol=seagreen]
> Thanks, but I have tried it. I get the following error:
> .....constant/fixed element cannot have attributes.....
> But I need these attributes.
> D.A.
> "Bertan ARI [MSFT]" wrote:
rights.[vbcol=seagreen]
the[vbcol=seagreen]
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">[vbcol=seagreen]
maxOccurs="unbounded">[vbcol=seagreen]
sql:field="alrovat_id"[vbcol=seagreen]
/>[vbcol=seagreen]
> ----
> ----
that[vbcol=seagreen]
no[vbcol=seagreen]
|||Thanks for your response.
D.A.
"Bertan ARI [MSFT]" wrote:
> Sorry my mistake. I didn't see the attributes.
> Unfortunately, your scenario is currently not supported by Bulkload.
> Currently we do not allow attributes on constant elements and there are no
> future plans to support it.
> You may use XSLT to transform the Xml into a shape Bulkload can support or
> you may use OpenXml which doesn't have this limitation.
> --
> Bertan ARI
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Danyi, Attila" <DanyiAttila@.discussions.microsoft.com> wrote in message
> news:CFB3D497-86E8-4207-97EA-F3EC42D2DEE5@.microsoft.com...
> rights.
> the
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> maxOccurs="unbounded">
> sql:field="alrovat_id"
> />
> that
> no
>
>
BulkLoad
COM object model. The elements of the project are following:
The XML file:
<?xml version="1.0" encoding="ISO8859-2" ?>
<export>
<ceg id="0000000147">
<rovat id = "0">
<alrovat id = "1">
<mezo id = "bir">piros</mezo>
<mezo id = "cf">tarka</mezo>
</alrovat>
</rovat>
<rovat id = "2">
<alrovat id = "1">
<mezo id = "nev">FA-MAG Ipari Kisszovetkezet</mezo>
</alrovat>
</rovat>
</ceg>
<ceg id="0000000153">
etc.
</ceg>
</export>
The columns of the tblExport table in the DB:
ceg_idchar(10)
rovat_idvarchar(10)
alrovat_idvarchar(10)
mezo_idvarchar(50)
mezo_textvarchar(1000)
The following xsd file has been used:
<?xml version="1.0"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="ceg" sql:relation="[tblExport]">
<xsd:complexType>
<xsd:choice>
<xsd:element name="rovat" minOccurs="0" maxOccurs="unbounded">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="alrovat" minOccurs="0"
maxOccurs="unbounded">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="mezo" sql:field="mezo_text"
minOccurs="0" maxOccurs="unbounded">
<xsd:complexType>
<xsd:attribute name="id"
sql:field="mezo_id" type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="id" sql:field="alrovat_id"
type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="id" sql:field="rovat_id"
type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:choice>
<xsd:attribute name="id" sql:field="ceg_id" type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:schema>
I've expected the following result set (and I would like to get same):
ceg_idrovat_idalrovat_idmezo_idmezo_text
000000014701birpiros
000000014701cftarka
000000014721nevFA-MAG Ipari Kisszovetkezet
0000000153etc.
But I've got this:
ceg_idrovat_idalrovat_idmezo_idmezo_text
0000000147NullNullNullNull
0000000153NullNullNullNull
etc.
Why? I tried to change the xsd file at many places and many times. For
example I changed <xsd:choice> to <xsd:sequence> but the BulkLoad
expected a reletionship on 'rovat'. But I don't want to take an extra table.
If I use sql:is-constant annotation an error will be raised saying that
constant element has no attribute. In most cases I don't get error but no
records will be generated.
Has anybody a good suggestion? I would be grateful for any help.
Thanks.
D. Attila
You have to use xsd:sequence instead of xsd:choice and specify
sql:is-constant="1" on the elements 'rovat' and 'alrovat'.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Danyi, Attila" <DanyiAttila@.discussions.microsoft.com> wrote in message
news:B08BA56A-FAD0-4DDE-A0B7-1FA57EA7C27B@.microsoft.com...
> I would like to load a xml file to a database. For reasons this I use the
> BulkLoad
> COM object model. The elements of the project are following:
> The XML file:
> --
> <?xml version="1.0" encoding="ISO8859-2" ?>
> <export>
> <ceg id="0000000147">
> <rovat id = "0">
> <alrovat id = "1">
> <mezo id = "bir">piros</mezo>
> <mezo id = "cf">tarka</mezo>
> </alrovat>
> </rovat>
> <rovat id = "2">
> <alrovat id = "1">
> <mezo id = "nev">FA-MAG Ipari Kisszovetkezet</mezo>
> </alrovat>
> </rovat>
> </ceg>
> <ceg id="0000000153">
> etc.
> </ceg>
> </export>
> The columns of the tblExport table in the DB:
> ceg_id char(10)
> rovat_id varchar(10)
> alrovat_id varchar(10)
> mezo_id varchar(50)
> mezo_text varchar(1000)
> The following xsd file has been used:
> --
> <?xml version="1.0"?>
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="ceg" sql:relation="[tblExport]">
> <xsd:complexType>
> <xsd:choice>
> <xsd:element name="rovat" minOccurs="0" maxOccurs="unbounded">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="alrovat" minOccurs="0"
> maxOccurs="unbounded">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="mezo"
sql:field="mezo_text"
> minOccurs="0" maxOccurs="unbounded">
> <xsd:complexType>
> <xsd:attribute name="id"
> sql:field="mezo_id" type="xsd:string" />
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> <xsd:attribute name="id" sql:field="alrovat_id"
> type="xsd:string" />
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> <xsd:attribute name="id" sql:field="rovat_id"
> type="xsd:string" />
> </xsd:complexType>
> </xsd:element>
> </xsd:choice>
> <xsd:attribute name="id" sql:field="ceg_id" type="xsd:string" />
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> I've expected the following result set (and I would like to get same):
> ceg_id rovat_id alrovat_id mezo_id mezo_text
> ----
> 0000000147 0 1 bir piros
> 0000000147 0 1 cf tarka
> 0000000147 2 1 nev FA-MAG Ipari Kisszovetkezet
> 0000000153 etc.
> But I've got this:
> ceg_id rovat_id alrovat_id mezo_id mezo_text
> ----
> 0000000147 Null Null Null Null
> 0000000153 Null Null Null Null
> etc.
> Why? I tried to change the xsd file at many places and many times. For
> example I changed <xsd:choice> to <xsd:sequence> but the BulkLoad
> expected a reletionship on 'rovat'. But I don't want to take an extra
table.
> If I use sql:is-constant annotation an error will be raised saying that
> constant element has no attribute. In most cases I don't get error but no
> records will be generated.
> Has anybody a good suggestion? I would be grateful for any help.
> Thanks.
> D. Attila
>
>
|||Thanks, but I have tried it. I get the following error:
......constant/fixed element cannot have attributes.....
But I need these attributes.
D.A.
"Bertan ARI [MSFT]" wrote:
> You have to use xsd:sequence instead of xsd:choice and specify
> sql:is-constant="1" on the elements 'rovat' and 'alrovat'.
> --
> Bertan ARI
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Danyi, Attila" <DanyiAttila@.discussions.microsoft.com> wrote in message
> news:B08BA56A-FAD0-4DDE-A0B7-1FA57EA7C27B@.microsoft.com...
> sql:field="mezo_text"
> table.
>
>
|||Sorry my mistake. I didn't see the attributes.
Unfortunately, your scenario is currently not supported by Bulkload.
Currently we do not allow attributes on constant elements and there are no
future plans to support it.
You may use XSLT to transform the Xml into a shape Bulkload can support or
you may use OpenXml which doesn't have this limitation.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Danyi, Attila" <DanyiAttila@.discussions.microsoft.com> wrote in message
news:CFB3D497-86E8-4207-97EA-F3EC42D2DEE5@.microsoft.com...[vbcol=seagreen]
> Thanks, but I have tried it. I get the following error:
> .....constant/fixed element cannot have attributes.....
> But I need these attributes.
> D.A.
> "Bertan ARI [MSFT]" wrote:
rights.[vbcol=seagreen]
the[vbcol=seagreen]
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">[vbcol=seagreen]
maxOccurs="unbounded">[vbcol=seagreen]
sql:field="alrovat_id"[vbcol=seagreen]
/>[vbcol=seagreen]
> ----
> ----
that[vbcol=seagreen]
no[vbcol=seagreen]
|||Thanks for your response.
D.A.
"Bertan ARI [MSFT]" wrote:
> Sorry my mistake. I didn't see the attributes.
> Unfortunately, your scenario is currently not supported by Bulkload.
> Currently we do not allow attributes on constant elements and there are no
> future plans to support it.
> You may use XSLT to transform the Xml into a shape Bulkload can support or
> you may use OpenXml which doesn't have this limitation.
> --
> Bertan ARI
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Danyi, Attila" <DanyiAttila@.discussions.microsoft.com> wrote in message
> news:CFB3D497-86E8-4207-97EA-F3EC42D2DEE5@.microsoft.com...
> rights.
> the
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> maxOccurs="unbounded">
> sql:field="alrovat_id"
> />
> that
> no
>
>
Subscribe to:
Posts (Atom)