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 'share drive', SCSI HD's on servers

Rodney is correct, your SAN or DAS solution must be in the
Windows Catalog for use on a cluster, but it's more than
just getting disks. How you carve them up is just as
important, because, for example, if you create one LUN but
plan to put multiple drives on it, that won't work.
You need to plan your disk layout carefully.
I am sorry for my lack of understanding here, but let's say if I have two
"regular" Compaq ML370, can I just add two more disks on each server and
let's say create a Raid1 and use that as the 'quorum' ? I've seen the list
of devices Rod listed OK, but I still don't understand how to configure the
disks on my servers to be used in a cluster (assume they are part of the MS
HCL).
"Allan Hirt" <anonymous@.discussions.microsoft.com> wrote in message
news:575601c47470$953e6530$a601280a@.phx.gbl...
> Rodney is correct, your SAN or DAS solution must be in the
> Windows Catalog for use on a cluster, but it's more than
> just getting disks. How you carve them up is just as
> important, because, for example, if you create one LUN but
> plan to put multiple drives on it, that won't work.
> You need to plan your disk layout carefully.
|||I think you need to read this
http://www.microsoft.com/downloads/d...displaylang=en
its a step by step guide on cluster installations
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
"Marlon Brown" <marlon_brown@.hotmail.com> wrote in message
news:OaEfxULdEHA.3916@.TK2MSFTNGP11.phx.gbl...
> I am sorry for my lack of understanding here, but let's say if I have two
> "regular" Compaq ML370, can I just add two more disks on each server and
> let's say create a Raid1 and use that as the 'quorum' ? I've seen the
list
> of devices Rod listed OK, but I still don't understand how to configure
the
> disks on my servers to be used in a cluster (assume they are part of the
MS
> HCL).
>
> "Allan Hirt" <anonymous@.discussions.microsoft.com> wrote in message
> news:575601c47470$953e6530$a601280a@.phx.gbl...
>
|||(...)
Actually it is what I didn't understand: From the text below I had the
impresson that "external disk storage unit connected to all computers" I
still can't see how my regular SCSI disks existing on both Compaq servers
can be configured as shared disks. It also says that the controller cannot
be the one used by the system drive...
Shared Disk Requirements:
An HCL-approved external disk storage unit connected to all
computers. This will be used as the clustered shared disk. Some type of a
hardware redundant array of independent disks (RAID) is recommended.
All shared disks, including the quorum disk, must be physically
attached to a shared bus.
Note: The requirement above does not hold true for Majority Node Set (MNS)
clusters, which are not covered in this guide.
Shared disks must be on a different controller then the one used
by the system drive.
"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:uzdl$BMdEHA.384@.TK2MSFTNGP10.phx.gbl...
> I think you need to read this
>
http://www.microsoft.com/downloads/d...displaylang=en[vbcol=seagreen]
> its a step by step guide on cluster installations
> Cheers,
> Rod
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering
> "Marlon Brown" <marlon_brown@.hotmail.com> wrote in message
> news:OaEfxULdEHA.3916@.TK2MSFTNGP11.phx.gbl...
two
> list
> the
> MS
>
|||Marlon, you have all the facts already. You need to buy or use an external
storage, on a different controller.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
"Marlon Brown" <marlon_brown@.hotmail.com> wrote in message
news:uhsppKOdEHA.1652@.TK2MSFTNGP09.phx.gbl...
> (...)
> Actually it is what I didn't understand: From the text below I had the
> impresson that "external disk storage unit connected to all computers" I
> still can't see how my regular SCSI disks existing on both Compaq servers
> can be configured as shared disks. It also says that the controller cannot
> be the one used by the system drive...
>
> Shared Disk Requirements:
> An HCL-approved external disk storage unit connected to all
> computers. This will be used as the clustered shared disk. Some type of a
> hardware redundant array of independent disks (RAID) is recommended.
> All shared disks, including the quorum disk, must be physically
> attached to a shared bus.
> Note: The requirement above does not hold true for Majority Node Set (MNS)
> clusters, which are not covered in this guide.
> Shared disks must be on a different controller then the one used
> by the system drive.
> "Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
> message news:uzdl$BMdEHA.384@.TK2MSFTNGP10.phx.gbl...
>
http://www.microsoft.com/downloads/d...displaylang=en[vbcol=seagreen]
> two
and[vbcol=seagreen]
configure[vbcol=seagreen]
the
>
|||Marlon,
I am considering writing an entire book on disk=20
architecture/design for SQL Server, but I would also=20
suggest you look at the stuff I have in the existing SQL=20
2K HA book to understand what goes into making a cluster.
At a minimum, you need (presented at the OS level) for=20
your cluster:
* 1 quorum disk
* 1 for SQL Server (probably more; I'd say a minimum of=20
two for data and log, but it will depend on your=20
requirements)
* 1 for MS DTC (for W2K3 required, and recommended for W2K)
Each of these should reside on different physical LUNs on=20
an external drive array, whether DAS or SAN (*not* NAS. =20
The quorum LUN must not be on a disk with any other (many=20
find it a waste of space, but that's the way it is). Some=20
vendors allow you to carve up one chunk of disk, and then=20
do partitions, and that is not really what you should do.
Hope this helps.
Allan

>--Original Message--
>Marlon, you have all the facts already. You need to buy=20
or use an external[vbcol=seagreen]
>storage, on a different controller.
>Cheers,
>Rod
>MVP - Windows Server - Clustering
>http://www.nw-america.com - Clustering
>"Marlon Brown" <marlon_brown@.hotmail.com> wrote in message
>news:uhsppKOdEHA.1652@.TK2MSFTNGP09.phx.gbl...
below I had the[vbcol=seagreen]
all computers" I[vbcol=seagreen]
both Compaq servers[vbcol=seagreen]
the controller cannot[vbcol=seagreen]
connected to all[vbcol=seagreen]
disk. Some type of a[vbcol=seagreen]
recommended.[vbcol=seagreen]
must be physically[vbcol=seagreen]
Majority Node Set (MNS)[vbcol=seagreen]
controller then the one used[vbcol=seagreen]
america.com> wrote in
>http://www.microsoft.com/downloads/details.aspx?
FamilyID=3D96f76ed7-9634-4300-9159-
89638f4b4ef7&displaylang=3Den[vbcol=seagreen]
message[vbcol=seagreen]
let's say if I have[vbcol=seagreen]
disks on each server[vbcol=seagreen]
>and
the 'quorum' ? I've seen the[vbcol=seagreen]
understand how to[vbcol=seagreen]
>configure
they are part of[vbcol=seagreen]
>the
wrote in message[vbcol=seagreen]
be in the[vbcol=seagreen]
more than[vbcol=seagreen]
just as[vbcol=seagreen]
one LUN but[vbcol=seagreen]
work.
>
>.
>