How to simulate Nothing from a deep link

Hello-

I'm working on an app that will create an HTTP connection to the SSRS server to gather a PDF generated by SSRS and place it on the LAN. looks something like this :

http://server/ReportServer?%2fDEV%2frpt_Sheet&rs%3aCommand=Render&rc:Toolbar=false&userID=NULL&StoreID=23&rs:Format=PDF

We have a few reports where 1 or more parameters can be null, but I'm having one heck of a time trying get this to work with a null value. Does anyone know what value is actually passed to SSRS when a parameter is NULL?

I have tried Nothing, but to no avail.

Thanks in adavance,

Tristan

Do not include that parameter in the link and set its default value as null or send something like this:

parameter name = "param1"

&param1%3aisnull=True

http://reportserver/?%2fReport1Test&param1%3aisnull=True&rs%3aParameterLanguage=&rc%3aParameters=Collapsed

Maciej

How to simulate an array in SQL Server

Just thought some might be interested in how to identify individual
items in an 'array' by declaring a local variable as table
----
declare @.MyTable table (MyRowID int identity, MyData varchar(255))
declare @.MyCurrentRow as int
declare @.MyRowCount as int
insert into @.MyTable select Name from sysobjects -- (or whatever)
set @.MyRowCount = @.@.RowCount
-- @.@.Rowcount hold the count of the last table in these cases anyway
-- then you can use statement to access the nth. row in the 'array'
select MyData from @.MyTable where MyRowID = 5 -- as an example
--
-- or step through the table using @.MyCurrentRow
Set @.MyCurrentRow = 1
while @.MyCurrentRow < @.MyRowCount +1
Begin
Select MyData from @.MyTable where MyRowID = @.MyCurrentRow
set @.MyCurrentRow = @.MyCurrentRow + 1
Continue
end
Just thought it might help. It isn't rocket science but I saw a few
with problems so thought I'd post.
I needed it to obtain individual values from a table and use those
values to call a stored procedure repeatedly.
CheersSee if these help:
http://www.aspfaq.com/show.asp?id=2248 Arrays & Lists
http://www.sommarskog.se/dynamic_sql.html Dynamic SQL
http://www.users.drew.edu/skass/sql...bleProc.sql.txt List to
Table script
Andrew J. Kelly SQL MVP
<mal_k100@.hotmail.com> wrote in message
news:1125573743.271138.167070@.o13g2000cwo.googlegroups.com...
> Just thought some might be interested in how to identify individual
> items in an 'array' by declaring a local variable as table
> ----
> declare @.MyTable table (MyRowID int identity, MyData varchar(255))
> declare @.MyCurrentRow as int
> declare @.MyRowCount as int
> insert into @.MyTable select Name from sysobjects -- (or whatever)
> set @.MyRowCount = @.@.RowCount
> -- @.@.Rowcount hold the count of the last table in these cases anyway
> -- then you can use statement to access the nth. row in the 'array'
> select MyData from @.MyTable where MyRowID = 5 -- as an example
> --
> -- or step through the table using @.MyCurrentRow
> Set @.MyCurrentRow = 1
> while @.MyCurrentRow < @.MyRowCount +1
> Begin
> Select MyData from @.MyTable where MyRowID = @.MyCurrentRow
> set @.MyCurrentRow = @.MyCurrentRow + 1
> Continue
> end
> Just thought it might help. It isn't rocket science but I saw a few
> with problems so thought I'd post.
> I needed it to obtain individual values from a table and use those
> values to call a stored procedure repeatedly.
> Cheers
>sql

how to simulate a block

could someone provide some simple code to simulate a block. I want to test
a blocking script I want to implement on prod.
TIA
C
Execute this on one connection:
create table ##foo (SomeColumn int)
go
begin transaction
insert into ##foo (SomeColumn) values (1)
waitfor delay '0:00:40' /* hold the transaction open for 40 seconds */
go
commit transaction
/*****************************/
Execute this on another connection:
select * from ##foo
Keith
"CD" <mcdye1@.hotmail.REMOVETHIS.com> wrote in message
news:%23PbFfLMeFHA.1920@.tk2msftngp13.phx.gbl...
> could someone provide some simple code to simulate a block. I want to
> test a blocking script I want to implement on prod.
> TIA
> C
>
|||Try,
-- conn 1
use northwind
go
begin transaction
update orders
set orderdate = orderdate
-- conn 2
use northwind
go
select * from orders
Connection 2 will be blocked until you commit or rollback the transaction in
connection 1.
AMB
"CD" wrote:

> could someone provide some simple code to simulate a block. I want to test
> a blocking script I want to implement on prod.
> TIA
> C
>
>
|||Look at tablockx holdlock keywords
these will hold locks on tables with in a block of code...
i.e.
create procedure ...
select @.nextid = id from foo with (tablockx holdlock)
/* tablockx forces an exclusive table lock
tablock to do a non-exclusive lock
holdlock tells SQL Server to hold locks for duration of the transaction */
update foo set [id] = [id]+ @.block
return @.nextid
...
this will prevent n users from getting the same id
GO
"CD" wrote:

> could someone provide some simple code to simulate a block. I want to test
> a blocking script I want to implement on prod.
> TIA
> C
>
>

how to simulate a block

could someone provide some simple code to simulate a block. I want to test
a blocking script I want to implement on prod.
TIA
CExecute this on one connection:
create table ##foo (SomeColumn int)
go
begin transaction
insert into ##foo (SomeColumn) values (1)
waitfor delay '0:00:40' /* hold the transaction open for 40 seconds */
go
commit transaction
/*****************************/
Execute this on another connection:
select * from ##foo
Keith
"CD" <mcdye1@.hotmail.REMOVETHIS.com> wrote in message
news:%23PbFfLMeFHA.1920@.tk2msftngp13.phx.gbl...
> could someone provide some simple code to simulate a block. I want to
> test a blocking script I want to implement on prod.
> TIA
> C
>|||Try,
-- conn 1
use northwind
go
begin transaction
update orders
set orderdate = orderdate
-- conn 2
use northwind
go
select * from orders
Connection 2 will be blocked until you commit or rollback the transaction in
connection 1.
AMB
"CD" wrote:

> could someone provide some simple code to simulate a block. I want to tes
t
> a blocking script I want to implement on prod.
> TIA
> C
>
>|||Look at tablockx holdlock keywords
these will hold locks on tables with in a block of code...
i.e.
create procedure ...
select @.nextid = id from foo with (tablockx holdlock)
/* tablockx forces an exclusive table lock
tablock to do a non-exclusive lock
holdlock tells SQL Server to hold locks for duration of the transaction */
update foo set [id] = [id]+ @.block
return @.nextid
...
this will prevent n users from getting the same id
GO
"CD" wrote:

> could someone provide some simple code to simulate a block. I want to tes
t
> a blocking script I want to implement on prod.
> TIA
> C
>
>sql

how to simulate a block

could someone provide some simple code to simulate a block. I want to test
a blocking script I want to implement on prod.
TIA
CExecute this on one connection:
create table ##foo (SomeColumn int)
go
begin transaction
insert into ##foo (SomeColumn) values (1)
waitfor delay '0:00:40' /* hold the transaction open for 40 seconds */
go
commit transaction
/*****************************/
Execute this on another connection:
select * from ##foo
Keith
"CD" <mcdye1@.hotmail.REMOVETHIS.com> wrote in message
news:%23PbFfLMeFHA.1920@.tk2msftngp13.phx.gbl...
> could someone provide some simple code to simulate a block. I want to
> test a blocking script I want to implement on prod.
> TIA
> C
>|||Try,
-- conn 1
use northwind
go
begin transaction
update orders
set orderdate = orderdate
-- conn 2
use northwind
go
select * from orders
Connection 2 will be blocked until you commit or rollback the transaction in
connection 1.
AMB
"CD" wrote:
> could someone provide some simple code to simulate a block. I want to test
> a blocking script I want to implement on prod.
> TIA
> C
>
>|||Look at tablockx holdlock keywords
these will hold locks on tables with in a block of code...
i.e.
create procedure ...
select @.nextid = id from foo with (tablockx holdlock)
/* tablockx forces an exclusive table lock
tablock to do a non-exclusive lock
holdlock tells SQL Server to hold locks for duration of the transaction */
update foo set [id] = [id]+ @.block
return @.nextid
...
this will prevent n users from getting the same id
GO
"CD" wrote:
> could someone provide some simple code to simulate a block. I want to test
> a blocking script I want to implement on prod.
> TIA
> C
>
>

How to simplify/optimize this query?

Hi,
Could the following query be simplified/optimized? The database has a table
that maps users to locations, locations to chapters, and sections to
chapters. I need to issue a query that would return a list of sections that
can be assigned to a user (excluding the ones that have already been
assigned) .
@.UserId int
[...]
SELECT SectionID, [Name],
FROM Section
WHERE SectionID NOT IN (SELECT SectionID FROM UserSection WHERE UserID = @.UserID) AND
SectionID IN (SELECT DISTINCT SectionID FROM ChapterSection
WHERE ChapterID IN
(SELECT DISTINCT ChapterID FROM ChapterLocation
WHERE LocationID IN
(SELECT LocationID FROM UserLocation WHERE UserID = @.UserID)
)
);
I realize that this might not be as clear as it should be but I'd appreciate
_any_ suggestions.
Thanks,
Dan> Could the following query be simplified/optimized?
I think the query below is equivalent. I think indexes on the columns in
the WHERE/JOIN clauses may help performance(e.g. a composite index on
UserSection UserID, SectionID).
SELECT SectionID, [Name]
FROM dbo.Section s
WHERE
NOT EXISTS(
SELECT * FROM dbo.UserSection us
WHERE
us.SectionID = s.SectionID
AND UserID = @.UserID
) AND
EXISTS(
SELECT * FROM dbo.ChapterSection cs
JOIN dbo.ChapterLocation cl ON
cl.ChapterID = cs.ChapterID
JOIN dbo.UserLocation ul ON
ul.LocationID = cl.LocationID
AND UserID = @.UserID)
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"dan" <dan@.company.com> wrote in message
news:uVUBR7mqIHA.3804@.TK2MSFTNGP02.phx.gbl...
> Hi,
> Could the following query be simplified/optimized? The database has a
> table that maps users to locations, locations to chapters, and sections to
> chapters. I need to issue a query that would return a list of sections
> that can be assigned to a user (excluding the ones that have already been
> assigned) .
> @.UserId int
> [...]
> SELECT SectionID, [Name],
> FROM Section
> WHERE SectionID NOT IN (SELECT SectionID FROM UserSection WHERE UserID => @.UserID) AND
> SectionID IN (SELECT DISTINCT SectionID FROM ChapterSection
> WHERE ChapterID IN
> (SELECT DISTINCT ChapterID FROM ChapterLocation
> WHERE LocationID IN
> (SELECT LocationID FROM UserLocation WHERE UserID = @.UserID)
> )
> );
> I realize that this might not be as clear as it should be but I'd
> appreciate _any_ suggestions.
> Thanks,
> Dan
>|||Thanks for the reply. The query looks clean but there seems to be a problem
with EXISTS() in the WHERE clause. Some of the sections included in the
result set are not assigned to the user's location(s) (implicitly through
chapters). I.e. the query selects chapters that are not assigned to user's
locations in addition to the chapters that are assigned.
Any suggestions?
Thanks,
Dan
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:A400C6C3-53B6-4A01-B0DD-182ACF421236@.microsoft.com...
>> Could the following query be simplified/optimized?
> I think the query below is equivalent. I think indexes on the columns in
> the WHERE/JOIN clauses may help performance(e.g. a composite index on
> UserSection UserID, SectionID).
> SELECT SectionID, [Name]
> FROM dbo.Section s
> WHERE
> NOT EXISTS(
> SELECT * FROM dbo.UserSection us
> WHERE
> us.SectionID = s.SectionID
> AND UserID = @.UserID
> ) AND
> EXISTS(
> SELECT * FROM dbo.ChapterSection cs
> JOIN dbo.ChapterLocation cl ON
> cl.ChapterID = cs.ChapterID
> JOIN dbo.UserLocation ul ON
> ul.LocationID = cl.LocationID
> AND UserID = @.UserID)
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
> "dan" <dan@.company.com> wrote in message
> news:uVUBR7mqIHA.3804@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> Could the following query be simplified/optimized? The database has a
>> table that maps users to locations, locations to chapters, and sections
>> to chapters. I need to issue a query that would return a list of sections
>> that can be assigned to a user (excluding the ones that have already been
>> assigned) .
>> @.UserId int
>> [...]
>> SELECT SectionID, [Name],
>> FROM Section
>> WHERE SectionID NOT IN (SELECT SectionID FROM UserSection WHERE UserID =>> @.UserID) AND
>> SectionID IN (SELECT DISTINCT SectionID FROM ChapterSection
>> WHERE ChapterID IN
>> (SELECT DISTINCT ChapterID FROM ChapterLocation
>> WHERE LocationID IN
>> (SELECT LocationID FROM UserLocation WHERE UserID = @.UserID)
>> )
>> );
>> I realize that this might not be as clear as it should be but I'd
>> appreciate _any_ suggestions.
>> Thanks,
>> Dan
>>
>|||> Thanks for the reply. The query looks clean but there seems to be a
> problem
> with EXISTS() in the WHERE clause. Some of the sections included in the
> result set are not assigned to the user's location(s) (implicitly through
> chapters). I.e. the query selects chapters that are not assigned to
> user's locations in addition to the chapters that are assigned.
> Any suggestions?
I missed the join from ChapterSection.SectionID back to Section.SectionID.
See the corrected query below. If this still doesn't work for you, please
post the table DDL and sample data so that I can test the solution.
SELECT SectionID, [Name]
FROM dbo.Section s
WHERE
NOT EXISTS(
SELECT * FROM dbo.UserSection us
WHERE
us.SectionID = s.SectionID
AND UserID = @.UserID
)
AND EXISTS(
SELECT * FROM dbo.ChapterSection cs
JOIN dbo.ChapterLocation cl ON
cl.ChapterID = cs.ChapterID
JOIN dbo.UserLocation ul ON
ul.LocationID = cl.LocationID
AND UserID = @.UserID
AND cs.SectionID = s.SectionID
)
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"dan" <dan@.company.com> wrote in message
news:eB2qg%23sqIHA.2492@.TK2MSFTNGP06.phx.gbl...
> Thanks for the reply. The query looks clean but there seems to be a
> problem with EXISTS() in the WHERE clause. Some of the sections included
> in the result set are not assigned to the user's location(s) (implicitly
> through chapters). I.e. the query selects chapters that are not assigned
> to user's locations in addition to the chapters that are assigned.
> Any suggestions?
> Thanks,
> Dan
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:A400C6C3-53B6-4A01-B0DD-182ACF421236@.microsoft.com...
>> Could the following query be simplified/optimized?
>> I think the query below is equivalent. I think indexes on the columns in
>> the WHERE/JOIN clauses may help performance(e.g. a composite index on
>> UserSection UserID, SectionID).
>> SELECT SectionID, [Name]
>> FROM dbo.Section s
>> WHERE
>> NOT EXISTS(
>> SELECT * FROM dbo.UserSection us
>> WHERE
>> us.SectionID = s.SectionID
>> AND UserID = @.UserID
>> ) AND
>> EXISTS(
>> SELECT * FROM dbo.ChapterSection cs
>> JOIN dbo.ChapterLocation cl ON
>> cl.ChapterID = cs.ChapterID
>> JOIN dbo.UserLocation ul ON
>> ul.LocationID = cl.LocationID
>> AND UserID = @.UserID)
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> http://weblogs.sqlteam.com/dang/
>> "dan" <dan@.company.com> wrote in message
>> news:uVUBR7mqIHA.3804@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> Could the following query be simplified/optimized? The database has a
>> table that maps users to locations, locations to chapters, and sections
>> to chapters. I need to issue a query that would return a list of
>> sections that can be assigned to a user (excluding the ones that have
>> already been assigned) .
>> @.UserId int
>> [...]
>> SELECT SectionID, [Name],
>> FROM Section
>> WHERE SectionID NOT IN (SELECT SectionID FROM UserSection WHERE UserID =>> @.UserID) AND
>> SectionID IN (SELECT DISTINCT SectionID FROM ChapterSection
>> WHERE ChapterID IN
>> (SELECT DISTINCT ChapterID FROM ChapterLocation
>> WHERE LocationID IN
>> (SELECT LocationID FROM UserLocation WHERE UserID = @.UserID)
>> )
>> );
>> I realize that this might not be as clear as it should be but I'd
>> appreciate _any_ suggestions.
>> Thanks,
>> Dan
>>
>|||Thanks. It works now.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:5F09A613-CEF3-45CA-B2EA-6A3D39CB69D5@.microsoft.com...
>> Thanks for the reply. The query looks clean but there seems to be a
>> problem
>> with EXISTS() in the WHERE clause. Some of the sections included in the
>> result set are not assigned to the user's location(s) (implicitly through
>> chapters). I.e. the query selects chapters that are not assigned to
>> user's locations in addition to the chapters that are assigned.
>> Any suggestions?
> I missed the join from ChapterSection.SectionID back to Section.SectionID.
> See the corrected query below. If this still doesn't work for you, please
> post the table DDL and sample data so that I can test the solution.
> SELECT SectionID, [Name]
> FROM dbo.Section s
> WHERE
> NOT EXISTS(
> SELECT * FROM dbo.UserSection us
> WHERE
> us.SectionID = s.SectionID
> AND UserID = @.UserID
> )
> AND EXISTS(
> SELECT * FROM dbo.ChapterSection cs
> JOIN dbo.ChapterLocation cl ON
> cl.ChapterID = cs.ChapterID
> JOIN dbo.UserLocation ul ON
> ul.LocationID = cl.LocationID
> AND UserID = @.UserID
> AND cs.SectionID = s.SectionID
> )
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
> "dan" <dan@.company.com> wrote in message
> news:eB2qg%23sqIHA.2492@.TK2MSFTNGP06.phx.gbl...
>> Thanks for the reply. The query looks clean but there seems to be a
>> problem with EXISTS() in the WHERE clause. Some of the sections included
>> in the result set are not assigned to the user's location(s) (implicitly
>> through chapters). I.e. the query selects chapters that are not assigned
>> to user's locations in addition to the chapters that are assigned.
>> Any suggestions?
>> Thanks,
>> Dan
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> news:A400C6C3-53B6-4A01-B0DD-182ACF421236@.microsoft.com...
>> Could the following query be simplified/optimized?
>> I think the query below is equivalent. I think indexes on the columns
>> in the WHERE/JOIN clauses may help performance(e.g. a composite index on
>> UserSection UserID, SectionID).
>> SELECT SectionID, [Name]
>> FROM dbo.Section s
>> WHERE
>> NOT EXISTS(
>> SELECT * FROM dbo.UserSection us
>> WHERE
>> us.SectionID = s.SectionID
>> AND UserID = @.UserID
>> ) AND
>> EXISTS(
>> SELECT * FROM dbo.ChapterSection cs
>> JOIN dbo.ChapterLocation cl ON
>> cl.ChapterID = cs.ChapterID
>> JOIN dbo.UserLocation ul ON
>> ul.LocationID = cl.LocationID
>> AND UserID = @.UserID)
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> http://weblogs.sqlteam.com/dang/
>> "dan" <dan@.company.com> wrote in message
>> news:uVUBR7mqIHA.3804@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> Could the following query be simplified/optimized? The database has a
>> table that maps users to locations, locations to chapters, and sections
>> to chapters. I need to issue a query that would return a list of
>> sections that can be assigned to a user (excluding the ones that have
>> already been assigned) .
>> @.UserId int
>> [...]
>> SELECT SectionID, [Name],
>> FROM Section
>> WHERE SectionID NOT IN (SELECT SectionID FROM UserSection WHERE UserID
>> = @.UserID) AND
>> SectionID IN (SELECT DISTINCT SectionID FROM ChapterSection
>> WHERE ChapterID IN
>> (SELECT DISTINCT ChapterID FROM ChapterLocation
>> WHERE LocationID IN
>> (SELECT LocationID FROM UserLocation WHERE UserID =>> @.UserID)
>> )
>> );
>> I realize that this might not be as clear as it should be but I'd
>> appreciate _any_ suggestions.
>> Thanks,
>> Dan
>>
>>
>

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