How to simplify XML Query? (SQL2000)

Hi, I am trying to write a query in SQL 2000 to produce a slightly more than
just basic xml schema. I can do the query using FOR XML EXPLICIT, but as the
schema that I want to produce is quite large (with a reasonable amount of
repitition) this seems unnecessarily complicated. A simplified version of the
schema I want to produce is:
<ArrayOfEntity>
<Entity Key=123>
<Name>...</Name>
<Prop1>...</Prop1>
<Prop2>...</Prop2>
<Prop3>...</Prop3>
<Node1 Key=32>text</Node1>
<Node2 Key=43>text</Node2>
<Node3 Key=54>text</Node3>
<Node4 Key=65>text</Node4>
</Entity>
<Entity Key=234>
...
</Entity>
</ArrayOfEntity>
The thing is, there are quite a few of these child "Node"s, which will only
contain a key and text value. There are also more complicated child nodes
(not shown here).
I was quite excited to find the [Element!Num!Attr!xml] explicit column name,
because I could then write a function to generate the child node. But this
does not seem to allow me to specify the Node Key's!!
Is there any way to write a reasonably simple query to get the above schema?
Thanks
Greg
Hi Greg
You will need to use the FOR XML EXPLICIT option if you wish to retrieve the
data in this format. A different approach you be to apply a transform after
the data has been extracted. You could use XSLT to do this, possibly use the
document()
function to include and export the data in fragments.
http://groups.google.com/group/micro...f8fbde6?hl=en&
John
"Greg Bacchus" wrote:

> Hi, I am trying to write a query in SQL 2000 to produce a slightly more than
> just basic xml schema. I can do the query using FOR XML EXPLICIT, but as the
> schema that I want to produce is quite large (with a reasonable amount of
> repitition) this seems unnecessarily complicated. A simplified version of the
> schema I want to produce is:
> <ArrayOfEntity>
> <Entity Key=123>
> <Name>...</Name>
> <Prop1>...</Prop1>
> <Prop2>...</Prop2>
> <Prop3>...</Prop3>
> <Node1 Key=32>text</Node1>
> <Node2 Key=43>text</Node2>
> <Node3 Key=54>text</Node3>
> <Node4 Key=65>text</Node4>
> </Entity>
> <Entity Key=234>
> ...
> </Entity>
> </ArrayOfEntity>
> The thing is, there are quite a few of these child "Node"s, which will only
> contain a key and text value. There are also more complicated child nodes
> (not shown here).
> I was quite excited to find the [Element!Num!Attr!xml] explicit column name,
> because I could then write a function to generate the child node. But this
> does not seem to allow me to specify the Node Key's!!
> Is there any way to write a reasonably simple query to get the above schema?
> Thanks
> Greg
|||Thanks for your reply John.
Can you do XSL Transforms in SQL 2000?
Greg
"John Bell" wrote:
[vbcol=seagreen]
> Hi Greg
> You will need to use the FOR XML EXPLICIT option if you wish to retrieve the
> data in this format. A different approach you be to apply a transform after
> the data has been extracted. You could use XSLT to do this, possibly use the
> document()
> function to include and export the data in fragments.
> http://groups.google.com/group/micro...f8fbde6?hl=en&
> John
> "Greg Bacchus" wrote:
|||Hi Greg
It should be possible to do this using the SP_OA... procedures but really I
would expect it to be done externally.
You may want to look at SQL2005 as this could all be done a lot easier using
..NET
John
"Greg Bacchus" wrote:
[vbcol=seagreen]
> Thanks for your reply John.
> Can you do XSL Transforms in SQL 2000?
> Greg
> "John Bell" wrote:

How to simplify XML Query? (SQL2000)

Hi, I am trying to write a query in SQL 2000 to produce a slightly more than
just basic xml schema. I can do the query using FOR XML EXPLICIT, but as the
schema that I want to produce is quite large (with a reasonable amount of
repitition) this seems unnecessarily complicated. A simplified version of th
e
schema I want to produce is:
<ArrayOfEntity>
<Entity Key=123>
<Name>...</Name>
<Prop1>...</Prop1>
<Prop2>...</Prop2>
<Prop3>...</Prop3>
<Node1 Key=32>text</Node1>
<Node2 Key=43>text</Node2>
<Node3 Key=54>text</Node3>
<Node4 Key=65>text</Node4>
</Entity>
<Entity Key=234>
..
</Entity>
</ArrayOfEntity>
The thing is, there are quite a few of these child "Node"s, which will only
contain a key and text value. There are also more complicated child nodes
(not shown here).
I was quite excited to find the [Element!Num!Attr!xml] explicit column n
ame,
because I could then write a function to generate the child node. But this
does not seem to allow me to specify the Node Key's!!
Is there any way to write a reasonably simple query to get the above schema?
?
Thanks
GregHi Greg
You will need to use the FOR XML EXPLICIT option if you wish to retrieve the
data in this format. A different approach you be to apply a transform after
the data has been extracted. You could use XSLT to do this, possibly use the
document()
function to include and export the data in fragments.
[url]http://groups.google.com/group/microsoft.public.xml/msg/24b2c92baf8fbde6?hl=en&[/u
rl]
John
"Greg Bacchus" wrote:

> Hi, I am trying to write a query in SQL 2000 to produce a slightly more th
an
> just basic xml schema. I can do the query using FOR XML EXPLICIT, but as t
he
> schema that I want to produce is quite large (with a reasonable amount of
> repitition) this seems unnecessarily complicated. A simplified version of
the
> schema I want to produce is:
> <ArrayOfEntity>
> <Entity Key=123>
> <Name>...</Name>
> <Prop1>...</Prop1>
> <Prop2>...</Prop2>
> <Prop3>...</Prop3>
> <Node1 Key=32>text</Node1>
> <Node2 Key=43>text</Node2>
> <Node3 Key=54>text</Node3>
> <Node4 Key=65>text</Node4>
> </Entity>
> <Entity Key=234>
> ...
> </Entity>
> </ArrayOfEntity>
> The thing is, there are quite a few of these child "Node"s, which will onl
y
> contain a key and text value. There are also more complicated child nodes
> (not shown here).
> I was quite excited to find the [Element!Num!Attr!xml] explicit column
name,
> because I could then write a function to generate the child node. But this
> does not seem to allow me to specify the Node Key's!!
> Is there any way to write a reasonably simple query to get the above schem
a'
> Thanks
> Greg|||Thanks for your reply John.
Can you do XSL Transforms in SQL 2000?
Greg
"John Bell" wrote:
[vbcol=seagreen]
> Hi Greg
> You will need to use the FOR XML EXPLICIT option if you wish to retrieve t
he
> data in this format. A different approach you be to apply a transform afte
r
> the data has been extracted. You could use XSLT to do this, possibly use t
he
> document()
> function to include and export the data in fragments.
> [url]http://groups.google.com/group/microsoft.public.xml/msg/24b2c92baf8fbde6?hl=en&[
/url]
> John
> "Greg Bacchus" wrote:
>|||Hi Greg
It should be possible to do this using the SP_OA... procedures but really I
would expect it to be done externally.
You may want to look at SQL2005 as this could all be done a lot easier using
.NET
John
"Greg Bacchus" wrote:
[vbcol=seagreen]
> Thanks for your reply John.
> Can you do XSL Transforms in SQL 2000?
> Greg
> "John Bell" wrote:
>sql

how to simplify what i'm doing (AS the solution?)

i need to do create reports that perform queries where the results are
percentages of total count divided by counts based on different
subquery where values.
a la ...
select 100 * (totalCount/ (select count where columnA value is x) as
partialCount1) as perc1,
100 * (totalCount/ (select count where columnA value is y) as
partialCount2) as perc2
etc
from myTable where (..bunch of where stuff..)
I have simplified the above example just for brevity here.
So, I hate these kinds of nested queries - and they get incredibly
complex to read. I could break them up into longer scripts to simplify
- but I am wondering if there's another way.
The data I am using is from a warehouse on SQL 2005. I have very little
experience with warehouses - and know little about analysis services
and OLAP.
My question is whether it is possible to use Analysis services to
create new tables that make this data far easier to query? That's the
path I am considering venturing down, but I don't want to hit a dead
end... and need just to know if this makes sense. That is to use
Analysis services to crunch out some new tables from the tables I am
using which would have the results neatly packaged in a table or 2 that
is fast and simple to query. Is that the idea of AS?
thx for helping a learner here. let me know if i am making enough sense
in the question.
Hi
This looks like you are trying to do a pivot, in which case the best
solutions will be at the front end although with SQL 2005 you have the new
pivot function, check out the topic "Using PIVOT and UNPIVOT " in books
online.
John
<rsphere@.gmail.com> wrote in message
news:1137856740.675188.233050@.g49g2000cwa.googlegr oups.com...
>i need to do create reports that perform queries where the results are
> percentages of total count divided by counts based on different
> subquery where values.
> a la ...
> select 100 * (totalCount/ (select count where columnA value is x) as
> partialCount1) as perc1,
> 100 * (totalCount/ (select count where columnA value is y) as
> partialCount2) as perc2
> etc
> from myTable where (..bunch of where stuff..)
> I have simplified the above example just for brevity here.
> So, I hate these kinds of nested queries - and they get incredibly
> complex to read. I could break them up into longer scripts to simplify
> - but I am wondering if there's another way.
> The data I am using is from a warehouse on SQL 2005. I have very little
> experience with warehouses - and know little about analysis services
> and OLAP.
> My question is whether it is possible to use Analysis services to
> create new tables that make this data far easier to query? That's the
> path I am considering venturing down, but I don't want to hit a dead
> end... and need just to know if this makes sense. That is to use
> Analysis services to crunch out some new tables from the tables I am
> using which would have the results neatly packaged in a table or 2 that
> is fast and simple to query. Is that the idea of AS?
> thx for helping a learner here. let me know if i am making enough sense
> in the question.
>

how to simplify what i'm doing (AS the solution?)

i need to do create reports that perform queries where the results are
percentages of total count divided by counts based on different
subquery where values.
a la ...
select 100 * (totalCount/ (select count where columnA value is x) as
partialCount1) as perc1,
100 * (totalCount/ (select count where columnA value is y) as
partialCount2) as perc2
etc
from myTable where (..bunch of where stuff..)
I have simplified the above example just for brevity here.
So, I hate these kinds of nested queries - and they get incredibly
complex to read. I could break them up into longer scripts to simplify
- but I am wondering if there's another way.
The data I am using is from a warehouse on SQL 2005. I have very little
experience with warehouses - and know little about analysis services
and OLAP.
My question is whether it is possible to use Analysis services to
create new tables that make this data far easier to query? That's the
path I am considering venturing down, but I don't want to hit a dead
end... and need just to know if this makes sense. That is to use
Analysis services to crunch out some new tables from the tables I am
using which would have the results neatly packaged in a table or 2 that
is fast and simple to query. Is that the idea of AS?
thx for helping a learner here. let me know if i am making enough sense
in the question.Hi
This looks like you are trying to do a pivot, in which case the best
solutions will be at the front end although with SQL 2005 you have the new
pivot function, check out the topic "Using PIVOT and UNPIVOT " in books
online.
John
<rsphere@.gmail.com> wrote in message
news:1137856740.675188.233050@.g49g2000cwa.googlegroups.com...
>i need to do create reports that perform queries where the results are
> percentages of total count divided by counts based on different
> subquery where values.
> a la ...
> select 100 * (totalCount/ (select count where columnA value is x) as
> partialCount1) as perc1,
> 100 * (totalCount/ (select count where columnA value is y) as
> partialCount2) as perc2
> etc
> from myTable where (..bunch of where stuff..)
> I have simplified the above example just for brevity here.
> So, I hate these kinds of nested queries - and they get incredibly
> complex to read. I could break them up into longer scripts to simplify
> - but I am wondering if there's another way.
> The data I am using is from a warehouse on SQL 2005. I have very little
> experience with warehouses - and know little about analysis services
> and OLAP.
> My question is whether it is possible to use Analysis services to
> create new tables that make this data far easier to query? That's the
> path I am considering venturing down, but I don't want to hit a dead
> end... and need just to know if this makes sense. That is to use
> Analysis services to crunch out some new tables from the tables I am
> using which would have the results neatly packaged in a table or 2 that
> is fast and simple to query. Is that the idea of AS?
> thx for helping a learner here. let me know if i am making enough sense
> in the question.
>

how to simplify what i'm doing (AS the solution?)

i need to do create reports that perform queries where the results are
percentages of total count divided by counts based on different
subquery where values.
a la ...
select 100 * (totalCount/ (select count where columnA value is x) as
partialCount1) as perc1,
100 * (totalCount/ (select count where columnA value is y) as
partialCount2) as perc2
etc
from myTable where (..bunch of where stuff..)
I have simplified the above example just for brevity here.
So, I hate these kinds of nested queries - and they get incredibly
complex to read. I could break them up into longer scripts to simplify
- but I am wondering if there's another way.
The data I am using is from a warehouse on SQL 2005. I have very little
experience with warehouses - and know little about analysis services
and OLAP.
My question is whether it is possible to use Analysis services to
create new tables that make this data far easier to query? That's the
path I am considering venturing down, but I don't want to hit a dead
end... and need just to know if this makes sense. That is to use
Analysis services to crunch out some new tables from the tables I am
using which would have the results neatly packaged in a table or 2 that
is fast and simple to query. Is that the idea of AS?
thx for helping a learner here. let me know if i am making enough sense
in the question.Hi
This looks like you are trying to do a pivot, in which case the best
solutions will be at the front end although with SQL 2005 you have the new
pivot function, check out the topic "Using PIVOT and UNPIVOT " in books
online.
John
<rsphere@.gmail.com> wrote in message
news:1137856740.675188.233050@.g49g2000cwa.googlegroups.com...
>i need to do create reports that perform queries where the results are
> percentages of total count divided by counts based on different
> subquery where values.
> a la ...
> select 100 * (totalCount/ (select count where columnA value is x) as
> partialCount1) as perc1,
> 100 * (totalCount/ (select count where columnA value is y) as
> partialCount2) as perc2
> etc
> from myTable where (..bunch of where stuff..)
> I have simplified the above example just for brevity here.
> So, I hate these kinds of nested queries - and they get incredibly
> complex to read. I could break them up into longer scripts to simplify
> - but I am wondering if there's another way.
> The data I am using is from a warehouse on SQL 2005. I have very little
> experience with warehouses - and know little about analysis services
> and OLAP.
> My question is whether it is possible to use Analysis services to
> create new tables that make this data far easier to query? That's the
> path I am considering venturing down, but I don't want to hit a dead
> end... and need just to know if this makes sense. That is to use
> Analysis services to crunch out some new tables from the tables I am
> using which would have the results neatly packaged in a table or 2 that
> is fast and simple to query. Is that the idea of AS?
> thx for helping a learner here. let me know if i am making enough sense
> in the question.
>

How to simplify this slow Stored Procedure

Dear Reader(s),

Is there anyway to write the following stored procedure without the loop so that it goes much faster? :confused:

-----------------------
use MJ_ReportBase
go
if exists(select 1 from sysobjects where type='P' and name='sp_Periode')
begin
drop procedure sp_Periode
end
go
create procedure sp_Periode
@.start int
, @.stop int
as
declare @.x int

set @.x = 0
set @.x=@.start

delete from tbl_periode

while (@.x>=@.stop)
begin

-- --
-- --
-- Create table tbl_inout
if exists(select 1 from sysobjects where type='U' and name='tbl_inout')
begin
drop table tbl_inout
end

select datetimestamp,accname,badgeid,personname,inoutreg into tbl_inout from WinXS..x18 where convert(varchar,datetimestamp,120)+' '+ltrim(str(id))+' '+ltrim(str(badgeid)) in
(select convert(varchar,max(datetimestamp),120)+' '+ltrim(str(max(id)))+' '+ltrim(str(badgeid)) as datetimestamp from WinXS..x18 where (accname='Kelder -1' or accname='Tnk Entree') and convert(varchar,datetimestamp,105)=convert(varchar ,getdate()-abs(@.x),105) group by badgeid)
and badgeid>0
order by personname

-- --
-- --
-- Create table tbl_result

if exists(select 1 from sysobjects where type='U' and name='tbl_result')
begin
drop table tbl_result
end

-- --
-- --

select
convert(varchar,datetimestamp,105) 'DATUM'
, badgeid 'PAS'
, initials 'VOORNAAM'
, personname 'NAAM'
, convert(varchar,min(datetimestamp),108) 'MIN'
, convert(varchar,max(datetimestamp),108) 'MAX'
into
tbl_result
from
WinXS..x18
where
convert(varchar,datetimestamp,105)=convert(varchar ,getdate()-abs(@.x),105)
and
accname in ('Kelder -1','Tnk Entree')
and badgeid>0
group by
convert(varchar,WinXS..x18.datetimestamp,105)
, badgeid
, initials
, personname
order by
initials
, personname asc
, convert(varchar,datetimestamp,105) asc

-- --
-- --
-- Rapportage tabel

insert into
tbl_periode
select
tbl_result.datum as DATUM
, ltrim(ltrim(rtrim(tbl_result.naam))+' '+ltrim(rtrim(isnull(tbl_result.voornaam,' ')))) as NAAM
, tbl_result.min as MIN
, tbl_result.max as MAX
, case tbl_inout.inoutreg when 1 then 'in' when 2 then 'out' else 'err' end as [IN/OUT]
, substring('00000',1,5-len(tbl_result.pas))+ltrim(str(tbl_result.pas)) as PAS
from
tbl_inout,tbl_result
where
tbl_result.datum+' '+tbl_result.max+' '+ltrim(str(tbl_result.pas))
= convert(varchar,tbl_inout.datetimestamp,105)+' '+convert(varchar,tbl_inout.datetimestamp,108)+' '+ltrim(str(badgeid))
order by
tbl_result.naam asc

-- --
-- --
--

set @.x=@.x-1
end
go

print 'Klaar!'
-----------------------

What it does is determining the minimum entry time and the maximum exiting time per day of people going true the main entrance of a building.

Many thanks in advance.
:)First...collapse the queries in to 1.

Second, what's the first select for?

Third

Loose the loop and do

WHERE datetimestamp > GetDate()-@.Start
AND datetimestamp < = GetDate()-@.Stop|||I don't even want to try to figure out your code. Don't use permanent tables for temporary storage (your tbl_inout table). That will get you into trouble in a multi-user environment.

Post the relevent DDL for your tables any somebody here can probably show you a more efficient set-based algorithm.|||Hi to all,

I have found the solution after a long time puzzeling. :)

Please check the sql below:
-----------------------
select
tbl_INOUT.*
, CASE B.inoutreg WHEN 1 THEN 'IN' WHEN 2 THEN 'UIT' ELSE 'Fout' END 'STATUS'
from
(
select
convert(char(10),datetimestamp,120) 'DATUM'
, badgeid 'PAS'
, initials 'VOORNAAM'
, personname 'NAAM'
, convert(varchar,min(datetimestamp),108) 'MIN'
, convert(varchar,max(datetimestamp),108) 'MAX'
from
WinXS..x18
where
(convert(varchar(10),datetimestamp,120)>=convert(varchar(10),'2005-01-01',120)
and
convert(varchar(10),datetimestamp,120)<=convert(varchar(10),'2005-02-28',120))
and
accname in ('Kelder -1','Tnk Entree')
and
badgeid>0
group by
convert(char(10),datetimestamp,120)
, badgeid
, initials
, personname
) tbl_INOUT, WinXS..x18 B
where
tbl_INOUT.pas=B.badgeid
and
convert(datetime,tbl_INOUT.datum+' '+tbl_INOUT.max,120)=B.datetimestamp
and
badgeid=81
order by
tbl_INOUT.naam asc
, tbl_INOUT.datum DESC

Greetz,
DePrins
:D

How to simplify my storeprocedure

Hello,if have more than two condition(City,State), how to simplify
following storeprocedure.(without Too much [if ... else] and duplicat
code),thanks.
CREATE PROCEDURE selectAuthors @.City Varchar(20),@.State Varchar(2)
AS
IF @.City<>''
IF @.State<>''
SELECT *
FROM authors where city=@.City AND state=@.State
Else
SELECT *
FROM authors where city=@.City
Else
IF @.State<>''
SELECT *
FROM authors where state=@.State
Else
SELECT *
FROM authorsDoes this help?
=====
DECLARE @.City VARCHAR(30)
DECLARE @.State VARCHAR(10)
SET @.City = 'Menlo Park'
SET @.State = ''
SELECT * FROM authors WHERE
city = CASE WHEN @.City = '' OR @.City IS NULL THEN city ELSE @.City END
AND
state = CASE WHEN @.State = '' OR @.State IS NULL THEN state ELSE @.State
END
=====
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Simon" <w007@.seed.net.tw> wrote in message
news:OwuB%23X%23RGHA.1236@.TK2MSFTNGP11.phx.gbl...
> Hello,if have more than two condition(City,State), how to simplify
> following storeprocedure.(without Too much [if ... else] and duplicat
> code),thanks.
>
> CREATE PROCEDURE selectAuthors @.City Varchar(20),@.State Varchar(2)
> AS
> IF @.City<>''
> IF @.State<>''
> SELECT *
> FROM authors where city=@.City AND state=@.State
> Else
> SELECT *
> FROM authors where city=@.City
> Else
> IF @.State<>''
> SELECT *
> FROM authors where state=@.State
> Else
> SELECT *
> FROM authors
>
>|||Thank you for your help
"SriSamp" <ssampath@.sct.co.in> bl
news:eqxiiI$RGHA.5780@.TK2MSFTNGP10.phx.gbl g...
> Does this help?
> =====
> DECLARE @.City VARCHAR(30)
> DECLARE @.State VARCHAR(10)
> SET @.City = 'Menlo Park'
> SET @.State = ''
> SELECT * FROM authors WHERE
> city = CASE WHEN @.City = '' OR @.City IS NULL THEN city ELSE @.City END
> AND
> state = CASE WHEN @.State = '' OR @.State IS NULL THEN state ELSE @.State
> END
> =====
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "Simon" <w007@.seed.net.tw> wrote in message
> news:OwuB%23X%23RGHA.1236@.TK2MSFTNGP11.phx.gbl...
>sql