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 set IDENTITY_INSERT to ON?

Hi,
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert explicit value
for identity column in table 'tblCustomers' when IDENTITY_INSERT is set to
OFF.
/checkout.asp, line 112
Is this a database setting error? Can anyone instruct us on how to set the
IDENTITY_INSERT to ON for the ID column in our table?
Thanks.
nath.
Hi,
thats a session thing, llok in the BOL for more information:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/a5dd49f2-45c7-44a8-b182-e0a5e5c373ee.htm
SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON |
OFF }
HTH, Jens Suessmeyer.
|||Hi,
thats a session thing, llok in the BOL for more information:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/a5dd49f2-45c7-44a8-b182-e0a5e5c373ee.htm
SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON |
OFF }
HTH, Jens Suessmeyer.
|||The BOL? :o(
Total newbie here Jens...hope you can bear with me! :o)
Where/What is the BOL?
Thanks
Nath.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1141478203.215944.156110@.i40g2000cwc.googlegr oups.com...
> Hi,
> thats a session thing, llok in the BOL for more information:
>
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/a5dd49f2-45c7-44a8-b182-e0a5e5c373ee.htm
>
> SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON |
> OFF }
>
> HTH, Jens Suessmeyer.
>
|||BOL is Books Online. The documentation that comes with SQL Server.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Nathon Jones" <sales@.NOSHPAMtradmusic.com> wrote in message
news:Om%23WVY5PGHA.5592@.TK2MSFTNGP11.phx.gbl...
> The BOL? :o(
> Total newbie here Jens...hope you can bear with me! :o)
> Where/What is the BOL?
> Thanks
> Nath.
> "Jens" <Jens@.sqlserver2005.de> wrote in message
> news:1141478203.215944.156110@.i40g2000cwc.googlegr oups.com...
>
|||Hi,
Doh!
Nath.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OpLMjd5PGHA.5152@.TK2MSFTNGP10.phx.gbl...
> BOL is Books Online. The documentation that comes with SQL Server.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Nathon Jones" <sales@.NOSHPAMtradmusic.com> wrote in message
> news:Om%23WVY5PGHA.5592@.TK2MSFTNGP11.phx.gbl...
>
|||Are you certain you want to set IDENTITY_INSERT ON? There are special
situations where you may need to turn on this option but in most cases you
want to omit the column from your insert statement so that SQL Server will
assign the value automatically.
Hope this helps.
Dan Guzman
SQL Server MVP
"Nathon Jones" <sales@.NOSHPAMtradmusic.com> wrote in message
news:ONykDn4PGHA.2828@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
> [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert explicit
> value
> for identity column in table 'tblCustomers' when IDENTITY_INSERT is set to
> OFF.
> /checkout.asp, line 112
> Is this a database setting error? Can anyone instruct us on how to set
> the
> IDENTITY_INSERT to ON for the ID column in our table?
> Thanks.
> nath.
>

How to set IDENTITY_INSERT to ON?

Hi,
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert explicit value
for identity column in table 'tblCustomers' when IDENTITY_INSERT is set to
OFF.
/checkout.asp, line 112
Is this a database setting error? Can anyone instruct us on how to set the
IDENTITY_INSERT to ON for the ID column in our table?
Thanks.
nath.Hi,
thats a session thing, llok in the BOL for more information:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/a5dd49f2-45c7-44a8-b182-e0a5e5c373ee.htm
SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON |
OFF }
HTH, Jens Suessmeyer.|||Hi,
thats a session thing, llok in the BOL for more information:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/a5dd49f2-45c7-44a8-b182-e0a5e5c373ee.htm
SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON |
OFF }
HTH, Jens Suessmeyer.|||The BOL? :o(
Total newbie here Jens...hope you can bear with me! :o)
Where/What is the BOL?
Thanks
Nath.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1141478203.215944.156110@.i40g2000cwc.googlegroups.com...
> Hi,
> thats a session thing, llok in the BOL for more information:
>
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/a5dd49f2-45c7-44a8-b182-e0a5e5c373ee.htm
>
> SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON |
> OFF }
>
> HTH, Jens Suessmeyer.
>|||BOL is Books Online. The documentation that comes with SQL Server.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Nathon Jones" <sales@.NOSHPAMtradmusic.com> wrote in message
news:Om%23WVY5PGHA.5592@.TK2MSFTNGP11.phx.gbl...
> The BOL? :o(
> Total newbie here Jens...hope you can bear with me! :o)
> Where/What is the BOL?
> Thanks
> Nath.
> "Jens" <Jens@.sqlserver2005.de> wrote in message
> news:1141478203.215944.156110@.i40g2000cwc.googlegroups.com...
>> Hi,
>> thats a session thing, llok in the BOL for more information:
>>
>> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/a5dd49f2-45c7-44a8-b182-e0a5e5c373ee.htm
>>
>> SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON |
>> OFF }
>>
>> HTH, Jens Suessmeyer.
>|||Hi,
Doh!
Nath.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OpLMjd5PGHA.5152@.TK2MSFTNGP10.phx.gbl...
> BOL is Books Online. The documentation that comes with SQL Server.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Nathon Jones" <sales@.NOSHPAMtradmusic.com> wrote in message
> news:Om%23WVY5PGHA.5592@.TK2MSFTNGP11.phx.gbl...
>> The BOL? :o(
>> Total newbie here Jens...hope you can bear with me! :o)
>> Where/What is the BOL?
>> Thanks
>> Nath.
>> "Jens" <Jens@.sqlserver2005.de> wrote in message
>> news:1141478203.215944.156110@.i40g2000cwc.googlegroups.com...
>> Hi,
>> thats a session thing, llok in the BOL for more information:
>>
>> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/a5dd49f2-45c7-44a8-b182-e0a5e5c373ee.htm
>>
>> SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON |
>> OFF }
>>
>> HTH, Jens Suessmeyer.
>>
>|||Are you certain you want to set IDENTITY_INSERT ON? There are special
situations where you may need to turn on this option but in most cases you
want to omit the column from your insert statement so that SQL Server will
assign the value automatically.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Nathon Jones" <sales@.NOSHPAMtradmusic.com> wrote in message
news:ONykDn4PGHA.2828@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
> [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert explicit
> value
> for identity column in table 'tblCustomers' when IDENTITY_INSERT is set to
> OFF.
> /checkout.asp, line 112
> Is this a database setting error? Can anyone instruct us on how to set
> the
> IDENTITY_INSERT to ON for the ID column in our table?
> Thanks.
> nath.
>