How to return multiple columns from FLWOR

I have very simple xml shredding requirement. What I would like to do is to get value of SourcesID and SourcesType returned as columns from the following. Currently I could get both values concatenated but requirement dictate having both values returned as separate columns. Please notice that given statement is a simplified version and I understand using Value function would be an easier solution but I have set of conditions within for that needs to be evaluated before I select SourcesID node. So, I would appreciate if the solution proposed is along the line of given code.

DECLARE @.xml XML

SET @.xml =

'<State>

<SourceState>

<SourcesID>57341</SourcesID>

<SourcesType>50</SourcesType>

</SourceState>

</State>'

SELECT

Element.Val.query(

'for $s in self::node()

return $s//SourcesID/text(),

for $s in self::node()

return $s//SourcesType/text()

') AS SourcesID

FROM @.xml.nodes('/State') AS Element(Val)

Asaf:

I think that I still don't understand what you are looking for in terms of output. Here is my first pass at trying to understand your needs. Please comment so that I might be able to properly target this.

Code Snippet

DECLARE @.xml XML
SET @.xml =
'<State>
<SourceState>
<SourcesID>57341</SourcesID>
<SourcesType>50</SourcesType>
</SourceState>
</State>'
select parsename(cast(SourcesID as varchar), 2) as SourcesID,
substring(parseName(cast(SourcesID as varchar), 1), 2, 99)
as SourcesType
from ( SELECT
Element.Val.query(
'for $s in self::node()/SourceState/SourcesID/text()
return concat(string($s),"."),
for $s in self::node()/SourceState/SourcesType/text()
return string($s)
') AS SourcesID
FROM @.xml.nodes('/State') AS Element(Val)
) x

/*
SourcesID SourcesType
-- -
57341 50
*/

|||

Hi there,

I am sorry for having a bit confusing question but the proposed solution from you was what I needed. What I was hoping, though, was to basically get two columns returned from FLWOR without any concatenation and parsing of returned resultset later on.

Thanks very much.

|||

It is not clear what kind of condition you have but note that you can also apply conditions in path expressions in square brackets so perhaps your problem can be solved by simply applying the condition already when you use the nodes function e.g.

FROM @.xml.nodes('/State[SourceState/SourcesType > 40]') AS Element(Val)

then you can apply the value method to get two columns in the result.

|||

Code Snippet

DECLARE @.xml XML
SET @.xml =
'<State>
<SourceState>
<SourcesID>57341</SourcesID>
<SourcesType>50</SourcesType>
</SourceState>
</State>'
SELECT
Element.Val.query(
'for $s in self::node()
return $s//SourcesID/text()') as a,
Element.val.query(
'for $s in self::node()
return $s//SourcesType/text()') as b
FROM @.xml.nodes('/State') AS Element(Val)

/*
a b
-
57341 50
*/

How to return multiple columns from FLWOR

I have very simple xml shredding requirement. What I would like to do is to get value of SourcesID and SourcesType returned as columns from the following. Currently I could get both values concatenated but requirement dictate having both values returned as separate columns. Please notice that given statement is a simplified version and I understand using Value function would be an easier solution but I have set of conditions within for that needs to be evaluated before I select SourcesID node. So, I would appreciate if the solution proposed is along the line of given code.

DECLARE @.xml XML

SET @.xml =

'<State>

<SourceState>

<SourcesID>57341</SourcesID>

<SourcesType>50</SourcesType>

</SourceState>

</State>'

SELECT

Element.Val.query(

'for $s in self::node()

return $s//SourcesID/text(),

for $s in self::node()

return $s//SourcesType/text()

') AS SourcesID

FROM @.xml.nodes('/State') AS Element(Val)

Asaf:

I think that I still don't understand what you are looking for in terms of output. Here is my first pass at trying to understand your needs. Please comment so that I might be able to properly target this.

Code Snippet

DECLARE @.xml XML
SET @.xml =
'<State>
<SourceState>
<SourcesID>57341</SourcesID>
<SourcesType>50</SourcesType>
</SourceState>
</State>'
select parsename(cast(SourcesID as varchar), 2) as SourcesID,
substring(parseName(cast(SourcesID as varchar), 1), 2, 99)
as SourcesType
from ( SELECT
Element.Val.query(
'for $s in self::node()/SourceState/SourcesID/text()
return concat(string($s),"."),
for $s in self::node()/SourceState/SourcesType/text()
return string($s)
') AS SourcesID
FROM @.xml.nodes('/State') AS Element(Val)
) x

/*
SourcesID SourcesType
-- -
57341 50
*/

|||

Hi there,

I am sorry for having a bit confusing question but the proposed solution from you was what I needed. What I was hoping, though, was to basically get two columns returned from FLWOR without any concatenation and parsing of returned resultset later on.

Thanks very much.

|||

It is not clear what kind of condition you have but note that you can also apply conditions in path expressions in square brackets so perhaps your problem can be solved by simply applying the condition already when you use the nodes function e.g.

FROM @.xml.nodes('/State[SourceState/SourcesType > 40]') AS Element(Val)

then you can apply the value method to get two columns in the result.

|||

Code Snippet

DECLARE @.xml XML
SET @.xml =
'<State>
<SourceState>
<SourcesID>57341</SourcesID>
<SourcesType>50</SourcesType>
</SourceState>
</State>'
SELECT
Element.Val.query(
'for $s in self::node()
return $s//SourcesID/text()') as a,
Element.val.query(
'for $s in self::node()
return $s//SourcesType/text()') as b
FROM @.xml.nodes('/State') AS Element(Val)

/*
a b
-
57341 50
*/

How to return a value if recordcount = 0

How would I return a select statement if the records returned is 0
i.e
Select * from orders where employeeID = 0
if @.RowCount = 0
--Return Shipname as 'None'
Select 'None' = shipname ect,
Thanks
Stephen K. Miyasato
MDsyncHi Stephen,
I would do something like...
SELECT * FROM Orders where EmployeeID = 0
IF @.@.ROWCOUNT = 0
SELECT 'None' As ShipName
or
IF EXISTS (SELECT * FROM Orders Where EmployeeID = 0)
SELECT * FROM Orders WHERE EmployeeID = 0
ELSE
SELECT 'None' As ShipName
Both examples would return a single row with a single column called
[ShipName] with the value 'None' in the event that no rows exist with an
employeeID of 0.
Rich.
"Stephen K. Miyasato" <miyasat@.flex.com> wrote in message
news:OWMcSE6cGHA.564@.TK2MSFTNGP02.phx.gbl...
> How would I return a select statement if the records returned is 0
> i.e
> Select * from orders where employeeID = 0
> if @.RowCount = 0
> --Return Shipname as 'None'
> Select 'None' = shipname ect,
>
> Thanks
> Stephen K. Miyasato
> MDsync
>|||Try this:
Select * from orders where employeeID = 0
if @.@.RowCount = 0
begin
--Return Shipname as 'None'
Select 'None' as 'shipname'
end|||Thanks but I could not get this to work.
Here is the actual query:
The query as it now is return two sets. The first set has 0 rows, the next
set is the one I need to produce.
if I use Exist(Select ..), I get and error since I have NTEXT values and
order by is not allowed.
Any other suggustions?
Stephen K. Miyasato
/****** Object: Table [dbo].[orderTest] Script Date: 5/9/2006 12:42:23
PM ******/
if not exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[orderTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [orderTest] (
[PatNo] [int] NULL ,
[Name] [varchar] (24) NULL ,
[Dosage] [varchar] (25) NULL ,
[Sig] [varchar] (30) NULL ,
[PatSig] [varchar] (50) NULL ,
[RxNo] [int] NULL ,
[Breakfast] [varchar] (1) NULL ,
[Lunch] [varchar] (1) NULL ,
[Dinner] [varchar] (1) NULL ,
[QHS] [varchar] (1) NULL ,
[Comments] [varchar] (60) NULL ,
[QuantityL] [varchar] (20) NULL ,
[Quantity] [float] NULL ,
[Generic] [varchar] (60) NULL ,
[BrandName] [varchar] (60) NULL ,
[MDrx] [int] NULL ,
[Category_id] [int] NULL ,
[Daily] [smallint] NULL ,
[Memo] [ntext] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
Declare @.PatNo int
Set @.PatNo = 7160
SELECT PatNo, Name, Dosage, Sig, PatSig, RxNo, Breakfast, Lunch,
Dinner, QHS,
Comments, QuantityL, Quantity, Generic, BrandName,
MDrx, Category_id, Daily,
Memo
FROM orderTest
WHERE (PatNo = @.PatNo) AND (StopDate IS NULL)
ORDER BY Daily
if @.@.RowCount = 0
SELECT @.PatNo AS PatNo,Null as Name, Null as Dosage, Null as Sig, Null
as PatSig, Null as RxNo, Null as Breakfast, Null as Lunch, Null as
Dinner,Null as QHS,
Null as Comments,Null as QuantityL, Null as Quantity,
Null as Generic, 'None' AS BrandName, Null as MDrx, Null as Category_id,
Null as Daily,
Null as Memo
"Richard O'Brien" <groups@.rjoconsulting.co.uk> wrote in message
news:ecp6gH6cGHA.4912@.TK2MSFTNGP05.phx.gbl...
> Hi Stephen,
> I would do something like...
> SELECT * FROM Orders where EmployeeID = 0
> IF @.@.ROWCOUNT = 0
> SELECT 'None' As ShipName
> or
> IF EXISTS (SELECT * FROM Orders Where EmployeeID = 0)
> SELECT * FROM Orders WHERE EmployeeID = 0
> ELSE
> SELECT 'None' As ShipName
> Both examples would return a single row with a single column called
> [ShipName] with the value 'None' in the event that no rows exist with an
> employeeID of 0.
> Rich.
> "Stephen K. Miyasato" <miyasat@.flex.com> wrote in message
> news:OWMcSE6cGHA.564@.TK2MSFTNGP02.phx.gbl...
>|||Stephen,
Select * from orders where employeeID = 0
union all
select
-- whatever values you want
1,2,3,4,...
where not exists(Select 1 from orders where employeeID = 0)|||StopDate isn't a column in the table definition you gave.
Try this.
Declare @.PatNo int
Set @.PatNo = 7160
if exists (select * from ordertest where (PatNo = @.PatNo)) -- AND
(StopDate IS NULL))
begin
SELECT PatNo, Name, Dosage, Sig, PatSig, RxNo, Breakfast, Lunch,
Dinner, QHS,
Comments, QuantityL, Quantity, Generic,
BrandName,
MDrx, Category_id, Daily,
Memo
FROM orderTest
WHERE (PatNo = @.PatNo)-- AND (StopDate IS NULL)
ORDER BY Daily
end
else begin
SELECT @.PatNo AS PatNo,Null as Name, Null as Dosage, Null as Sig,
Null
as PatSig, Null as RxNo, Null as Breakfast, Null as Lunch, Null as
Dinner,Null as QHS,
Null as Comments,Null as QuantityL, Null as
Quantity,
Null as Generic, 'None' AS BrandName, Null as MDrx, Null as
Category_id,
Null as Daily,
Null as Memo
end

How to return a value from Stored Procedure?

Hi,
I am calling a stored procedure from my Java script app and trying to use
the returned value later on in my app.
Here is the javascript code.
var connObj = Server.CreateObject("ADODB.connection");
connObj.Open("DRIVER={ SQL Server }; SERVER = 9.1.2.1;
DATABASE=TEST","sa","xyz");
var recordSetObj = Server.CreateObject("ADODB.Recordset");
var StrName = "venkat";
var RetVal;
var query = "Exec ProcSP '"+StrName+'", '"+RetVal+"'";
recordSetObj.Open(query,connObj);
Response.Write("Return Value= "+RetVal);
/******Stored Procedure********/
Create Procedure ProcSP
@.StrName nvarchar(10),
@.RetVal integer OUTPUT
AS
SET RetVal = 1
return
GO
The value returned is empty, even if I initialize RetVal to 2 say before the
call of Store procedure then once the execution of stored procedure is
finished it prints as 2 only. It is not getting changed to 1.
Please note the stored procedure is working fine if i execute any queries, i
mean the stored procedure is getting called from the app and is working
properly the only thing is i am not able to return any value from it.
I greatly appreciate anyhelp on this, i need to fix this issue ASAP, i am
running short of time. Please help me.
Regards,
VenkatHave you tried using parameters and the command object?
That's typically how you retrieve the values of output
parameters and return values in ADO.
-Sue
On Fri, 28 Nov 2003 11:09:31 +0530, "Venkat"
<venkat_kp@.yahoo.com> wrote:

quote:

---

>Hi,
>I am calling a stored procedure from my Java script app and trying to use
>the returned value later on in my app.
>Here is the javascript code.
>var connObj = Server.CreateObject("ADODB.connection");
>connObj.Open("DRIVER={ SQL Server }; SERVER = 9.1.2.1;
>DATABASE=TEST","sa","xyz");
>var recordSetObj = Server.CreateObject("ADODB.Recordset");
>var StrName = "venkat";
>var RetVal;
>var query = "Exec ProcSP '"+StrName+'", '"+RetVal+"'";
>recordSetObj.Open(query,connObj);
>Response.Write("Return Value= "+RetVal);
>
>/******Stored Procedure********/
>Create Procedure ProcSP
>@.StrName nvarchar(10),
>@.RetVal integer OUTPUT
>AS
>SET RetVal = 1
>return
>GO
>
>The value returned is empty, even if I initialize RetVal to 2 say before th
e
>call of Store procedure then once the execution of stored procedure is
>finished it prints as 2 only. It is not getting changed to 1.
>Please note the stored procedure is working fine if i execute any queries,
i
>mean the stored procedure is getting called from the app and is working
>properly the only thing is i am not able to return any value from it.
>
>I greatly appreciate anyhelp on this, i need to fix this issue ASAP, i am
>running short of time. Please help me.
>
>Regards,
>Venkat
>
>
>

---