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
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.
http://groups.google.com/group/microsoft.public.xml/msg/24b2c92baf8fbde6?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:
> 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/microsoft.public.xml/msg/24b2c92baf8fbde6?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|||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:
> Thanks for your reply John.
> Can you do XSL Transforms in SQL 2000?
> Greg
> "John Bell" wrote:
> > 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/microsoft.public.xml/msg/24b2c92baf8fbde6?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

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 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 show roles with permissions to objects

Hello,

I am trying to write a script using SQL Server 2000 to list all of the
roles that have any permissions on a specified object (view, table,
sp, etc.). Essentially I am trying to script what is displayed when
one selects the 'list only users/user-defined database roles/public
with permissions to this object' option under 'manage permissions' in
EM but without showing individual users, only roles. I've looked at
the system sp's and the information_schema views but none of those
seem to give this information. Am I going to have to look directly at
the system tables? If anyone has a script that does this for a
specified object or can point me to more specific information on how
to do this I'd appreciate it. Thanks!

BruceHave you checked "sp_helprotect" , this one is permissions for all objects,

--

Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=http://www.itjobfeed.com">UK IT Jobs</a>

"Bruce" <deluxeinformation@.gmail.comwrote in message
news:1173802556.534695.194010@.p10g2000cwp.googlegr oups.com...

Quote:

Originally Posted by

Hello,
>
I am trying to write a script using SQL Server 2000 to list all of the
roles that have any permissions on a specified object (view, table,
sp, etc.). Essentially I am trying to script what is displayed when
one selects the 'list only users/user-defined database roles/public
with permissions to this object' option under 'manage permissions' in
EM but without showing individual users, only roles. I've looked at
the system sp's and the information_schema views but none of those
seem to give this information. Am I going to have to look directly at
the system tables? If anyone has a script that does this for a
specified object or can point me to more specific information on how
to do this I'd appreciate it. Thanks!
>
Bruce
>

|||On Mar 14, 4:41 am, "Jack Vamvas" <DEL_TO_RE...@.del.comwrote:

Quote:

Originally Posted by

Have you checked "sp_helprotect" , this one is permissions for all objects,
>
--
>
Jack Vamvas
___________________________________
The latest IT jobs -www.ITjobfeed.com
<a href="http://links.10026.com/?link=http://www.itjobfeed.com">UK IT Jobs</a>
>
"Bruce" <deluxeinformat...@.gmail.comwrote in message
>
news:1173802556.534695.194010@.p10g2000cwp.googlegr oups.com...
>

Quote:

Originally Posted by

Hello,

>

Quote:

Originally Posted by

I am trying to write a script using SQL Server 2000 to list all of the
roles that have any permissions on a specified object (view, table,
sp, etc.). Essentially I am trying to script what is displayed when
one selects the 'list only users/user-defined database roles/public
with permissions to this object' option under 'manage permissions' in
EM but without showing individual users, only roles. I've looked at
the system sp's and the information_schema views but none of those
seem to give this information. Am I going to have to look directly at
the system tables? If anyone has a script that does this for a
specified object or can point me to more specific information on how
to do this I'd appreciate it. Thanks!

>

Quote:

Originally Posted by

Bruce

Thank you. I don't know how I overlooked that one. Sometimes I wish
BOL was laid out a little differently.

Bruce