SQL server connection: tables

Showing posts with label tables. Show all posts

Monday, March 19, 2012

Another Temporary Table Q

Hi
I've been playing around with temporary tables ... everything works fine,
but I've read a few articles saying how they can affect overall performance
and I'm wondering if there may be a faster alternative performance.
Basically, the user fires off a query at the DB. With the results, I need to
delete some rows which are retrieved as well (they cannot be filtered out as
part of the SELECT statement because of the effects of LEFT JOINS) as amend
certain data values prior to returning the data to the user. I thought
temporary tables would be the way, so I could just
1) Create Temp Table
2) Populate with INSERT
3) DELETE rows fulfilling certain criteria
4) UPDATE certain data values
5) Return contents of Temporary Table to user as ADO dataset.
I'm wondering now if I could actually get all the Data back from the results
of the initial SELECT statement to the local machine fast, and then run the
DELETE/UPDATES locally so as to remove load from the central server. I've
done timings already comparing Temporary Tables to cycling through the raw
dataset locally and deleting rows and/or updating Data Values. Using
temporary tables is much faster.
So is there a way of getting all the data back as fast as possible from the
central server and then speedily deleting/updating rows of the local dataset
other than the way I've been testing. I'd wondered about creating some
in-local table (using DAO, for example) and then firing SQL at that ... but
I'm just not sure if that would be any quicker.
So in summary-
1) What's the quickest way of getting ALL the data from the server to the
local client?
2) What should I use on the local client?
Any thoughts would be greatly appreciated.
Thanks, again
SimonCan you post some more details, including DDL and some sample
data. There's a good chance this can all be achieved in a single
SELECT.|||<markc600@.hotmail.com> wrote in message
news:1137676726.723163.59050@.g47g2000cwa.googlegroups.com...
> Can you post some more details, including DDL and some sample
> data. There's a good chance this can all be achieved in a single
> SELECT.
Thanks for replying.
It will take me a while to get this together, I'm afraid.|||On Thu, 19 Jan 2006 12:40:29 -0000, Simon Woods wrote:

>Hi
>I've been playing around with temporary tables ... everything works fine,
>but I've read a few articles saying how they can affect overall performance
>and I'm wondering if there may be a faster alternative performance.
>Basically, the user fires off a query at the DB. With the results, I need t
o
>delete some rows which are retrieved as well (they cannot be filtered out a
s
>part of the SELECT statement because of the effects of LEFT JOINS) as amend
>certain data values prior to returning the data to the user. I thought
>temporary tables would be the way, so I could just
>1) Create Temp Table
>2) Populate with INSERT
>3) DELETE rows fulfilling certain criteria
>4) UPDATE certain data values
>5) Return contents of Temporary Table to user as ADO dataset.
>I'm wondering now if I could actually get all the Data back from the result
s
>of the initial SELECT statement to the local machine fast, and then run the
>DELETE/UPDATES locally so as to remove load from the central server.
Hi Simon.
Either I am completely misunderstanding what you're writing, or you are
completely misunderstanding how SQL Server works.
From this message, it appearrs as if you want to get all rows to the
client, then iterate over the results and send update or delete
statements for each row "to remove load from the server".
However, since the data is stored on the server, you'll have to do the
deletes and updates on the server as well. This means that in your
version, you'll have lots of network traffic and you force SQL Server to
do individual updates and deletes in the order that your application
processes them.
It's much better to keep all the logic in a single stored procedure on
the server. Not only will this save you lots of network traffic, it will
also enable the query optimizer to pick the best order of execution for
the updates and deletes (provided you write them as set-based UPDATE and
DELETE statement, of course).
Hugo Kornelis, SQL Server MVP|||Hugo ... thanks for replying ... see below
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info> wrote in message
news:qp50t15rumqtlr0din5fdbqc4tlh1g01kf@.
4ax.com...
> On Thu, 19 Jan 2006 12:40:29 -0000, Simon Woods wrote:
>
> Hi Simon.
> Either I am completely misunderstanding what you're writing, or you are
> completely misunderstanding how SQL Server works.
... the latter is most likely ...

> From this message, it appearrs as if you want to get all rows to the
> client, then iterate over the results and send update or delete
> statements for each row "to remove load from the server".
> However, since the data is stored on the server, you'll have to do the
> deletes and updates on the server as well. This means that in your
> version, you'll have lots of network traffic and you force SQL Server to
> do individual updates and deletes in the order that your application
> processes them.
On the client I'm actually wanting a read-only set of data so I don't want
to do any updating of the source data only issuing SQL to update and delete
the temporary table.
Having chatted to some of my colleagues, I've been pointed to ADO
disconnected datasets to try and move load away from temporary tables on the
server to recordset processing on the client. However, at the moment my
preference is to go with the temporary table solution - but I'll have to do
a bit more digging.
Thanks again|||On Fri, 20 Jan 2006 13:06:32 -0000, Simon Woods wrote:
(snip)
>Having chatted to some of my colleagues, I've been pointed to ADO
>disconnected datasets to try and move load away from temporary tables on th
e
>server to recordset processing on the client. However, at the moment my
>preference is to go with the temporary table solution - but I'll have to do
>a bit more digging.
Hi Simon,
I'm not familiar with ADO, so I can't comment on the disconnected
datasets. But considering that SQL Server is designed to do quick and
efficient manipulations on large amounts of data, I'm very much inclined
to agree with your preference of doing it on the server.
Hugo Kornelis, SQL Server MVP

Another SUM limitation problem

I am trying to simply do a rather simply you'd think.... SUM in my textbox on 3 COUNT fields in 3 tables...it's not possible, I get errors whatever way I try this. Look at the Grand Total, that's what I want....a sum of the 3 black fields that have COUNT in them. If this is not possible in SSRS 2005, then Microsoft missed something huge that totally degrades their entire platform, this sucks!

I'm so sick and tired in the past 2 months, very spent on battling SSRS 2005 limitations on Grand Totals like this on many occasions both in tables and freeform textboxes. If you can't do an aggregate on an aggregate then this interface is useless!

http://www.webfound.net/grand_total.jpg

You should be able to do this with a workaround and Code. On each count row, add a hidden cell that assigns a value to a private variable. In your text box, run the code to add up your values.

You should be able to do this with an array. But here is a quick and dirty, assuming you only have two count fields. For simplicity, they are called item1 and item2.

So the cell next to your first count would call code.SetItem1(ReportItems!item1.Value)

The next one would reference the SetItem2 function.

Your textbox then calls GrandTotal()

Code behind:

Dim private _item1 as Integer
Dim private _item2 as Integer

Public Function SetItem1(ByVal item1 As Object) As Integer

_item1 =item1
End Function

Public Function SetItem2(ByVal item2 As Object) As Integer

_item2 =item2
End Function

Public Function GrandTotal() as Object

Return _item1 + _item2

End Function

Another SQL Query!

Dear All,
I have two tables in my database, part of a tennis league results and
fixtures website I am putting together.
Results
id fixture_id home_team_rubbers away_team_rubbers
1 229 2 2
2 253 2 2
3 265 1 3
4 230 2 2
Fixtures Table
id home_team_id away_team_id match_date
229 20 26 2006-05-03
230 20 29 2006-05-31
231 20 45 2006-05-17
232 20 78 2006-06-28
233 20 79 2006-07-26
234 20 89 2006-07-12
235 26 20 2006-06-20
236 26 29 2006-07-25
237 26 45 2006-07-11
238 26 78 2006-05-16
239 26 79 2006-06-13
240 26 89 2006-05-30
253 78 20 2006-05-09
265 89 20 2006-05-23
What I want to do it list all the fixture (based on fixure id) that
don't have a result associated with the fixture through an SQL query.
The resulting output from the query that I desire is the following.
Query Output
fixture_id
231
232
233
234
235
236
237
238
239
240
Any ideas how I can do this?
Cheers,
SimonTry:
select
f.id
from
Fixtures f
where not exists
(
select
*
from
Results r
where
r.fixture_id = f.id
)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<simon.stockton@.baesystems.com> wrote in message
news:1149375877.441866.70510@.i40g2000cwc.googlegroups.com...
Dear All,
I have two tables in my database, part of a tennis league results and
fixtures website I am putting together.
Results
id fixture_id home_team_rubbers away_team_rubbers
1 229 2 2
2 253 2 2
3 265 1 3
4 230 2 2
Fixtures Table
id home_team_id away_team_id match_date
229 20 26 2006-05-03
230 20 29 2006-05-31
231 20 45 2006-05-17
232 20 78 2006-06-28
233 20 79 2006-07-26
234 20 89 2006-07-12
235 26 20 2006-06-20
236 26 29 2006-07-25
237 26 45 2006-07-11
238 26 78 2006-05-16
239 26 79 2006-06-13
240 26 89 2006-05-30
253 78 20 2006-05-09
265 89 20 2006-05-23
What I want to do it list all the fixture (based on fixure id) that
don't have a result associated with the fixture through an SQL query.
The resulting output from the query that I desire is the following.
Query Output
fixture_id
231
232
233
234
235
236
237
238
239
240
Any ideas how I can do this?
Cheers,
Simon|||Select f.id
From Fixtures f
Left Join Results r on f.id = r.fixture_id
Where r.id Is Null
Order By f.id
Tom
<simon.stockton@.baesystems.com> wrote in message
news:1149375877.441866.70510@.i40g2000cwc.googlegroups.com...
> Dear All,
> I have two tables in my database, part of a tennis league results and
> fixtures website I am putting together.
> Results
> id fixture_id home_team_rubbers away_team_rubbers
> 1 229 2 2
> 2 253 2 2
> 3 265 1 3
> 4 230 2 2
> Fixtures Table
> id home_team_id away_team_id match_date
> 229 20 26 2006-05-03
> 230 20 29 2006-05-31
> 231 20 45 2006-05-17
> 232 20 78 2006-06-28
> 233 20 79 2006-07-26
> 234 20 89 2006-07-12
> 235 26 20 2006-06-20
> 236 26 29 2006-07-25
> 237 26 45 2006-07-11
> 238 26 78 2006-05-16
> 239 26 79 2006-06-13
> 240 26 89 2006-05-30
> 253 78 20 2006-05-09
> 265 89 20 2006-05-23
> What I want to do it list all the fixture (based on fixure id) that
> don't have a result associated with the fixture through an SQL query.
> The resulting output from the query that I desire is the following.
> Query Output
> fixture_id
> 231
> 232
> 233
> 234
> 235
> 236
> 237
> 238
> 239
> 240
> Any ideas how I can do this?
> Cheers,
> Simon
>|||Thanks guys, much appreciated!

Wednesday, March 7, 2012

another complex query

Hi, here am i back ..
not sure it's possible to solve it in one query ...
there are four tables:
1) headquarters
--
hqID (primary key)
hqname
2) department
--
depID (primary key)
depname
hqID
3) reports
--
repID (p. key)
depID
userID
4) users
--
userID (p.key)
username
I want to get in one query those four fields:
hqID, hqname, depID, depname + varchar(number of distinct users that has
made a report for
each department) . This last field is a concatenation.
The complexity resides in the fact there is need for a COUNT among other
fields ...
E.g.: table reports may look like this:
repID depID userID
1 1 1
2 1 1
3 1 3
4 3 6
5 4 8
This gives:
for dep 1: 2 distinct users
for dep 2: 0
for dep 3: 1 distinct user
for dep 4: 1 distinct user
Thanks for help
ChrisTry:
select
d.depID
, count (distinct r.userID)
from
departments d
left join
reports r on r.depID = d.depID
group by
d.depID
order by
d.depID
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Chris" <ch@.spam.it> wrote in message
news:%23HOudwFBIHA.3900@.TK2MSFTNGP02.phx.gbl...
Hi, here am i back ..
not sure it's possible to solve it in one query ...
there are four tables:
1) headquarters
--
hqID (primary key)
hqname
2) department
--
depID (primary key)
depname
hqID
3) reports
--
repID (p. key)
depID
userID
4) users
--
userID (p.key)
username
I want to get in one query those four fields:
hqID, hqname, depID, depname + varchar(number of distinct users that has
made a report for
each department) . This last field is a concatenation.
The complexity resides in the fact there is need for a COUNT among other
fields ...
E.g.: table reports may look like this:
repID depID userID
1 1 1
2 1 1
3 1 3
4 3 6
5 4 8
This gives:
for dep 1: 2 distinct users
for dep 2: 0
for dep 3: 1 distinct user
for dep 4: 1 distinct user
Thanks for help
Chris|||Thanks
"Tom Moreau" <tom@.dont.spam.me.cips.ca> schreef in bericht
news:estlIZHBIHA.3848@.TK2MSFTNGP05.phx.gbl...
> Try:
> select
> d.depID
> , count (distinct r.userID)
> from
> departments d
> left join
> reports r on r.depID = d.depID
> group by
> d.depID
> order by
> d.depID
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Chris" <ch@.spam.it> wrote in message
> news:%23HOudwFBIHA.3900@.TK2MSFTNGP02.phx.gbl...
> Hi, here am i back ..
> not sure it's possible to solve it in one query ...
> there are four tables:
> 1) headquarters
> --
> hqID (primary key)
> hqname
> 2) department
> --
> depID (primary key)
> depname
> hqID
> 3) reports
> --
> repID (p. key)
> depID
> userID
> 4) users
> --
> userID (p.key)
> username
> I want to get in one query those four fields:
> hqID, hqname, depID, depname + varchar(number of distinct users that has
> made a report for
> each department) . This last field is a concatenation.
> The complexity resides in the fact there is need for a COUNT among other
> fields ...
>
> E.g.: table reports may look like this:
> repID depID userID
> 1 1 1
> 2 1 1
> 3 1 3
> 4 3 6
> 5 4 8
> This gives:
> for dep 1: 2 distinct users
> for dep 2: 0
> for dep 3: 1 distinct user
> for dep 4: 1 distinct user
> Thanks for help
> Chris
>

Saturday, February 25, 2012

Annotated schema with views?

My database I am working with is very normalized so I ended up creating
views for certain tables which I then use in my XSD schema to select XML
from the DB.
The problem is the generated query does unecessary existence checks because
it doesn't read the underlying table column schema info.. the underlying
column is definately marked "NOT NULL".
...
(((((_Q6.A32 IS NOT NULL AND (_Q6.A32 =
N'11111145-110b-11c4-d8b3-c8c5154c131e') OR _Q6.A32 IS NOT NULL AND (_Q6.A32
= N'11111145-110b-11c4-d8b3-c8c5154c131d')) OR _Q6.A32 IS NOT NULL
...
How do I get rid of this unecessary checks. Surely it slows down performance
of the query.
Hi Joe
This should not be a problem. Since we do not look at the relational schema
when we generate the queries, we do not know whether the column can be null
or not. However, the query optimizier will know about it and this optimize
these expressions away. Thus, there should be no slow down in performance
due to these IS NOT NULL checks...
Best regards
Michael
"Joe" <morbidcamel@.msn.com> wrote in message
news:%23A08G49dFHA.2776@.TK2MSFTNGP10.phx.gbl...
> My database I am working with is very normalized so I ended up creating
> views for certain tables which I then use in my XSD schema to select XML
> from the DB.
> The problem is the generated query does unecessary existence checks
> because
> it doesn't read the underlying table column schema info.. the underlying
> column is definately marked "NOT NULL".
> ...
> (((((_Q6.A32 IS NOT NULL AND (_Q6.A32 =
> N'11111145-110b-11c4-d8b3-c8c5154c131e') OR _Q6.A32 IS NOT NULL AND
> (_Q6.A32
> = N'11111145-110b-11c4-d8b3-c8c5154c131d')) OR _Q6.A32 IS NOT NULL
> ...
> How do I get rid of this unecessary checks. Surely it slows down
> performance
> of the query.
>
>
|||OK, thank you for the advice. I got this information of existence checks
from the following article
http://support.microsoft.com/default...b;en-us;813955
I have another couple of questions though.
Question 1:
Will SQL 2005 support SQLXML natively and will the generated queries use FOR
XML PATH instead of the bulky FOR XML EXPLICIT. I know of the XML columns,
but I don't want to redo my tables necessarily.
Question 2:
I'm running into limitations in terms of performace with my XSD Schema
because there is simply a lot of elements.
I changed my XML format to be attribute centric and also added a lot of
clustered indexes on the keys and selected elements. This speeded up
tremendously. The problem is I have to add more elements in future and the
containment hierarchy is becoming huge. I ended up writting some logic to
query elements in fragments and add children manually. Will this be better
in SQL 2005 (if question 1 is "yes" ofcourse)?
Question 3:
UpdateGrams doesn't work well with identity columns. I ended using GUIDs
instead. This is fine in my current project, but I have another existing
project I want to convert to use SQLXML with .NET serialization which
depends hugely on identity values... will this be improved in future. I
suppose you can always write XSLT to generate clever SQL script or something
as a work-around, but I like the convenience of updategrams. Let MS do the
query generation work

.
Question 4:
I know AS in 2005 uses XML/A. What is the chances in future of mapping XSD
to cubes instead of using XSLT to get the same effect? I don't know if this
is the right group to pose this question though.
Question 5:
SQL queries FOR XML doesn't support XML Attribute Groups, will this be
supported in future? Or is a XSLT the only way to do this as well?
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:Ok6LcyDeFHA.640@.tk2msftngp13.phx.gbl...
> Hi Joe
> This should not be a problem. Since we do not look at the relational
> schema when we generate the queries, we do not know whether the column can
> be null or not. However, the query optimizier will know about it and this
> optimize these expressions away. Thus, there should be no slow down in
> performance due to these IS NOT NULL checks...
> Best regards
> Michael
> "Joe" <morbidcamel@.msn.com> wrote in message
> news:%23A08G49dFHA.2776@.TK2MSFTNGP10.phx.gbl...
>
|||See below for the answers that I know (I am not the owner of the SQLXML
component for a couple of years now).
Best regards
Michael
"Joe" <morbidcamel@.msn.com> wrote in message
news:uc9DJzMeFHA.3620@.TK2MSFTNGP09.phx.gbl...
> OK, thank you for the advice. I got this information of existence checks
> from the following article
> http://support.microsoft.com/default...b;en-us;813955
> I have another couple of questions though.
> Question 1:
> Will SQL 2005 support SQLXML natively and will the generated queries use
> FOR XML PATH instead of the bulky FOR XML EXPLICIT. I know of the XML
> columns, but I don't want to redo my tables necessarily.
>
SQL 2005 will ship with SQLXML 4.0 which is SQLXML 3.0 SP2 with support for
the new datatypes and minus the IIS ISAPI for exposing templates and queries
through IIS (use ASP.Net instead is the recommendation).
I don't know whether they will start using FOR XML PATH, but given the cost
of rewriting, I would assume not.

> Question 2:
> I'm running into limitations in terms of performace with my XSD Schema
> because there is simply a lot of elements.
> I changed my XML format to be attribute centric and also added a lot of
> clustered indexes on the keys and selected elements. This speeded up
> tremendously. The problem is I have to add more elements in future and the
> containment hierarchy is becoming huge. I ended up writting some logic to
> query elements in fragments and add children manually. Will this be better
> in SQL 2005 (if question 1 is "yes" ofcourse)?
I am not sure that I have enough information about your scenario to provide
you good feedback. But I would assume that the SQLXML team is interested in
understanding your scenario and pain points.

> Question 3:
> UpdateGrams doesn't work well with identity columns. I ended using GUIDs
> instead. This is fine in my current project, but I have another existing
> project I want to convert to use SQLXML with .NET serialization which
> depends hugely on identity values... will this be improved in future. I
> suppose you can always write XSLT to generate clever SQL script or
> something as a work-around, but I like the convenience of updategrams. Let
> MS do the query generation work

.
I will pass this along.

> Question 4:
> I know AS in 2005 uses XML/A. What is the chances in future of mapping XSD
> to cubes instead of using XSLT to get the same effect? I don't know if
> this is the right group to pose this question though.
You better suggest that in the AS newsgroup (although I will forward this
request/question).

> Question 5:
> SQL queries FOR XML doesn't support XML Attribute Groups, will this be
> supported in future? Or is a XSLT the only way to do this as well?
I don't quite understand this question. Attribute Groups are an XML schema
concept.
Could you please provide an example?
Thanks
Michael

> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:Ok6LcyDeFHA.640@.tk2msftngp13.phx.gbl...
>
|||Question 1:
There are no plans for using FOR XML PATH for SQL 2005. SQLXML will use FOR
XML EXPLICIT as it did in earlier releases..
Question 2:
In Sql 2005, there are no changes in the way SQLXML generate queries.
For the complexity brought by the elements, if you use simple type elements
in an xsd:sequence block, we will treat them as complex type elements and
generate a select statement for it. This is necessary to preserve the order
of the elements. If you specify the simple type elements in xsd:all group,
it will be treated like an attribute. On the other hand, there are some
other cases, a simple type element might be interpreted as complex type
elements so I would recommend using attributes vs elements.
If your schema is deep and consists of several complex type elements, the
generate FOR XML query will be also big. I couldn't understand how you can
partially generate hierarchies and combine them. Don't you need to find the
relevant parent Xml element to insert the Xml fragments? We would love to
hear more about your solution.
Question 3:
Updategrams have support for identity-columns. You may either use
updg:at-identity :
http://msdn.microsoft.com/library/de...egram_375f.asp
or use the sql:identity annoations in schema to specify the identity
columns.
http://msdn.microsoft.com/library/de...tions_7j03.asp
Let us know if these solutions don't work for you.
Question 4:
Question 5:
Ditto Michael here.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:%23KzUBbTeFHA.2984@.TK2MSFTNGP15.phx.gbl...
> See below for the answers that I know (I am not the owner of the SQLXML
> component for a couple of years now).
> Best regards
> Michael
> "Joe" <morbidcamel@.msn.com> wrote in message
> news:uc9DJzMeFHA.3620@.TK2MSFTNGP09.phx.gbl...
> SQL 2005 will ship with SQLXML 4.0 which is SQLXML 3.0 SP2 with support
> for the new datatypes and minus the IIS ISAPI for exposing templates and
> queries through IIS (use ASP.Net instead is the recommendation).
> I don't know whether they will start using FOR XML PATH, but given the
> cost of rewriting, I would assume not.
>
> I am not sure that I have enough information about your scenario to
> provide you good feedback. But I would assume that the SQLXML team is
> interested in understanding your scenario and pain points.
>
> I will pass this along.
>
> You better suggest that in the AS newsgroup (although I will forward this
> request/question).
>
> I don't quite understand this question. Attribute Groups are an XML schema
> concept.
> Could you please provide an example?
> Thanks
> Michael
>
>
|||I have attached some XML sample. I cannot give you the schema because there
is some traid secrets in there

It is based on the OMG MOF model and is
represented with interfaces and classes. Note that XSLT is used to transform
XML to XMI etc.
When requestig the fragments, they always have a PID (Parent Id) an so doing
I can add it to the parent element's contents. As you can see, the XML can
nest quite deeply. All XML are serialized into classes. The XML can also be
serialized to a DataSet for generating Diffgrams and/or SqlAdaptors... The
problem is, I have to add more model elements to my schema, and when
requesting a <Pkg> (package) element there can be up to 12 different
complex types retrieved.
In my case a typical X-Path query looks like
/Nms[@.ID='f5fde8f9-b359-5eca-71b6-8012ce027c32'] and for multiple elements
/Nms[@.ID='000000ea-00e0-00fa-b0ca-d0bad000eae0' or
@.ID='00000000-00fa-00b0-eae0-cad0bad00001' or
@.ID='e4ecf9eb-a252-4f0b-8a47-5bf305e36d27' or
@.ID='f5fde8f9-b359-5eca-71b6-8012ce027c33']. When the fragment is selected,
an additional query is done to get a collection of light-weight
"descriptors" of parent elements, which in turn is used to construct the
parent elements if they weren't already constructed. SQLXML and .NET
Serializer reduces the ETL time significantly. All I want though is super
fast query times, which up until now was quite impressive. The second
version of my framework is only due in another 6 months so there is still
some time to find a solution and all my hopes is currently on SQL2005.
As far as the attribute groups are involved I am refering to something like
:
<xs:attributeGroup name="XMI.element.att">
<xs:annotation>
<xs:documentation>
XMI.element.att defines the attributes that each XML element
that corresponds to a metamodel class must have to conform to
the XMI specification.
</xs:documentation>
</xs:annotation>
<xs:attribute name="xmi.id" type="xs:ID"/>
<xs:attribute name="xmi.label" type="xs:string"/>
<xs:attribute name="xmi.uuid" type="xs:string"/>
</xs:attributeGroup>
Can SQLXML annotations added to this attribute group be interpreted by the
SQLXML engine.
Here is sample XML requested from SQL.
....
<Nms N="Synap-c" ID="00000000-00fa-00b0-eae0-cad0bad00001"
PID="000000ea-00e0-00fa-b0ca-d0bad000eae0">
<An>SiloFx Synap-c suite of products</An>
<Tag>[Tank].[Synap-c]</Tag>
<NmsC>
<Nms N="Administrator" ID="e4ecf9eb-a252-4f0b-8a47-5bf305e36d27"
PID="00000000-00fa-00b0-eae0-cad0bad00001">
<An>{1}:{2}</An>
<Tag>[Tank].[Synap-c].[Administrator]</Tag>
<NmsC>
<Nms N="EAE" ID="f5fde8f9-b359-5eca-71b6-8012ce027c33"
PID="e4ecf9eb-a252-4f0b-8a47-5bf305e36d27">
<An>SiloFx Synap-c Enterprise Architect Edition (EAE) at
Administrator</An>
<Tag>[Tank].[Synap-c].[Administrator].[EAE]</Tag>
<NmsC>
<Pkg N="Types" ID="f5fde8f9-b359-5eca-71b6-8012ce027f30"
PID="f5fde8f9-b359-5eca-71b6-8012ce027c33" V="Internal" A="false" R="true"
L="false" B="false">
<An>Administrator: Synap-c EAE types used in projects
and ontologies</An>
<PkgC />
</Pkg>
<Nms N="Projects"
ID="f5fde8f9-b359-5eca-71b6-8012ce027c31"
PID="f5fde8f9-b359-5eca-71b6-8012ce027c33">
<An>Administrator: Synap-c EAE types used in projects
and ontologies</An>
<Tag>[Tank].[Synap-c].[Administrator].[EAE].[Projects]</Tag>
<NmsC>
<Pkg N="Application Architecture"
ID="54968e3e-8330-46e2-b2df-4c15e6e30c2f"
PID="f5fde8f9-b359-5eca-71b6-8012ce027c31" V="Public" A="false" R="true"
L="false" B="false">
<PkgC />
</Pkg>
<Pkg N="Application Architecture"
ID="fce438aa-25e4-4f88-bd26-0a92ac82627c"
PID="f5fde8f9-b359-5eca-71b6-8012ce027c31" V="Public" A="false" R="true"
L="false" B="false">
<PkgC />
</Pkg>
<Pkg N="As Is"
ID="9cfe0641-8e53-4586-bd72-d397fd890b28"
PID="f5fde8f9-b359-5eca-71b6-8012ce027c31" V="Public" A="false" R="true"
L="false" B="false">
<PkgC />
</Pkg>
<Pkg N="SCM Organisational Structure"
ID="57deba4b-a86e-4a7c-96b7-4d9a0e0deb09"
PID="f5fde8f9-b359-5eca-71b6-8012ce027c31" V="Public" A="false" R="true"
L="false" B="true">
<PkgC />
</Pkg>
<Pkg N="Application Architecture"
ID="aaf77499-5968-46e3-ab32-887545cfaedd"
PID="f5fde8f9-b359-5eca-71b6-8012ce027c31" V="Public" A="false" R="true"
L="false" B="false">
<PkgC />
</Pkg>
<Pkg N="Application Architecture"
ID="68cd9da0-765d-4958-ba12-95c5ae9e2860"
PID="f5fde8f9-b359-5eca-71b6-8012ce027c31" V="Public" A="false" R="true"
L="false" B="false">
<PkgC />
</Pkg>
<Pkg N="Technical Architecture"
ID="a6e957b1-f5ef-457d-bf68-ec95c9281899"
PID="f5fde8f9-b359-5eca-71b6-8012ce027c31" V="Public" A="false" R="true"
L="false" B="false">
<PkgC />
</Pkg>
<Pkg N="Created on 2005/06/27 08:48:05 AM 872"
ID="555da19a-aa21-4ed4-9694-915f134d48f3"
PID="f5fde8f9-b359-5eca-71b6-8012ce027c31" V="Public" A="false" R="true"
L="false" B="false">
<PkgC>
<Asc N="Links to"
ID="32c627b8-2344-4f7f-a2e6-eeabb61541af"
PID="555da19a-aa21-4ed4-9694-915f134d48f3" V="Private" A="false" R="true"
L="true" AT="DependsOn">
<Tag>PX=0PY=0SW=1SH=1D=8|2|1|1|1|</Tag>
<AscC>
<AsE N="(Shop - Instance 1*)"
ID="c5529649-0672-cae5-7a71-691db0d70c90"
PID="32c627b8-2344-4f7f-a2e6-eeabb61541af"
TID="b75471b1-e5f6-455a-9857-4776c6820dff" C="true" M="1|1|1|0|0" A="None"
Nv="false" OID="b65c7879-2f74-cb4c-78a3-ca5e51774b3a" />
<AsE N="P(Shop - Instance 1*)"
ID="b65c7879-2f74-cb4c-78a3-ca5e51774b3a"
PID="32c627b8-2344-4f7f-a2e6-eeabb61541af"
TID="445a1f81-ccf0-44f3-9a05-e4b5a722ca55" C="true" M="1|1|1|0|0" A="None"
Nv="true" OID="c5529649-0672-cae5-7a71-691db0d70c90" />
</AscC>
</Asc>
<Asc N="Buys goods - Instance"
ID="920109d7-82b3-48ea-900e-a0a0b44a7768"
PID="555da19a-aa21-4ed4-9694-915f134d48f3" V="Private" A="false" R="true"
L="true" AT="DependsOn">
<Tag>PX=0PY=0SW=1SH=1D=8|2|1|1|1|</Tag>
<AscC>
<AsE N="(Buys goods - Instance*)"
ID="361f87ab-4c73-c4f0-6ad3-176260208799"
PID="920109d7-82b3-48ea-900e-a0a0b44a7768"
TID="64de4e3c-8e80-4cda-ba9d-7782942ab0b1" C="true" M="1|1|1|0|0" A="None"
Nv="false" OID="c8cf4d1d-95df-cdce-7ccb-0486e947e2bc" />
<AsE N="P(Buys goods - Instance*)"
ID="c8cf4d1d-95df-cdce-7ccb-0486e947e2bc"
PID="920109d7-82b3-48ea-900e-a0a0b44a7768"
TID="1a8e048a-d72c-45e4-ac85-64e61dcd5594" C="true" M="1|1|1|0|0" A="None"
Nv="true" OID="361f87ab-4c73-c4f0-6ad3-176260208799" />
</AscC>
</Asc>
<Cls N="Shop - Instance 1"
ID="0dae96ef-3e78-48b3-ba27-60d735345725"
PID="555da19a-aa21-4ed4-9694-915f134d48f3" V="Public" A="false" R="false"
L="true" S="false" B="false">
<ClsC />
</Cls>
...
"Bertan ARI [MSFT]" <bertan@.online.microsoft.com> wrote in message
news:Okj2O53eFHA.2556@.TK2MSFTNGP10.phx.gbl...
> Question 1:
> There are no plans for using FOR XML PATH for SQL 2005. SQLXML will use
> FOR XML EXPLICIT as it did in earlier releases..
> Question 2:
> In Sql 2005, there are no changes in the way SQLXML generate queries.
> For the complexity brought by the elements, if you use simple type
> elements in an xsd:sequence block, we will treat them as complex type
> elements and generate a select statement for it. This is necessary to
> preserve the order of the elements. If you specify the simple type
> elements in xsd:all group, it will be treated like an attribute. On the
> other hand, there are some other cases, a simple type element might be
> interpreted as complex type elements so I would recommend using attributes
> vs elements.
> If your schema is deep and consists of several complex type elements, the
> generate FOR XML query will be also big. I couldn't understand how you can
> partially generate hierarchies and combine them. Don't you need to find
> the relevant parent Xml element to insert the Xml fragments? We would love
> to hear more about your solution.
> Question 3:
> Updategrams have support for identity-columns. You may either use
> updg:at-identity :
> http://msdn.microsoft.com/library/de...egram_375f.asp
> or use the sql:identity annoations in schema to specify the identity
> columns.
> http://msdn.microsoft.com/library/de...tions_7j03.asp
> Let us know if these solutions don't work for you.
> Question 4:
> Question 5:
> Ditto Michael here.
> --
> Bertan ARI
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:%23KzUBbTeFHA.2984@.TK2MSFTNGP15.phx.gbl...
>
|||Yes, we do support attribute groups in SqlXml. You can annotate them as
normal attributes and the annotations will be resolved based on the context
of attributegroup ref..
As far as I undertand your technique, you are executing multiple queries to
construct one Xml document.instead of executing one complex query. That
might be useful since the complexity of our FOR XML explicit queries
increases with the square of complex type elements.
Michael may talk about if SQL 2005 will offer solutions that will perform
better in deep hierarchies.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Joe" <morbidcamel@.msn.com> wrote in message
news:eKARm$6eFHA.3280@.TK2MSFTNGP09.phx.gbl...
>I have attached some XML sample. I cannot give you the schema because there
>is some traid secrets in there

It is based on the OMG MOF model and is
>represented with interfaces and classes. Note that XSLT is used to
>transform XML to XMI etc.
> When requestig the fragments, they always have a PID (Parent Id) an so
> doing I can add it to the parent element's contents. As you can see, the
> XML can nest quite deeply. All XML are serialized into classes. The XML
> can also be serialized to a DataSet for generating Diffgrams and/or
> SqlAdaptors... The problem is, I have to add more model elements to my
> schema, and when requesting a <Pkg> (package) element there can be up to
> 12 different complex types retrieved.
> In my case a typical X-Path query looks like
> /Nms[@.ID='f5fde8f9-b359-5eca-71b6-8012ce027c32'] and for multiple elements
> /Nms[@.ID='000000ea-00e0-00fa-b0ca-d0bad000eae0' or
> @.ID='00000000-00fa-00b0-eae0-cad0bad00001' or
> @.ID='e4ecf9eb-a252-4f0b-8a47-5bf305e36d27' or
> @.ID='f5fde8f9-b359-5eca-71b6-8012ce027c33']. When the fragment is
> selected, an additional query is done to get a collection of light-weight
> "descriptors" of parent elements, which in turn is used to construct the
> parent elements if they weren't already constructed. SQLXML and .NET
> Serializer reduces the ETL time significantly. All I want though is super
> fast query times, which up until now was quite impressive. The second
> version of my framework is only due in another 6 months so there is still
> some time to find a solution and all my hopes is currently on SQL2005.
> As far as the attribute groups are involved I am refering to something
> like :
> <xs:attributeGroup name="XMI.element.att">
> <xs:annotation>
> <xs:documentation>
> XMI.element.att defines the attributes that each XML element
> that corresponds to a metamodel class must have to conform to
> the XMI specification.
> </xs:documentation>
> </xs:annotation>
> <xs:attribute name="xmi.id" type="xs:ID"/>
> <xs:attribute name="xmi.label" type="xs:string"/>
> <xs:attribute name="xmi.uuid" type="xs:string"/>
> </xs:attributeGroup>
> Can SQLXML annotations added to this attribute group be interpreted by the
> SQLXML engine.
>
> Here is sample XML requested from SQL.
> ...
> <Nms N="Synap-c" ID="00000000-00fa-00b0-eae0-cad0bad00001"
> PID="000000ea-00e0-00fa-b0ca-d0bad000eae0">
> <An>SiloFx Synap-c suite of products</An>
> <Tag>[Tank].[Synap-c]</Tag>
> <NmsC>
> <Nms N="Administrator" ID="e4ecf9eb-a252-4f0b-8a47-5bf305e36d27"
> PID="00000000-00fa-00b0-eae0-cad0bad00001">
> <An>{1}:{2}</An>
> <Tag>[Tank].[Synap-c].[Administrator]</Tag>
> <NmsC>
> <Nms N="EAE" ID="f5fde8f9-b359-5eca-71b6-8012ce027c33"
> PID="e4ecf9eb-a252-4f0b-8a47-5bf305e36d27">
> <An>SiloFx Synap-c Enterprise Architect Edition (EAE) at
> Administrator</An>
> <Tag>[Tank].[Synap-c].[Administrator].[EAE]</Tag>
> <NmsC>
> <Pkg N="Types" ID="f5fde8f9-b359-5eca-71b6-8012ce027f30"
> PID="f5fde8f9-b359-5eca-71b6-8012ce027c33" V="Internal" A="false" R="true"
> L="false" B="false">
> <An>Administrator: Synap-c EAE types used in projects
> and ontologies</An>
> <PkgC />
> </Pkg>
> <Nms N="Projects"
> ID="f5fde8f9-b359-5eca-71b6-8012ce027c31"
> PID="f5fde8f9-b359-5eca-71b6-8012ce027c33">
> <An>Administrator: Synap-c EAE types used in projects
> and ontologies</An>
> <Tag>[Tank].[Synap-c].[Administrator].[EAE].[Projects]</Tag>
> <NmsC>
> <Pkg N="Application Architecture"
> ID="54968e3e-8330-46e2-b2df-4c15e6e30c2f"
> PID="f5fde8f9-b359-5eca-71b6-8012ce027c31" V="Public" A="false" R="true"
> L="false" B="false">
> <PkgC />
> </Pkg>
> <Pkg N="Application Architecture"
> ID="fce438aa-25e4-4f88-bd26-0a92ac82627c"
> PID="f5fde8f9-b359-5eca-71b6-8012ce027c31" V="Public" A="false" R="true"
> L="false" B="false">
> <PkgC />
> </Pkg>
> <Pkg N="As Is"
> ID="9cfe0641-8e53-4586-bd72-d397fd890b28"
> PID="f5fde8f9-b359-5eca-71b6-8012ce027c31" V="Public" A="false" R="true"
> L="false" B="false">
> <PkgC />
> </Pkg>
> <Pkg N="SCM Organisational Structure"
> ID="57deba4b-a86e-4a7c-96b7-4d9a0e0deb09"
> PID="f5fde8f9-b359-5eca-71b6-8012ce027c31" V="Public" A="false" R="true"
> L="false" B="true">
> <PkgC />
> </Pkg>
> <Pkg N="Application Architecture"
> ID="aaf77499-5968-46e3-ab32-887545cfaedd"
> PID="f5fde8f9-b359-5eca-71b6-8012ce027c31" V="Public" A="false" R="true"
> L="false" B="false">
> <PkgC />
> </Pkg>
> <Pkg N="Application Architecture"
> ID="68cd9da0-765d-4958-ba12-95c5ae9e2860"
> PID="f5fde8f9-b359-5eca-71b6-8012ce027c31" V="Public" A="false" R="true"
> L="false" B="false">
> <PkgC />
> </Pkg>
> <Pkg N="Technical Architecture"
> ID="a6e957b1-f5ef-457d-bf68-ec95c9281899"
> PID="f5fde8f9-b359-5eca-71b6-8012ce027c31" V="Public" A="false" R="true"
> L="false" B="false">
> <PkgC />
> </Pkg>
> <Pkg N="Created on 2005/06/27 08:48:05 AM 872"
> ID="555da19a-aa21-4ed4-9694-915f134d48f3"
> PID="f5fde8f9-b359-5eca-71b6-8012ce027c31" V="Public" A="false" R="true"
> L="false" B="false">
> <PkgC>
> <Asc N="Links to"
> ID="32c627b8-2344-4f7f-a2e6-eeabb61541af"
> PID="555da19a-aa21-4ed4-9694-915f134d48f3" V="Private" A="false" R="true"
> L="true" AT="DependsOn">
> <Tag>PX=0PY=0SW=1SH=1D=8|2|1|1|1|</Tag>
> <AscC>
> <AsE N="(Shop - Instance 1*)"
> ID="c5529649-0672-cae5-7a71-691db0d70c90"
> PID="32c627b8-2344-4f7f-a2e6-eeabb61541af"
> TID="b75471b1-e5f6-455a-9857-4776c6820dff" C="true" M="1|1|1|0|0" A="None"
> Nv="false" OID="b65c7879-2f74-cb4c-78a3-ca5e51774b3a" />
> <AsE N="P(Shop - Instance 1*)"
> ID="b65c7879-2f74-cb4c-78a3-ca5e51774b3a"
> PID="32c627b8-2344-4f7f-a2e6-eeabb61541af"
> TID="445a1f81-ccf0-44f3-9a05-e4b5a722ca55" C="true" M="1|1|1|0|0" A="None"
> Nv="true" OID="c5529649-0672-cae5-7a71-691db0d70c90" />
> </AscC>
> </Asc>
> <Asc N="Buys goods - Instance"
> ID="920109d7-82b3-48ea-900e-a0a0b44a7768"
> PID="555da19a-aa21-4ed4-9694-915f134d48f3" V="Private" A="false" R="true"
> L="true" AT="DependsOn">
> <Tag>PX=0PY=0SW=1SH=1D=8|2|1|1|1|</Tag>
> <AscC>
> <AsE N="(Buys goods - Instance*)"
> ID="361f87ab-4c73-c4f0-6ad3-176260208799"
> PID="920109d7-82b3-48ea-900e-a0a0b44a7768"
> TID="64de4e3c-8e80-4cda-ba9d-7782942ab0b1" C="true" M="1|1|1|0|0" A="None"
> Nv="false" OID="c8cf4d1d-95df-cdce-7ccb-0486e947e2bc" />
> <AsE N="P(Buys goods - Instance*)"
> ID="c8cf4d1d-95df-cdce-7ccb-0486e947e2bc"
> PID="920109d7-82b3-48ea-900e-a0a0b44a7768"
> TID="1a8e048a-d72c-45e4-ac85-64e61dcd5594" C="true" M="1|1|1|0|0" A="None"
> Nv="true" OID="361f87ab-4c73-c4f0-6ad3-176260208799" />
> </AscC>
> </Asc>
> <Cls N="Shop - Instance 1"
> ID="0dae96ef-3e78-48b3-ba27-60d735345725"
> PID="555da19a-aa21-4ed4-9694-915f134d48f3" V="Public" A="false" R="false"
> L="true" S="false" B="false">
> <ClsC />
> </Cls>
> ...
> "Bertan ARI [MSFT]" <bertan@.online.microsoft.com> wrote in message
> news:Okj2O53eFHA.2556@.TK2MSFTNGP10.phx.gbl...
>
|||In SQL Server 2005 you can compose FOR XML queries and we introduced the new
FOR XML PATH mode (see
http://msdn.microsoft.com/library/en.../forxml2k5.asp) that
should make it much easier to author complex hierarchies. Note however, that
while EXPLICIT mode is more complex to write and maintain, in some cases, it
may perform faster since it "XMLifies" the tree only once at the end. The
processes that create intermediate results that then are composed together
may often perform slower.
HTH
Michael
"Bertan ARI [MSFT]" <bertan@.online.microsoft.com> wrote in message
news:ONUxR$GfFHA.2156@.TK2MSFTNGP14.phx.gbl...
> Yes, we do support attribute groups in SqlXml. You can annotate them as
> normal attributes and the annotations will be resolved based on the
> context of attributegroup ref..
> As far as I undertand your technique, you are executing multiple queries
> to construct one Xml document.instead of executing one complex query. That
> might be useful since the complexity of our FOR XML explicit queries
> increases with the square of complex type elements.
> Michael may talk about if SQL 2005 will offer solutions that will perform
> better in deep hierarchies.
> --
> Bertan ARI
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Joe" <morbidcamel@.msn.com> wrote in message
> news:eKARm$6eFHA.3280@.TK2MSFTNGP09.phx.gbl...
>
|||I thank you for the input. I am busy porting some of the logic to SQLServer
2005. I think using the XML PATH is going to do the trick for me.
This will mean I don't have to have views to get it into the right format,
some of the views uses the same tables, thus I can use reuse one table in
my query,
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:O7AmR3NfFHA.3780@.TK2MSFTNGP10.phx.gbl...
> In SQL Server 2005 you can compose FOR XML queries and we introduced the
> new FOR XML PATH mode (see
> http://msdn.microsoft.com/library/en.../forxml2k5.asp) that
> should make it much easier to author complex hierarchies. Note however,
> that while EXPLICIT mode is more complex to write and maintain, in some
> cases, it may perform faster since it "XMLifies" the tree only once at the
> end. The processes that create intermediate results that then are composed
> together may often perform slower.
> HTH
> Michael
> "Bertan ARI [MSFT]" <bertan@.online.microsoft.com> wrote in message
> news:ONUxR$GfFHA.2156@.TK2MSFTNGP14.phx.gbl...
>
|||Cool, thanks. Feel free to share your feedback on how it went and how the
FOR XML PATH works.
Best regards
Michael
"Joe" <morbidcamel@.msn.com> wrote in message
news:eZxGtU4hFHA.576@.tk2msftngp13.phx.gbl...
>I thank you for the input. I am busy porting some of the logic to SQLServer
>2005. I think using the XML PATH is going to do the trick for me.
> This will mean I don't have to have views to get it into the right format,
> some of the views uses the same tables, thus I can use reuse one table in
> my query,
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:O7AmR3NfFHA.3780@.TK2MSFTNGP10.phx.gbl...
>

Friday, February 24, 2012

annotated schema and bulk loading into multiple tables...

Hi guys,
I'm trying to get XML data loaded into a set of tables using bulk load. The
child tables also have an XML column where I want to store portions of the
XML.
So far the I've got it correctly inserting data into the parent, and able to
insert the correct amount of rows in the child tables, but the data in the
child tables is empty... The child data is an identity column, a foreign key
pointing back to the parent row (empty!), and an XML data column holding the
contents of the xml fragment (also empty!).
Any help would be greatly appreciated!
Thanks.
Daniel.
Below is where I've got to so far...
SQL definitions for my parent table and a child table:
----
CREATE TABLE [dbo].[Foo](
[Foo_PK] [int] IDENTITY(1,1) NOT NULL,
[StartDateTime] [datetime] NULL,
[EndDateTime] [datetime] NULL,
CONSTRAINT [PK_Foo] PRIMARY KEY CLUSTERED
(
[Foo_PK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[ChildOfFoo](
[ChildOfFoo_PK] [int] IDENTITY(1,1) NOT NULL,
[Foo_FK] [int] NULL,
[Data] [xml] NULL,
CONSTRAINT [PK_ChildOfFoo] PRIMARY KEY CLUSTERED
(
[ChildOfFoo_PK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Here's the annotated XSD:
----
<?xml version="1.0" encoding="utf-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xs:annotation>
<xs:appinfo>

<sql:relationship name="HeaderWash"
parent="Foo"
parent-key="Foo_PK"
child="ChildOfFoo"
child-key="Foo_FK" />
</xs:appinfo>
</xs:annotation>
<xs:element name="Root" sql:is-constant="1">
<xs:complexType>
<xs:sequence>

<xs:element name="Header"
sql:relation="Foo"
sql:key-fields="Foo_PK">
<xs:complexType>
<xs:sequence>
<xs:element name="Foo_PK" type="xs:integer"
minOccurs="0"
default="0" sql:identity="ignore"/>
<xs:element name="StartDateTime"
type="xs:dateTime" />
<xs:element name="EndDateTime"
type="xs:dateTime" />
</xs:sequence>
</xs:complexType>
</xs:element>

<xs:element name="Children" sql:is-constant="1">
<xs:complexType>
<xs:sequence>
<xs:element maxOccurs="unbounded" name="Wash"
sql:relation="ChildOfFoo"
sql:key-fields="ChildOfFoo_PK"
sql:relationship="HeaderWash">
<xs:complexType>
<xs:sequence>
<xs:element name="ChildOfFoo_PK"
type="xs:string"
minOccurs="0"
default="0" sql:identity="ignore" />
<xs:element name="Foo_FK"
type="xs:string" />
<xs:element name="Wash"
sql:field="Data" sql:datatype="xml" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
And finally, (thanks for your patience!), a sample of XML data...
----
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<Root>
<Header>
<StartDateTime>15 Jun 2007 08:07:00</StartDateTime>
<EndDateTime>15 Jun 2007 09:12:00</EndDateTime>
</Header>
<Children>

<ChildOfFoo>
<Phasetime>02:00</Phasetime>
<Heated>No</Heated>
</ChildOfFoo>
<ChildOfFoo>
<Quantityinjected>22.8 ml</Quantityinjected>
<Lowerlimit>61.1 ml</Lowerlimit>
<Upperlimit>82.7 ml</Upperlimit>
</ChildOfFoo>
<ChildOfFoo>
<Watertype>Cold water</Watertype>
<Numberofstages>1</Numberofstages>
</ChildOfFoo>
<ChildOfFoo>
<Quantityinjected>137.6 ml</Quantityinjected>
<Lowerlimit>122.3 ml</Lowerlimit>
<Upperlimit>165.4 ml</Upperlimit>
</ChildOfFoo>
<ChildOfFoo>
<Circulationpressure>Detected</Circulationpressure>
</ChildOfFoo>
<ChildOfFoo>
<Heated>No</Heated>
<Circulationpressure>Detected</Circulationpressure>
</ChildOfFoo>
</Children>
</Root>In case anyone elase wants this solution... The schema is modified as such:
1. When referencing a foreign key (as in the relationship) it needs to stay
in scope from when it is initially populated by the parent table, to each
time it is used. If you get a foreign key of null, you are most probably
using the FK out of scope.
2. Use the sql:overflow-field="<MyXmlColumn>" in the element that relates to
the table you want to store as XML. The sql:overflow-field field takes all
unreferenced XML in that node and stuffs it into the field you specify.
<?xml version="1.0" encoding="utf-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xs:annotation>
<xs:appinfo>

<sql:relationship name="HeaderWash"
parent="Foo"
parent-key="Foo_PK"
child="ChildOfFoo"
child-key="Foo_FK" />
</xs:appinfo>
</xs:annotation>
<xs:element name="Root" sql:is-constant="1">
<xs:complexType>
<xs:sequence>

<xs:element name="Foo_PK" type="xs:integer" minOccurs="0"
default="0" sql:identity="ignore"/>
<xs:element name="StartDateTime" type="xs:dateTime" />
<xs:element name="EndDateTime" type="xs:dateTime" />

<xs:element name="Children" sql:is-constant="1">
<xs:complexType>
<xs:sequence>
<xs:element maxOccurs="unbounded" name="Wash"
sql:relation="ChildOfFoo"
sql:key-fields="ChildOfFoo_PK"
sql:relationship="HeaderWash"
sql:overflow-field="Data">
<xs:complexType>
<xs:sequence>
<xs:element name="ChildOfFoo_PK"
type="xs:string"
minOccurs="0" default="0"
sql:identity="ignore" />
<xs:element name="Foo_FK"
type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

> <?xml version="1.0" encoding="utf-8"?>
> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xs:annotation>
> <xs:appinfo>
> 
> <sql:relationship name="HeaderWash"
> parent="Foo"
> parent-key="Foo_PK"
> child="ChildOfFoo"
> child-key="Foo_FK" />
> </xs:appinfo>
> </xs:annotation>
> <xs:element name="Root" sql:is-constant="1">
> <xs:complexType>
> <xs:sequence>
> 
> <xs:element name="Header"
> sql:relation="Foo"
> sql:key-fields="Foo_PK">
> <xs:complexType>
> <xs:sequence>
> <xs:element name="Foo_PK" type="xs:integer"
> minOccurs="0"
> default="0" sql:identity="ignore"/>
> <xs:element name="StartDateTime"
> type="xs:dateTime" />
> <xs:element name="EndDateTime"
> type="xs:dateTime" />
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> 
> <xs:element name="Children" sql:is-constant="1">
> <xs:complexType>
> <xs:sequence>
> <xs:element maxOccurs="unbounded" name="Wash"
> sql:relation="ChildOfFoo"
> sql:key-fields="ChildOfFoo_PK"
> sql:relationship="HeaderWash">
> <xs:complexType>
> <xs:sequence>
> <xs:element name="ChildOfFoo_PK"
> type="xs:string"
> minOccurs="0"
> default="0" sql:identity="ignore" />
> <xs:element name="Foo_FK"
> type="xs:string" />
> <xs:element name="Wash"
> sql:field="Data" sql:datatype="xml" />
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> </xs:schema>
"Daniel Bass" <danREMOVEbass@.blueCAPSbottle.comFIRST> wrote in message
news:%23gNnsRHxHHA.600@.TK2MSFTNGP05.phx.gbl...
> Hi guys,
> I'm trying to get XML data loaded into a set of tables using bulk load.
> The child tables also have an XML column where I want to store portions of
> the XML.
> So far the I've got it correctly inserting data into the parent, and able
> to insert the correct amount of rows in the child tables, but the data in
> the child tables is empty... The child data is an identity column, a
> foreign key pointing back to the parent row (empty!), and an XML data
> column holding the contents of the xml fragment (also empty!).
> Any help would be greatly appreciated!
> Thanks.
> Daniel.
>
>
> Below is where I've got to so far...
> SQL definitions for my parent table and a child table:
> ----
> CREATE TABLE [dbo].[Foo](
> [Foo_PK] [int] IDENTITY(1,1) NOT NULL,
> [StartDateTime] [datetime] NULL,
> [EndDateTime] [datetime] NULL,
> CONSTRAINT [PK_Foo] PRIMARY KEY CLUSTERED
> (
> [Foo_PK] ASC
> )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
> OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[ChildOfFoo](
> [ChildOfFoo_PK] [int] IDENTITY(1,1) NOT NULL,
> [Foo_FK] [int] NULL,
> [Data] [xml] NULL,
> CONSTRAINT [PK_ChildOfFoo] PRIMARY KEY CLUSTERED
> (
> [ChildOfFoo_PK] ASC
> )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
> OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
> ) ON [PRIMARY]
>
>
> Here's the annotated XSD:
> ----
> <?xml version="1.0" encoding="utf-8"?>
> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xs:annotation>
> <xs:appinfo>
> 
> <sql:relationship name="HeaderWash"
> parent="Foo"
> parent-key="Foo_PK"
> child="ChildOfFoo"
> child-key="Foo_FK" />
> </xs:appinfo>
> </xs:annotation>
> <xs:element name="Root" sql:is-constant="1">
> <xs:complexType>
> <xs:sequence>
> 
> <xs:element name="Header"
> sql:relation="Foo"
> sql:key-fields="Foo_PK">
> <xs:complexType>
> <xs:sequence>
> <xs:element name="Foo_PK" type="xs:integer"
> minOccurs="0"
> default="0" sql:identity="ignore"/>
> <xs:element name="StartDateTime"
> type="xs:dateTime" />
> <xs:element name="EndDateTime"
> type="xs:dateTime" />
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> 
> <xs:element name="Children" sql:is-constant="1">
> <xs:complexType>
> <xs:sequence>
> <xs:element maxOccurs="unbounded" name="Wash"
> sql:relation="ChildOfFoo"
> sql:key-fields="ChildOfFoo_PK"
> sql:relationship="HeaderWash">
> <xs:complexType>
> <xs:sequence>
> <xs:element name="ChildOfFoo_PK"
> type="xs:string"
> minOccurs="0"
> default="0" sql:identity="ignore" />
> <xs:element name="Foo_FK"
> type="xs:string" />
> <xs:element name="Wash"
> sql:field="Data" sql:datatype="xml" />
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> </xs:schema>
>
> And finally, (thanks for your patience!), a sample of XML data...
> ----
> <?xml version="1.0" encoding="UTF-8" standalone="no"?>
> <Root>
> <Header>
> <StartDateTime>15 Jun 2007 08:07:00</StartDateTime>
> <EndDateTime>15 Jun 2007 09:12:00</EndDateTime>
> </Header>
> <Children>
> 
> <ChildOfFoo>
> <Phasetime>02:00</Phasetime>
> <Heated>No</Heated>
> </ChildOfFoo>
> <ChildOfFoo>
> <Quantityinjected>22.8 ml</Quantityinjected>
> <Lowerlimit>61.1 ml</Lowerlimit>
> <Upperlimit>82.7 ml</Upperlimit>
> </ChildOfFoo>
> <ChildOfFoo>
> <Watertype>Cold water</Watertype>
> <Numberofstages>1</Numberofstages>
> </ChildOfFoo>
> <ChildOfFoo>
> <Quantityinjected>137.6 ml</Quantityinjected>
> <Lowerlimit>122.3 ml</Lowerlimit>
> <Upperlimit>165.4 ml</Upperlimit>
> </ChildOfFoo>
> <ChildOfFoo>
> <Circulationpressure>Detected</Circulationpressure>
> </ChildOfFoo>
> <ChildOfFoo>
> <Heated>No</Heated>
> <Circulationpressure>Detected</Circulationpressure>
> </ChildOfFoo>
> </Children>
> </Root>
>

AND and One to many relationships

I have two tables in a one to many relationship, customers and invoices. The
invoices can be a variety of types marked by an invoice flag column (for
instance 1, 2 or 3). I want to retrieve customers, which only have invoices
of 1 AND 2. If there is an invoice of type 3 I don't want the customer
returned. How do I do this SQLSELECT <column list>
FROM Customers c
WHERE EXISTS
(SELECT NULL FROM Invoices i
WHERE c.CustomerID = i.CustomerID
AND i.InvoiceType IN (1,2))
--
Jacco Schalkwijk
SQL Server MVP
"Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
news:eA0P$hiKFHA.1308@.TK2MSFTNGP15.phx.gbl...
>I have two tables in a one to many relationship, customers and invoices.
>The invoices can be a variety of types marked by an invoice flag column
>(for instance 1, 2 or 3). I want to retrieve customers, which only have
>invoices of 1 AND 2. If there is an invoice of type 3 I don't want the
>customer returned. How do I do this SQL
>|||I can't get this to work. If the customer has invoices of type 1 and 2 I
want the customer returned. If i.InvoiceType IN (1,2) in the query and
customer has an invoice type of 3 I don't want the customer returned.
Does this make sense. Thanks.
SELECT <column list>
FROM Customers c
WHERE EXISTS
(SELECT NULL FROM Invoices i
WHERE c.CustomerID = i.CustomerID
AND i.InvoiceType IN (1,2))
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23CPdaBjKFHA.3332@.TK2MSFTNGP15.phx.gbl...
> SELECT <column list>
> FROM Customers c
> WHERE EXISTS
> (SELECT NULL FROM Invoices i
> WHERE c.CustomerID = i.CustomerID
> AND i.InvoiceType IN (1,2))
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
> news:eA0P$hiKFHA.1308@.TK2MSFTNGP15.phx.gbl...
>>I have two tables in a one to many relationship, customers and invoices.
>>The invoices can be a variety of types marked by an invoice flag column
>>(for instance 1, 2 or 3). I want to retrieve customers, which only have
>>invoices of 1 AND 2. If there is an invoice of type 3 I don't want the
>>customer returned. How do I do this SQL
>|||SELECT <column list>
FROM Customers c
WHERE EXISTS
(SELECT NULL FROM Invoices i
WHERE c.CustomerID = i.CustomerID
AND i.InvoiceType IN (1,2))
AND NOT EXISTS
(SELECT NULL FROM Invoices i
WHERE c.CustomerID = i.CustomerID
AND i.InvoiceType = 3)
?
That gives you customers that have invoice with type either 1 or 2 but don't
have invoices of type 3.
--
Jacco Schalkwijk
SQL Server MVP
"Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
news:u8eMEMjKFHA.2852@.TK2MSFTNGP14.phx.gbl...
>I can't get this to work. If the customer has invoices of type 1 and 2 I
>want the customer returned. If i.InvoiceType IN (1,2) in the query and
>customer has an invoice type of 3 I don't want the customer returned.
> Does this make sense. Thanks.
> SELECT <column list>
> FROM Customers c
> WHERE EXISTS
> (SELECT NULL FROM Invoices i
> WHERE c.CustomerID = i.CustomerID
> AND i.InvoiceType IN (1,2))
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> wrote in message news:%23CPdaBjKFHA.3332@.TK2MSFTNGP15.phx.gbl...
>> SELECT <column list>
>> FROM Customers c
>> WHERE EXISTS
>> (SELECT NULL FROM Invoices i
>> WHERE c.CustomerID = i.CustomerID
>> AND i.InvoiceType IN (1,2))
>> --
>> Jacco Schalkwijk
>> SQL Server MVP
>>
>> "Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
>> news:eA0P$hiKFHA.1308@.TK2MSFTNGP15.phx.gbl...
>>I have two tables in a one to many relationship, customers and invoices.
>>The invoices can be a variety of types marked by an invoice flag column
>>(for instance 1, 2 or 3). I want to retrieve customers, which only have
>>invoices of 1 AND 2. If there is an invoice of type 3 I don't want the
>>customer returned. How do I do this SQL
>>
>|||select c.*
from customers c
where c.customerid not in (select customerid from invoices where
invoicetype = 3)
if you need other data from the invoice table then just do a join to
it.
i am presuming in this example that customers can have all three types,
but will have the same customerid on each row in the invoice table,
thus if you exclude the customers on an id level, then it will exclude
the ones that have any combination of 3 (1-2-3, 1-3, 2-3, 3)
hth,
hans|||Hi
As I have understood you need the list of customers who have invoices of
type 1 AND 2 so we can't use IN keyword because it is actually a type of OR.
Here is the query (I assumed invoice Type field is char):
select distinct A.idcustomer from
(select idinvoice,idcustomer from tblinvoice where type='1' ) A inner join
(select idinvoice,idcustomer from tblinvoice where type='2') B on
A.idcustomer=B.idcustomer
and not exists (select 'true' from tblinvoice C where
C.idcustomer=A.idcustomer and C.type not in ('1','2'))|||Hi this is going in the right direction. To complicate things would there be
any way to make the query more dynamic. Say there were 5 invoices types and
I need to show customers with invoices of invoice type 1 AND 3 but then
wanted to narrow it down even further to 1 AND 3 AND 5 and so on.
"Reza" <Reza@.discussions.microsoft.com> wrote in message
news:EB715DA5-F950-41C5-9C1C-F9B616926561@.microsoft.com...
> Hi
> As I have understood you need the list of customers who have invoices of
> type 1 AND 2 so we can't use IN keyword because it is actually a type of
> OR.
> Here is the query (I assumed invoice Type field is char):
> select distinct A.idcustomer from
> (select idinvoice,idcustomer from tblinvoice where type='1' ) A inner join
> (select idinvoice,idcustomer from tblinvoice where type='2') B on
> A.idcustomer=B.idcustomer
> and not exists (select 'true' from tblinvoice C where
> C.idcustomer=A.idcustomer and C.type not in ('1','2'))|||On Thu, 17 Mar 2005 14:12:34 -0000, Chris Kennedy wrote:
>Hi this is going in the right direction. To complicate things would there be
>any way to make the query more dynamic. Say there were 5 invoices types and
>I need to show customers with invoices of invoice type 1 AND 3 but then
>wanted to narrow it down even further to 1 AND 3 AND 5 and so on.
Hi Chris,
For full flexibility, create an extra table to hold the types you need.
Then, to find all customers with all requested types of invoice plus
possibly others, use
SELECT i.CustomerID
FROM (SELECT DISTINCT CustomerID, Type
FROM Invoices) AS i
INNER JOIN TypesWanted AS t
ON t.Type = i.Type
GROUP BY i.CustomerID
HAVING COUNT(*) = (SELECT COUNT(*) FROM TypesWanted)
And to find all customers with all requested types of invoice, but no
others, you change this to
SELECT i.CustomerID
FROM (SELECT DISTINCT CustomerID, Type
FROM Invoices) AS i
LEFT JOIN TypesWanted AS t
ON t.Type = i.Type
GROUP BY i.CustomerID
HAVING COUNT(*) = (SELECT COUNT(*) FROM TypesWanted)
AND COUNT(*) = COUNT(t.Type)
Both above queries are untested. Post CREATE TABLE and INSERT statements
with test data if you want a tested solution.
>"Reza" <Reza@.discussions.microsoft.com> wrote in message
>news:EB715DA5-F950-41C5-9C1C-F9B616926561@.microsoft.com...
>> Hi
>> As I have understood you need the list of customers who have invoices of
>> type 1 AND 2 so we can't use IN keyword because it is actually a type of
>> OR.
>> Here is the query (I assumed invoice Type field is char):
>> select distinct A.idcustomer from
>> (select idinvoice,idcustomer from tblinvoice where type='1' ) A inner join
>> (select idinvoice,idcustomer from tblinvoice where type='2') B on
>> A.idcustomer=B.idcustomer
>> and not exists (select 'true' from tblinvoice C where
>> C.idcustomer=A.idcustomer and C.type not in ('1','2'))
>
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Where does the Types wanted table come from. Is this like a many to many
relationship with customers having many invoices and invoices types having
many invoices?
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:2j4j31lmk3p78au3eookj29outfd7uaei2@.4ax.com...
> On Thu, 17 Mar 2005 14:12:34 -0000, Chris Kennedy wrote:
>>Hi this is going in the right direction. To complicate things would there
>>be
>>any way to make the query more dynamic. Say there were 5 invoices types
>>and
>>I need to show customers with invoices of invoice type 1 AND 3 but then
>>wanted to narrow it down even further to 1 AND 3 AND 5 and so on.
> Hi Chris,
> For full flexibility, create an extra table to hold the types you need.
> Then, to find all customers with all requested types of invoice plus
> possibly others, use
> SELECT i.CustomerID
> FROM (SELECT DISTINCT CustomerID, Type
> FROM Invoices) AS i
> INNER JOIN TypesWanted AS t
> ON t.Type = i.Type
> GROUP BY i.CustomerID
> HAVING COUNT(*) = (SELECT COUNT(*) FROM TypesWanted)
> And to find all customers with all requested types of invoice, but no
> others, you change this to
> SELECT i.CustomerID
> FROM (SELECT DISTINCT CustomerID, Type
> FROM Invoices) AS i
> LEFT JOIN TypesWanted AS t
> ON t.Type = i.Type
> GROUP BY i.CustomerID
> HAVING COUNT(*) = (SELECT COUNT(*) FROM TypesWanted)
> AND COUNT(*) = COUNT(t.Type)
> Both above queries are untested. Post CREATE TABLE and INSERT statements
> with test data if you want a tested solution.
>
>>"Reza" <Reza@.discussions.microsoft.com> wrote in message
>>news:EB715DA5-F950-41C5-9C1C-F9B616926561@.microsoft.com...
>> Hi
>> As I have understood you need the list of customers who have invoices of
>> type 1 AND 2 so we can't use IN keyword because it is actually a type of
>> OR.
>> Here is the query (I assumed invoice Type field is char):
>> select distinct A.idcustomer from
>> (select idinvoice,idcustomer from tblinvoice where type='1' ) A inner
>> join
>> (select idinvoice,idcustomer from tblinvoice where type='2') B on
>> A.idcustomer=B.idcustomer
>> and not exists (select 'true' from tblinvoice C where
>> C.idcustomer=A.idcustomer and C.type not in ('1','2'))
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Thu, 17 Mar 2005 16:16:24 -0000, Chris Kennedy wrote:
>Where does the Types wanted table come from. Is this like a many to many
>relationship with customers having many invoices and invoices types having
>many invoices?
Hi Chris,
The TypesWanted table is where you (temporarily) store the types you are
looking for. Your question was:
>>I need to show customers with invoices of invoice type 1 AND 3 but then
>>wanted to narrow it down even further to 1 AND 3 AND 5 and so on.
To find customer with types 1 and 3 and 5, just run
DELETE FROM TypesWanted; -- No where - everything gets dispatched of.
INSERT TypesWanted (Type) VALUE(1);
INSERT TypesWanted (Type) VALUE(3);
INSERT TypesWanted (Type) VALUE(5);
And then run the query.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Works great. Cheers.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:e23k311ik0teriodjc3ik2u6jie07kh8j4@.4ax.com...
> On Thu, 17 Mar 2005 16:16:24 -0000, Chris Kennedy wrote:
>>Where does the Types wanted table come from. Is this like a many to many
>>relationship with customers having many invoices and invoices types having
>>many invoices?
> Hi Chris,
> The TypesWanted table is where you (temporarily) store the types you are
> looking for. Your question was:
>>I need to show customers with invoices of invoice type 1 AND 3 but then
>>wanted to narrow it down even further to 1 AND 3 AND 5 and so on.
> To find customer with types 1 and 3 and 5, just run
> DELETE FROM TypesWanted; -- No where - everything gets dispatched of.
> INSERT TypesWanted (Type) VALUE(1);
> INSERT TypesWanted (Type) VALUE(3);
> INSERT TypesWanted (Type) VALUE(5);
> And then run the query.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Thursday, February 16, 2012

Analysis: How to put this in tables?

Hi,
I have some question:
How do I put the folowing stuff into tables:
210004000231023551079690000463 1000000000010000230204841010060
2302040370100
23000400020000000000000000000000 BANQUE ACCORD
59110 LA MADELEINE FR 000
210004000331023551079690000463 1000000000022000230204841010060
2302040370100
23000400030000000000000000000000 BANQUE ACCORD
59110 LA MADELEINE FR 000
210004000431023551079690000463 1000000000008000230204841010060
2302040370100
23000400040000000000000000000000 BANQUE ACCORD
59110 LA MADELEINE FR 000
210004000531023551079690000463 1000000000008400230204841010110
2302040371100
23000400050000000000000000000000 BANQUE ACCORD
59110 LA MADELEINE FR 001
310004000631023551079690000463 84101000100430003 01100 00001408804 11
0 1
310004000731023551079690000463 841010001001BANQUE ACCORD
0 1
310004000831023551079690000463 841010001001RUE JEANNE MAILLOTTE 4/6
0 1
310004000931023551079690000463 84101000100159110 LA MADELEINE FR
0 1
310004001031023551079690000463 841010001002COM 2EME SEM
0 1
310004001131023551079690000463 841010001005SOGEFRPPLLM
0 1
310004001231023551079690000463 841010000 Pi ce justificative en annexe
Votre ordre de paiemen t de 1 0
3200040012 22.000,00 EUR R f rences de l'op ration:
H1040223K007969 Frais du ba nquier correspon 1 0
3300040012dant 1 0,00 EUR Commission de
paiement 0 1
It's really simple:
- 21-records: these are records beginning with "21": they are the
'main'-records containing the information
- 22 and 23-records starting with 22 or 23: details of the 21-record
- 31, 32, 33-records: also details of the 21-records.
positions in 21-record:
- On position 35 I have an indiciation of Credit/Debit
- 83-90: Coding of the 21-record
- 91: Strucutred or Non-Structured.
-> I need these 3 things to identify in some way the 21-record.
Now my question is: I have to put all this stuf in tables, and I need to be
able to do easily and fast query's with it.
I guess the most performant way would be to put every record in it's own
kidn of table: so I would create an tbl21, tbl22, tbl23, tbl31, etc Liek
that I would be able to put the Credit/Debit, Coding etc in there own Column
and than I can do some fast query's on it.
But on the other hand: When I put everything in different tables, it will be
more difficult to have a nice overview of the whole file (21 + 22 + 23 +
... ), and to be able to know which cmae after which one etc.
So I thought about putting everything in one table.
like this:
CREATE TABLE [dbo].[tblExtractChild128] (
[ExtractID] [int] NOT NULL , -> unique ID
[ParentID] [int] NULL , -> indicates which is
the
Parent of the 22, 23, 31-record (so the ID of the 21-record)
[Identification] [varchar] (2) COLLATE French_CI_AS NULL , ->
"21",
"22", "23", "31", "32", "33": to easily see which type of record it is
[Text] [varchar] (128) COLLATE French_CI_AS NULL , ->
contains the whole Line ("210004000231023551079690000463
1000000000010000230204841010060
2302040370100"
[CD] [bit] NULL
-> position 35 of the 21-line: Credit/Debit
[Amount] [decimal](18, 0) NULL
-> Amount = value in EURO of the record
[Coding] [varchar] (8) COLLATE French_CI_AS NULL , ->
83-90:
Coding of the 21-record
[Structured] [bit]
-> 91: Strucutred or Non-Structured.
) ON [PRIMARY]
GO
In which I put all the records, and for the 21-records I use the Columns CD,
Coding and Structured, for the others I don't.
Like this I can do query's:
SELECT DISTINCT r21.*
FROM tblExtractChild128 r21 LEFT JOIN tblExtractChild128 r23 ON (r21.ID =
r23.ParentID)
LEFT JOIN tblExtractChild128 r31 ON (r21.ID = r31.ParentID)
WHERE (r21.Identification = '21')
AND (r21.Coding= '84101006')
AND (r22.Text LIKE '%BANQUE ACCORD%')
AND (r31.Text LIKE '%RUE JEANNE MAILLOTTE%')
Ok, this works: but I have my doubts if this is really the best way to do
all this stuff. Is there a way which will have much more performance? Work
nicer, more easily for maintenance etc?
For those who read this untill the end and try to formulate an answer:
thanks a lot! hehe :-)
PieterPieter,
You may want to start explaining what this data is all about. Then start
identifying what the entity types, their attributes and relationships among
them. Without such information, it is hard to provide any worthy
suggestions.
Anith

Monday, March 19, 2012

Another Temporary Table Q

Another SUM limitation problem

Another SQL Query!

Wednesday, March 7, 2012

another complex query

Saturday, February 25, 2012

Annotated schema with views?

Friday, February 24, 2012

annotated schema and bulk loading into multiple tables...

AND and One to many relationships

AND and One to many relationships

AND and One to many relationships

Thursday, February 16, 2012

Analysis: How to put this in tables?

SQL server connection

Blog Archive

About Me