@XMLVariable.query

How do I get the value of ID when an attribute is part of the XML? The
following script shows the problem. It should be ready to execute,
depending on wrapping. Test1 works; test2 and test3 have an attribute, and
do not work.
Thanks,
Richard
set nocount on
use tempdb
go
declare @.Message xml
select @.Message =
N'<?xml version="1.0" standalone="yes" ?>
<DataSet>
<Request>
<ID>14</ID>
</Request>
</DataSet>'
select @.Message.query( 'data( /DataSet/Request/ID )' ) as Test1
go
declare @.Message xml
select @.Message =
N'<?xml version="1.0" standalone="yes" ?>
<DataSet xmlns="http://tempuri.org/DataSet.xsd">
<Request>
<ID>14</ID>
</Request>
</DataSet>'
select @.Message.query( 'data( /DataSet/Request/ID )' ) as Test2
go
declare @.Message xml
select @.Message =
N'<?xml version="1.0" standalone="yes" ?>
<DataSet xmlns="http://tempuri.org/DataSet.xsd">
<Request>
<ID>14</ID>
</Request>
</DataSet>'
select @.Message.query( 'data( /DataSet
xmlns="http://tempuri.org/DataSet.xsd"/Request/ID )' ) as Test3
goDoes this help?
declare @.Message xml
select @.Message =
N'<?xml version="1.0" standalone="yes" ?>
<DataSet xmlns="http://tempuri.org/DataSet.xsd">
<Request>
<ID>14</ID>
</Request>
</DataSet>'
select @.Message.query( 'declare default element namespace
"http://tempuri.org/DataSet.xsd";
data( /DataSet/Request/ID )' ) as Test2 ;
with xmlnamespaces(default 'http://tempuri.org/DataSet.xsd' )
select @.Message.query( 'data( /DataSet/Request/ID )' ) as Test2|||Your xml document is using a default namespace which must be defined somewhe
re.
Here is an example of one way to do it, note the use of the semicolon.
declare @.Message xml
select @.Message =
N'<?xml version="1.0" standalone="yes" ?>
<DataSet xmlns="http://tempuri.org/DataSet.xsd">
<Request>
<ID>14</ID>
</Request>
</DataSet>';
WITH XMLNAMESPACES (DEFAULT 'http://tempuri.org/DataSet.xsd')
select @.Message.query( 'data( /DataSet/Request/ID )' ) as Test2
Dan

> How do I get the value of ID when an attribute is part of the XML?
> The following script shows the problem. It should be ready to
> execute, depending on wrapping. Test1 works; test2 and test3 have an
> attribute, and do not work.
> Thanks,
> Richard
> set nocount on
> use tempdb
> go
> declare @.Message xml
> select @.Message =
> N'<?xml version="1.0" standalone="yes" ?>
> <DataSet>
> <Request>
> <ID>14</ID>
> </Request>
> </DataSet>'
> select @.Message.query( 'data( /DataSet/Request/ID )' ) as Test1
> go
> declare @.Message xml
> select @.Message =
> N'<?xml version="1.0" standalone="yes" ?>
> <DataSet xmlns="http://tempuri.org/DataSet.xsd">
> <Request>
> <ID>14</ID>
> </Request>
> </DataSet>'
> select @.Message.query( 'data( /DataSet/Request/ID )' ) as Test2
> go
> declare @.Message xml
> select @.Message =
> N'<?xml version="1.0" standalone="yes" ?>
> <DataSet xmlns="http://tempuri.org/DataSet.xsd">
> <Request>
> <ID>14</ID>
> </Request>
> </DataSet>'
> select @.Message.query( 'data( /DataSet
> xmlns="http://tempuri.org/DataSet.xsd"/Request/ID )' ) as Test3 go
>|||Perfect! Thanks for the quick replies.
"Richard" <napa299@.yahoo.com> wrote in message
news:%23c%23EM$miGHA.4284@.TK2MSFTNGP05.phx.gbl...
> How do I get the value of ID when an attribute is part of the XML? The
> following script shows the problem. It should be ready to execute,
> depending on wrapping. Test1 works; test2 and test3 have an attribute,
> and do not work.
> Thanks,
> Richard
> set nocount on
> use tempdb
> go
> declare @.Message xml
> select @.Message =
> N'<?xml version="1.0" standalone="yes" ?>
> <DataSet>
> <Request>
> <ID>14</ID>
> </Request>
> </DataSet>'
> select @.Message.query( 'data( /DataSet/Request/ID )' ) as Test1
> go
> declare @.Message xml
> select @.Message =
> N'<?xml version="1.0" standalone="yes" ?>
> <DataSet xmlns="http://tempuri.org/DataSet.xsd">
> <Request>
> <ID>14</ID>
> </Request>
> </DataSet>'
> select @.Message.query( 'data( /DataSet/Request/ID )' ) as Test2
> go
> declare @.Message xml
> select @.Message =
> N'<?xml version="1.0" standalone="yes" ?>
> <DataSet xmlns="http://tempuri.org/DataSet.xsd">
> <Request>
> <ID>14</ID>
> </Request>
> </DataSet>'
> select @.Message.query( 'data( /DataSet
> xmlns="http://tempuri.org/DataSet.xsd"/Request/ID )' ) as Test3
> go
>

@return_status from SQLCLR stored procedure

I'm trying to write a VB CLR stored procedure that will set the return statu
s
to 0 or 1 depending on whether the CLR was successful or not. How do I do
that? Basically I want to use the following T-SQL code to call my CLR and
set the @.return_status
declare @.return_status int
EXEC @.return_status=My_CLR_SP
select @.return_statusI would like to know how to do this as well. Can anyone help us?
Greg Larsen wrote:
> I'm trying to write a VB CLR stored procedure that will set the return sta
tus
> to 0 or 1 depending on whether the CLR was successful or not. How do I do
> that? Basically I want to use the following T-SQL code to call my CLR and
> set the @.return_status
> declare @.return_status int
> EXEC @.return_status=My_CLR_SP
> select @.return_status|||Greg,
I found this at http://msdn2.microsoft.com/en-us/library/ms131094.aspx.
I tried it out and it works great.
Here is a really simple example:
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Function TestProc() As Int32
TestProc = 1
End Function
And the test script:
DECLARE @.result AS int
EXEC @.result = TestProc
PRINT @.result
Tom
Greg Larsen wrote:
> I'm trying to write a VB CLR stored procedure that will set the return sta
tus
> to 0 or 1 depending on whether the CLR was successful or not. How do I do
> that? Basically I want to use the following T-SQL code to call my CLR and
> set the @.return_status
> declare @.return_status int
> EXEC @.return_status=My_CLR_SP
> select @.return_status|||Thank you for the VB example.
I got mine to working by writing it in C # and doing the following:
[Microsoft.SqlServer.Server.SqlProcedure]
public static int TestProc()
{ return=1;}|||You're welcome. Thanks for posting the C# example.
Tom
Greg Larsen wrote:
> Thank you for the VB example.
> I got mine to working by writing it in C # and doing the following:
> [Microsoft.SqlServer.Server.SqlProcedure]
> public static int TestProc()
> { return=1;}

@@RowCount

I want to run one of two SELECT statements, one with a Join the other
without, depending on wheteher the Join fails because of an unrelated record
.
The second statement should always return one row as the @.RequestID value
being passed comes from a Tree selection. But no row is returned. Where am I
going wrong? TIA
I am using:
CREATE PROCEDURE tdGetRequestor
@.RequestID BigInt
AS
SELECT tblRequests.RequestID, tblRequests.RequestedBy,
tblRequests.RequestDate, tblRequests.RequestorSectionID,
tblSections.SectionName AS RequestorSection
FROM tblRequests
INNER JOIN tblSections ON tblRequests.RequestorSectionID =
tblSections.SectionID
WHERE tblRequests.RequestID = @.RequestID
IF @.@.ROWCOUNT=0
BEGIN
SELECT RequestID, RequestedBy, RequestDate,RequestorSectionID
FROM tblRequests
WHERE tblRequests.RequestID = @.RequestID
END
GOHave you tried the second query in outside of the SP by providing @.RequestID
manually?
"td" <td@.discussions.microsoft.com> wrote in message
news:6DC4AB4F-DEF2-478E-B873-30422C1207AF@.microsoft.com...
>I want to run one of two SELECT statements, one with a Join the other
> without, depending on wheteher the Join fails because of an unrelated
> record.
> The second statement should always return one row as the @.RequestID value
> being passed comes from a Tree selection. But no row is returned. Where am
> I
> going wrong? TIA
> I am using:
> CREATE PROCEDURE tdGetRequestor
> @.RequestID BigInt
> AS
> SELECT tblRequests.RequestID, tblRequests.RequestedBy,
> tblRequests.RequestDate, tblRequests.RequestorSectionID,
> tblSections.SectionName AS RequestorSection
> FROM tblRequests
> INNER JOIN tblSections ON tblRequests.RequestorSectionID =
> tblSections.SectionID
> WHERE tblRequests.RequestID = @.RequestID
> IF @.@.ROWCOUNT=0
> BEGIN
> SELECT RequestID, RequestedBy, RequestDate,RequestorSectionID
> FROM tblRequests
> WHERE tblRequests.RequestID = @.RequestID
> END
> GO|||I'm not sure there is an use for RowCount here. Is there a reason why you
can't use an outer join? This should return the same results, with NULL for
tblSections.SectionName if a related record does not exist.
CREATE PROCEDURE tdGetRequestor
@.RequestID BigInt
AS
SELECT
tblRequests.RequestID
, tblRequests.RequestedBy
, tblRequests.RequestDate
, tblRequests.RequestorSectionID
, tblSections.SectionName AS RequestorSection
FROM tblRequests
LEFT OUTER JOIN tblSections
ON tblRequests.RequestorSectionID = tblSections.SectionID
WHERE tblRequests.RequestID = @.RequestID
GO
"td" <td@.discussions.microsoft.com> wrote in message
news:6DC4AB4F-DEF2-478E-B873-30422C1207AF@.microsoft.com...
> I want to run one of two SELECT statements, one with a Join the other
> without, depending on wheteher the Join fails because of an unrelated
record.
> The second statement should always return one row as the @.RequestID value
> being passed comes from a Tree selection. But no row is returned. Where am
I
> going wrong? TIA
> I am using:
> CREATE PROCEDURE tdGetRequestor
> @.RequestID BigInt
> AS
> SELECT tblRequests.RequestID, tblRequests.RequestedBy,
> tblRequests.RequestDate, tblRequests.RequestorSectionID,
> tblSections.SectionName AS RequestorSection
> FROM tblRequests
> INNER JOIN tblSections ON tblRequests.RequestorSectionID =
> tblSections.SectionID
> WHERE tblRequests.RequestID = @.RequestID
> IF @.@.ROWCOUNT=0
> BEGIN
> SELECT RequestID, RequestedBy, RequestDate,RequestorSectionID
> FROM tblRequests
> WHERE tblRequests.RequestID = @.RequestID
> END
> GO|||While I agree with Jim that you proably could use an outer join for this
(almost assuredly) have you tried to run this in Query Analyzer? See what
happens there. If you get two result sets, then it is probably something in
how you are trying to deal with 2 resultsets (which would be a question for
someone in the newsgroup that corresponds to your language (programming
language, of course :))
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"td" <td@.discussions.microsoft.com> wrote in message
news:6DC4AB4F-DEF2-478E-B873-30422C1207AF@.microsoft.com...
>I want to run one of two SELECT statements, one with a Join the other
> without, depending on wheteher the Join fails because of an unrelated
> record.
> The second statement should always return one row as the @.RequestID value
> being passed comes from a Tree selection. But no row is returned. Where am
> I
> going wrong? TIA
> I am using:
> CREATE PROCEDURE tdGetRequestor
> @.RequestID BigInt
> AS
> SELECT tblRequests.RequestID, tblRequests.RequestedBy,
> tblRequests.RequestDate, tblRequests.RequestorSectionID,
> tblSections.SectionName AS RequestorSection
> FROM tblRequests
> INNER JOIN tblSections ON tblRequests.RequestorSectionID =
> tblSections.SectionID
> WHERE tblRequests.RequestID = @.RequestID
> IF @.@.ROWCOUNT=0
> BEGIN
> SELECT RequestID, RequestedBy, RequestDate,RequestorSectionID
> FROM tblRequests
> WHERE tblRequests.RequestID = @.RequestID
> END
> GO|||Save @.@.rowcount in an integer variable immediately after the Transact-SQL
statement completes. The value of the variable can be used later.
declare @.rowcount int
set @.rowcount = @.@.rowcount
HTH
Rajesh Peddireddy
"td" wrote:

> I want to run one of two SELECT statements, one with a Join the other
> without, depending on wheteher the Join fails because of an unrelated reco
rd.
> The second statement should always return one row as the @.RequestID value
> being passed comes from a Tree selection. But no row is returned. Where am
I
> going wrong? TIA
> I am using:
> CREATE PROCEDURE tdGetRequestor
> @.RequestID BigInt
> AS
> SELECT tblRequests.RequestID, tblRequests.RequestedBy,
> tblRequests.RequestDate, tblRequests.RequestorSectionID,
> tblSections.SectionName AS RequestorSection
> FROM tblRequests
> INNER JOIN tblSections ON tblRequests.RequestorSectionID =
> tblSections.SectionID
> WHERE tblRequests.RequestID = @.RequestID
> IF @.@.ROWCOUNT=0
> BEGIN
> SELECT RequestID, RequestedBy, RequestDate,RequestorSectionID
> FROM tblRequests
> WHERE tblRequests.RequestID = @.RequestID
> END
> GO|||Good thinking! I'm glad I thought of that (=: (or should have thought of tha
t)
"Jim Underwood" wrote:

> I'm not sure there is an use for RowCount here. Is there a reason why you
> can't use an outer join? This should return the same results, with NULL f
or
> tblSections.SectionName if a related record does not exist.
> CREATE PROCEDURE tdGetRequestor
> @.RequestID BigInt
> AS
> SELECT
> tblRequests.RequestID
> , tblRequests.RequestedBy
> , tblRequests.RequestDate
> , tblRequests.RequestorSectionID
> , tblSections.SectionName AS RequestorSection
> FROM tblRequests
> LEFT OUTER JOIN tblSections
> ON tblRequests.RequestorSectionID = tblSections.SectionID
> WHERE tblRequests.RequestID = @.RequestID
> GO
> "td" <td@.discussions.microsoft.com> wrote in message
> news:6DC4AB4F-DEF2-478E-B873-30422C1207AF@.microsoft.com...
> record.
> I
>
>|||Yes. It works fine. As I mentioned, the second statement will always return
a
row.
"Leila" wrote:

> Have you tried the second query in outside of the SP by providing @.Request
ID
> manually?
>
> "td" <td@.discussions.microsoft.com> wrote in message
> news:6DC4AB4F-DEF2-478E-B873-30422C1207AF@.microsoft.com...
>
>|||Hello Louis, Thanks for your input.
I did take Jim's advise & it works fine.
On your suggestion I have run the query in Query Analyser and it does run
correctly. In VB .Net the Reader is reporting that .HasRows is false.
Thanks to all for your suggestions
What gets returned by SQL Server in a situation such as this? It does seem
that it is reporting the zero rows back from the first query and, perhaps,
returning the result of the second as well?
In Query Analyser two distinct results ARE displayed. I will write some more
code and check what is happening in VB .Net.
td
"Louis Davidson" wrote:

> While I agree with Jim that you proably could use an outer join for this
> (almost assuredly) have you tried to run this in Query Analyzer? See what
> happens there. If you get two result sets, then it is probably something
in
> how you are trying to deal with 2 resultsets (which would be a question fo
r
> someone in the newsgroup that corresponds to your language (programming
> language, of course :))
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often convincing.
"
> (Oscar Wilde)
> "td" <td@.discussions.microsoft.com> wrote in message
> news:6DC4AB4F-DEF2-478E-B873-30422C1207AF@.microsoft.com...
>
>