Wednesday, March 28, 2012
How to Saving Multiple ID in One Column
I need one Table Design information.
My requirement is:
I have some products (say 10 products) in my master table. Each product has
Product ID (PK).
ID Code Desc
--
1 A1 AAAA
2 B1 BBBBBB
etc. etc...
I have one interface where the user will select the products that he is
interestered with. Mean One user may select product ID 1, 3, 5 and other may
select 2, 7.
I have to save the product info selected by that user in one Column of the
user table.
(I have to save the selected product in one column only)
So What Type will be for that Column and how do i implement logic to save
all selected product so that it will be easy to save and extract the values
also.
I think I can use some binary kind of valu for each product and save the
selected binary combination or similar logic to store and retrieve.
Please suggest.
Thanks
PrabhatPrabhat
Use RDBMS. Split the User table into produtsSeclected and use usertable for
storing base information only. then you will have three tables
Products where product id is primary
users where users id is primary
and Junction Table : Products selected with foreign keys of productid and
userid. thus participating in many to many relationship.
Regards
R.D
"Prabhat" wrote:
> Dear Sir,
> I need one Table Design information.
> My requirement is:
> I have some products (say 10 products) in my master table. Each product ha
s
> Product ID (PK).
> ID Code Desc
> --
> 1 A1 AAAA
> 2 B1 BBBBBB
> etc. etc...
> I have one interface where the user will select the products that he is
> interestered with. Mean One user may select product ID 1, 3, 5 and other m
ay
> select 2, 7.
> I have to save the product info selected by that user in one Column of the
> user table.
> (I have to save the selected product in one column only)
> So What Type will be for that Column and how do i implement logic to save
> all selected product so that it will be easy to save and extract the value
s
> also.
> I think I can use some binary kind of valu for each product and save the
> selected binary combination or similar logic to store and retrieve.
> Please suggest.
> Thanks
> Prabhat
>
>|||However this is the example of bad DB design where you keep multiple
data in single column, you can make that column of varchar type and
build a string of product ID and use comma (,) separator to saperate
each Product ID.
While fetching you ll only need to split IDs from comma|||What is Bad Design ? Normalization or CSVs for OLTP:
IF you forget about Data intigrity and future intigration problems only
then you can use comma seperated values
this sipmle scenario is not possible with csv.
If you want to write a query to select particular product selected by
multiple users,
is it possible when u use CSV.
Regards
R.D
"Zero.NULL" wrote:
> However this is the example of bad DB design where you keep multiple
> data in single column, you can make that column of varchar type and
> build a string of product ID and use comma (,) separator to saperate
> each Product ID.
> While fetching you ll only need to split IDs from comma
>|||Hi R.D.,
You are right. I know the concept and I know we can use 3 tables for that.
But My question was, Is there a better way of calculation / procedure that
we can store all selected value in one column?
Also the Comma separated method is very critical, as if products will
increase we need to increase the width of the field.
Thanks
Prabhat
"R.D" <RD@.discussions.microsoft.com> wrote in message
news:4A593B0A-B72F-4D7F-9FD4-43E851E2C70B@.microsoft.com...
> Prabhat
> Use RDBMS. Split the User table into produtsSeclected and use usertable
for
> storing base information only. then you will have three tables
> Products where product id is primary
> users where users id is primary
> and Junction Table : Products selected with foreign keys of productid and
> userid. thus participating in many to many relationship.
> Regards
> R.D
>
How to save the " character in a CDATA in SQL SErver 2005
Dear Pals,
I know that special characters, such as <, in a cdata section will be converted properly into entity reference before getting stored in a xml field in the database.
However, it seems that the character " (quotation) does not get converted to ". This would result in problems when the XML document is fetched.
For example, a xml document like:
<A><![CDATA[THis is "John"]]></A>
when stored, it becomes
<A>THis is "John"</A>
This of course causes problem for XML parsers.
IS there any cure for that problem.
Thanks
Feng-Hsu Wang
Feng-Hsu,
According to the XML spec:
The ampersand character (&) and the left angle bracket (<) MUST NOT appear in their literal form, except when used as markup delimiters, or within a comment, a processing instruction, or a CDATA section. If they are needed elsewhere, they MUST be escaped using either numeric character references or the strings "
&" and "<" respectively. The right angle bracket (>) may be represented using the string ">", and MUST, for compatibility, be escaped using either ">" or a character reference when it appears in the string "]]>" in content, when that string is not marking the end of a CDATA section.In the content of elements, character data is any string of characters which does not contain the start-delimiter of any markup and does not include the CDATA-section-close delimiter, "
]]>". In a CDATA section, character data is any string of characters not including the CDATA-section-close delimiter, "]]>".To allow attribute values to contain both single and double quotes, the apostrophe or single-quote character (') may be represented as "
'", and the double-quote character (") as """.
As I understand the XML spec, a single quote and a double quote character does not need to be escaped inside an element value. They only need to be escaped inside an attribute value.
So, this should not be causing any problems for XML parsers.
Jimmy Wu
|||<A>THis is "John"</A>
is well-formed xml. That means it is following all the syntax rules for xml. If you are getting an error from an xml parser reading this then that parser is broken.
Dan
sql
How to Save MS Word/Excel Files in Database?
i have to save a word doc or an excel file in the database in the module given to me where .net is the platform that i am using, can some one help me out in this.
thanks in advance
Regards
PuligaHi Puliga,
Have you found the answer yet?
I like to do the same over here.
regards
Pleun|||I would suggest storing full path to those files only and not files them selves.
Even if you will manage storing data in a database, you will encounter slowness in retrieving those documents and will convert to my first idea anyway... :)
Monday, March 26, 2012
How to run user defined function on sql server 2005
Dear all
I wants to run sql server user defined function when linked two server.
I have linked two sql server.There is one function called getenc().This function created on first server.What i want.I wants to run this user defined function on the second sql server. can any one help me?
Regards
Jerminxxx
You can call the function with [ServerName] .[DatabaseName].[User/Schema Name].[FunctionName].
|||Just use the select ServerName.DatabaseName.OwnerName.Functionname
i.e Like select IFoundd10.Employee.dbo.getenc()
__________________________________________________________
Don't forget to click "Mark as Answer" on the post that helped you.
|||Thanks for ur reply.
when i try to use this technique i got some error like ."contains more than the maximum number of prefixes."
Can u help me
||| Which version of SQL Server are you using ? Can you post some of the details of the 2 servers that you've linked, the function and from where are you trying to execute the function ?
I'm using sql server 2005.
For Example:
We have two sql server named as server1 and server2. These two servers are already linked.
On server1 we have some user defined function.Amoung the function one function called getencf().
What i want .I wants to run function getencf() on server 2 environment.
When i try to execute like servername.Databasename.dbo.functionname.I got some error.
CAn you help me
Regards
Jerminxxx
|||
jerminxxx:
When i try to execute like servername.Databasename.dbo.functionname.I got some error.
Are you getting the same error ("contains more than the maximum number of prefixes.") which you posted in one of your posts ?
Are you able to run the function from server1 ?
it will e hard due to prefixes number, if your function returns single value try to replace it( or add extra ) stored procedure on the server which will call you function. If your function returns results set try to use OPENQuery to execute it on remote server.
Friday, March 23, 2012
How to run SSIS package through the command line?
Dear member,
Does any body know how to run SSIS package through the command line?
Thanks
Shamen
You can use DTEXEC from the command line to run packages.|||Thanks Jwelch...I will try....
How to run SQL Server Exp 2005
I have installed SQL Server Express Edition (55 mb).
Program >> Start >> MS SQL Server 2005 >> Configuration Tool ... this is what being displayed. This is my first use of MS Sql Server, before this I was using Oracle and after installation one could see Sql Plus from where the user can log in.
I am confused how to start SQL Server Expr 2005 edition and try some sql.
Please help me out..
Have a nice day
Ramniklal
Yo have to download a graphical management tool as a separate download: http://go.microsoft.com/fwlink/?LinkId=64064
--
SvenC
Will get you back after downloading and using the graphical mgt. tool
bye n have a nice day
ramniklal|||
If you want to run commandline queries (I know that Oracle guys like that :-) ) you can use the OSQL.EXE (but this will be deprecated in the future) or the SQLCMD.EXE which is the successor of the OSQL.exe.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Monday, March 19, 2012
How to return the result (XML) with FOR XML EXPLICIT
I got the following SP, that runs a SQL statement with FOR XML
EXPLICIT:
**** SQL STATEMENT *****
CREATE PROCEDURE SP_F06_GetActions
(
@.MSISDN as varchar(20),
@.SessionId as varchar(64)
)
AS
BEGIN
SELECT
1 as tag
,NULL as parent
,'F06' as [RESPONSE!1!Flow_Name!Element]
,[MSISDN] as [RESPONSE!1!MSISDN!Element]
,getdate() as [RESPONSE!1!Timestamp!Element]
,@.SessionId as [RESPONSE!1!SessionId!Element]
,0 as [RESPONSE!1!Status!Element]
,NULL as [Actions!2!NumOfActions!Element]
,NULL as [Action!3!Id]
,NULL as [Action!3!ActionId!Element]
,NULL as [Action!3!ActionName!Element]
,NULL as [Action!3!ActionType!Element]
,NULL as [Action!3!From!Element]
,NULL as [Action!3!To!Element]
,NULL as [Action!3!ActionStart!Element]
,NULL as [Action!3!ActionStop!Element]
,NULL as [Action!3!ActionDurationThreshold!Elemen
t]
,NULL as [Action!3!ActionVideoURL!Element]
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
UNION
SELECT
2
,1
,NULL
,NULL
,NULL
,NULL
,NULL
, dbo.getNumOfActionsByMSISDN(MSISDN)
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
UNION
SELECT
3
,2
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,[ActionId]
,[ActionId]
,[ActionName]
,[ActionType]
,[From]
,[To]
,[ActionStart]
,[ActionStop]
,[ActionDurationThreshold]
,[ActionVideoURL]
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
WHERE [MSISDN]=@.MSISDN
--ORDER BY 3, 1
FOR XML EXPLICIT
RETURN
END
GO
**** END OF SQL ***********
Well, it returns the following XML:
***** XML *********************
<RESPONSE>
<Flow_Name>F06</Flow_Name>
<MSISDN>9053*********</MSISDN>
<Timestamp>2007-11-15T14:37:24.467</Timestamp>
<SessionId>45717B60-0A0F-434B-9FB9-CC8F647BFEA7</SessionId>
<Status>0</Status>
<Actions>
<NumOfActions>3</NumOfActions>
<Action Id="10001">
<ActionId>10001</ActionId>
<ActionName>Acme</ActionName>
<ActionType>1</ActionType>
<From>00:00:00</From>
<To>12:00:00</To>
<ActionStart>2007-11-15T09:52:56.513</ActionStart>
<ActionStop>2007-11-25T09:52:56.513</ActionStop>
<ActionDurationThreshold>8</ActionDurationThreshold>
<ActionVideoURL>http://www.dynu.com/media/video/video1.g3p</
ActionVideoURL>
</Action>
<Action Id="10002">
<ActionId>10002</ActionId>
<ActionName>Pepsi</ActionName>
<ActionType>1</ActionType>
<From>12:00</From>
<To>16:00:00</To>
<ActionStart>2007-11-15T09:53:17.640</ActionStart>
<ActionStop>2007-11-25T09:53:17.640</ActionStop>
<ActionDurationThreshold>7</ActionDurationThreshold>
<ActionVideoURL>http://www.dynu.com/media/video/video2.g3p</
ActionVideoURL>
</Action>
<Action Id="10003">
<ActionId>10003</ActionId>
<ActionName>Momo Deterjan</ActionName>
<ActionType>1</ActionType>
<From>18:00:00</From>
<To>22:00:00</To>
<ActionStart>2007-11-15T09:56:32.950</ActionStart>
<ActionStop>2007-11-25T09:56:32.950</ActionStop>
<ActionDurationThreshold>6</ActionDurationThreshold>
<ActionVideoURL>http://www.dynu.com/media/video/video2.g3p</
ActionVideoURL>
</Action>
</Actions>
</RESPONSE>
**** END OF XML ************
Therefore, what I want is to assign this XML to a variable. To do
that, I tried the following (but failed):
Exec @.RC=SP_F06_GetActions
@.MSISDN,
@.SessionId
So, my QUESTION is HOW CAN I RETRIEVE THIS XML INTO A VARIABLE?
Thanks in advance for your help and interest.
Regards,
Ali<ali.koyuncu@.gmail.com> wrote in message
news:ea5805dd-f725-49c8-a641-68f2ea767a14@.f13g2000hsa.googlegroups.com...
> Dear SQLXML specialists and other animals,
> I got the following SP, that runs a SQL statement with FOR XML
> EXPLICIT:
> **** SQL STATEMENT *****
> CREATE PROCEDURE SP_F06_GetActions
> (
> @.MSISDN as varchar(20),
> @.SessionId as varchar(64)
> )
> AS
> BEGIN
> SELECT
> 1 as tag
> ,NULL as parent
> ,'F06' as [RESPONSE!1!Flow_Name!Element]
> ,[MSISDN] as [RESPONSE!1!MSISDN!Element]
> ,getdate() as [RESPONSE!1!Timestamp!Element]
> ,@.SessionId as [RESPONSE!1!SessionId!Element]
> ,0 as [RESPONSE!1!Status!Element]
> ,NULL as [Actions!2!NumOfActions!Element]
> ,NULL as [Action!3!Id]
> ,NULL as [Action!3!ActionId!Element]
> ,NULL as [Action!3!ActionName!Element]
> ,NULL as [Action!3!ActionType!Element]
> ,NULL as [Action!3!From!Element]
> ,NULL as [Action!3!To!Element]
> ,NULL as [Action!3!ActionStart!Element]
> ,NULL as [Action!3!ActionStop!Element]
> ,NULL as [Action!3!ActionDurationThreshold!Elemen
t]
> ,NULL as [Action!3!ActionVideoURL!Element]
> FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
> UNION
> SELECT
> 2
> ,1
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> , dbo.getNumOfActionsByMSISDN(MSISDN)
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
> UNION
> SELECT
> 3
> ,2
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,[ActionId]
> ,[ActionId]
> ,[ActionName]
> ,[ActionType]
> ,[From]
> ,[To]
> ,[ActionStart]
> ,[ActionStop]
> ,[ActionDurationThreshold]
> ,[ActionVideoURL]
> FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
> WHERE [MSISDN]=@.MSISDN
> --ORDER BY 3, 1
> FOR XML EXPLICIT
> RETURN
> END
> GO
> **** END OF SQL ***********
> Well, it returns the following XML:
> ***** XML *********************
> <RESPONSE>
> <Flow_Name>F06</Flow_Name>
> <MSISDN>9053*********</MSISDN>
> <Timestamp>2007-11-15T14:37:24.467</Timestamp>
> <SessionId>45717B60-0A0F-434B-9FB9-CC8F647BFEA7</SessionId>
> <Status>0</Status>
> <Actions>
> <NumOfActions>3</NumOfActions>
> <Action Id="10001">
> <ActionId>10001</ActionId>
> <ActionName>Acme</ActionName>
> <ActionType>1</ActionType>
> <From>00:00:00</From>
> <To>12:00:00</To>
> <ActionStart>2007-11-15T09:52:56.513</ActionStart>
> <ActionStop>2007-11-25T09:52:56.513</ActionStop>
> <ActionDurationThreshold>8</ActionDurationThreshold>
> <ActionVideoURL>http://www.dynu.com/media/video/video1.g3p</
> ActionVideoURL>
> </Action>
> <Action Id="10002">
> <ActionId>10002</ActionId>
> <ActionName>Pepsi</ActionName>
> <ActionType>1</ActionType>
> <From>12:00</From>
> <To>16:00:00</To>
> <ActionStart>2007-11-15T09:53:17.640</ActionStart>
> <ActionStop>2007-11-25T09:53:17.640</ActionStop>
> <ActionDurationThreshold>7</ActionDurationThreshold>
> <ActionVideoURL>http://www.dynu.com/media/video/video2.g3p</
> ActionVideoURL>
> </Action>
> <Action Id="10003">
> <ActionId>10003</ActionId>
> <ActionName>Momo Deterjan</ActionName>
> <ActionType>1</ActionType>
> <From>18:00:00</From>
> <To>22:00:00</To>
> <ActionStart>2007-11-15T09:56:32.950</ActionStart>
> <ActionStop>2007-11-25T09:56:32.950</ActionStop>
> <ActionDurationThreshold>6</ActionDurationThreshold>
> <ActionVideoURL>http://www.dynu.com/media/video/video2.g3p</
> ActionVideoURL>
> </Action>
> </Actions>
> </RESPONSE>
> **** END OF XML ************
> Therefore, what I want is to assign this XML to a variable. To do
> that, I tried the following (but failed):
> Exec @.RC=SP_F06_GetActions
> @.MSISDN,
> @.SessionId
> So, my QUESTION is HOW CAN I RETRIEVE THIS XML INTO A VARIABLE?
> Thanks in advance for your help and interest.
> Regards,
> Ali
Which version of SQL Server? In 2000 you can't do what you want, in 2005 you
need to use the TYPE directive.
http://technet.microsoft.com/en-us/...y/ms345110.aspx
Joe Fawcett (MVP - XML)
http://joe.fawcett.name|||I use SQL Server 2005.
How to use TYPE? Can you give me simple example?
Thanks,
Ali
On 15 Kas=FDm, 15:56, "Joe Fawcett" <joefawc...@.newsgroup.nospam> wrote:
> <ali.koyu...@.gmail.com> wrote in message
> news:ea5805dd-f725-49c8-a641-68f2ea767a14@.f13g2000hsa.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> Which version of SQL Server? In 2000 you can't do what you want, in 2005 y=[/color
]
ou
> need to use the TYPE directive.http://technet.microsoft.com/en-us/library/=[/color
]
ms345110.aspx
> --
> Joe Fawcett (MVP - XML)
> http://joe.fawcett.name|||I use SQL Server 2005.
How to use TYPE? Can you give me simple example?
Thanks,
Ali
On 15 Kas=FDm, 15:56, "Joe Fawcett" <joefawc...@.newsgroup.nospam> wrote:
> <ali.koyu...@.gmail.com> wrote in message
> news:ea5805dd-f725-49c8-a641-68f2ea767a14@.f13g2000hsa.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> Which version of SQL Server? In 2000 you can't do what you want, in 2005 y=[/color
]
ou
> need to use the TYPE directive.http://technet.microsoft.com/en-us/library/=[/color
]
ms345110.aspx
> --
> Joe Fawcett (MVP - XML)
> http://joe.fawcett.name|||I use SQL Server 2005.
How to use TYPE? Can you give me simple example?
Thanks,
Ali
On 15 Kas=FDm, 15:56, "Joe Fawcett" <joefawc...@.newsgroup.nospam> wrote:
> <ali.koyu...@.gmail.com> wrote in message
> news:ea5805dd-f725-49c8-a641-68f2ea767a14@.f13g2000hsa.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> Which version of SQL Server? In 2000 you can't do what you want, in 2005 y=[/color
]
ou
> need to use the TYPE directive.http://technet.microsoft.com/en-us/library/=[/color
]
ms345110.aspx
> --
> Joe Fawcett (MVP - XML)
> http://joe.fawcett.name|||When I use TYPE, it gave me the following error :( *damn*
Msg 1086, Level 15, State 1, Line 72
The FOR XML clause is invalid in views, inline functions, derived
tables, and subqueries when they contain a set operator. To work
around, wrap the SELECT containing a set operator using derived table
syntax and apply FOR XML on top of it.
On 15 Kas=C4=B1m, 16:08, ali.koyu...@.gmail.com wrote:
> I use SQL Server 2005.
> How to use TYPE? Can you give me simple example?
> Thanks,
> Ali
> On 15 Kas=C3=BDm, 15:56, "Joe Fawcett" <joefawc...@.newsgroup.nospam> wrote=[/color
]
:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
you
y/ms345110.aspx
>
>
>|||At first, many thans to Joe...
then YAY! I solved. Just changed my SP as follows:
ALTER PROCEDURE SP_F06_GetActions
(
@.MSISDN as varchar(20),
@.SessionId as varchar(64),
@.ResultXML as xml=3DNULL output --NEW!
)
AS
BEGIN
SELECT
1 as tag
,NULL as parent
,'F06' as [RESPONSE!1!Flow_Name!Element]
,[MSISDN] as [RESPONSE!1!MSISDN!Element]
,getdate() as [RESPONSE!1!Timestamp!Element]
,@.SessionId as [RESPONSE!1!SessionId!Element]
,0 as [RESPONSE!1!Status!Element]
,NULL as [Actions!2!NumOfActions!Element]
,NULL as [Action!3!Id]
,NULL as [Action!3!ActionId!Element]
,NULL as [Action!3!ActionName!Element]
,NULL as [Action!3!ActionType!Element]
,NULL as [Action!3!From!Element]
,NULL as [Action!3!To!Element]
,NULL as [Action!3!ActionStart!Element]
,NULL as [Action!3!ActionStop!Element]
,NULL as [Action!3!ActionDurationThreshold!Elemen
t]
,NULL as [Action!3!ActionVideoURL!Element]
INTO #TempTable -- DEFINING A TEMP TABLE!
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
UNION
SELECT
2
,1
,NULL
,NULL
,NULL
,NULL
,NULL
, dbo.getNumOfActionsByMSISDN(MSISDN)
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
UNION
SELECT
3
,2
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,[ActionId]
,[ActionId]
,[ActionName]
,[ActionType]
,[From]
,[To]
,[ActionStart]
,[ActionStop]
,[ActionDurationThreshold]
,[ActionVideoURL]
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
WHERE [MSISDN]=3D@.MSISDN
SET @.ResultXML=3D(SELECT * FROM #TempTable FOR XML EXPLICIT, TYPE)
DROP TABLE #TempTable
RETURN
END
GO
On 15 Kas=C4=B1m, 16:08, ali.koyu...@.gmail.com wrote:
> I use SQL Server 2005.
> How to use TYPE? Can you give me simple example?
> Thanks,
> Ali
> On 15 Kas=C3=BDm, 15:56, "Joe Fawcett" <joefawc...@.newsgroup.nospam> wrote=[/color
]
:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
you
y/ms345110.aspx
>
>
>|||At first, many thans to Joe...
then YAY! I solved. Just changed my SP as follows:
ALTER PROCEDURE SP_F06_GetActions
(
@.MSISDN as varchar(20),
@.SessionId as varchar(64),
@.ResultXML as xml=3DNULL output --NEW!
)
AS
BEGIN
SELECT
1 as tag
,NULL as parent
,'F06' as [RESPONSE!1!Flow_Name!Element]
,[MSISDN] as [RESPONSE!1!MSISDN!Element]
,getdate() as [RESPONSE!1!Timestamp!Element]
,@.SessionId as [RESPONSE!1!SessionId!Element]
,0 as [RESPONSE!1!Status!Element]
,NULL as [Actions!2!NumOfActions!Element]
,NULL as [Action!3!Id]
,NULL as [Action!3!ActionId!Element]
,NULL as [Action!3!ActionName!Element]
,NULL as [Action!3!ActionType!Element]
,NULL as [Action!3!From!Element]
,NULL as [Action!3!To!Element]
,NULL as [Action!3!ActionStart!Element]
,NULL as [Action!3!ActionStop!Element]
,NULL as [Action!3!ActionDurationThreshold!Elemen
t]
,NULL as [Action!3!ActionVideoURL!Element]
INTO #TempTable -- DEFINING A TEMP TABLE!
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
UNION
SELECT
2
,1
,NULL
,NULL
,NULL
,NULL
,NULL
, dbo.getNumOfActionsByMSISDN(MSISDN)
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
UNION
SELECT
3
,2
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,[ActionId]
,[ActionId]
,[ActionName]
,[ActionType]
,[From]
,[To]
,[ActionStart]
,[ActionStop]
,[ActionDurationThreshold]
,[ActionVideoURL]
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
WHERE [MSISDN]=3D@.MSISDN
SET @.ResultXML=3D(SELECT * FROM #TempTable FOR XML EXPLICIT, TYPE)
DROP TABLE #TempTable
RETURN
END
GO
On 15 Kas=C4=B1m, 16:08, ali.koyu...@.gmail.com wrote:
> I use SQL Server 2005.
> How to use TYPE? Can you give me simple example?
> Thanks,
> Ali
> On 15 Kas=C3=BDm, 15:56, "Joe Fawcett" <joefawc...@.newsgroup.nospam> wrote=[/color
]
:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
you
y/ms345110.aspx
>
>
>|||At first, many thans to Joe...
then YAY! I solved. Just changed my SP as follows:
ALTER PROCEDURE SP_F06_GetActions
(
@.MSISDN as varchar(20),
@.SessionId as varchar(64),
@.ResultXML as xml=3DNULL output --NEW!
)
AS
BEGIN
SELECT
1 as tag
,NULL as parent
,'F06' as [RESPONSE!1!Flow_Name!Element]
,[MSISDN] as [RESPONSE!1!MSISDN!Element]
,getdate() as [RESPONSE!1!Timestamp!Element]
,@.SessionId as [RESPONSE!1!SessionId!Element]
,0 as [RESPONSE!1!Status!Element]
,NULL as [Actions!2!NumOfActions!Element]
,NULL as [Action!3!Id]
,NULL as [Action!3!ActionId!Element]
,NULL as [Action!3!ActionName!Element]
,NULL as [Action!3!ActionType!Element]
,NULL as [Action!3!From!Element]
,NULL as [Action!3!To!Element]
,NULL as [Action!3!ActionStart!Element]
,NULL as [Action!3!ActionStop!Element]
,NULL as [Action!3!ActionDurationThreshold!Elemen
t]
,NULL as [Action!3!ActionVideoURL!Element]
INTO #TempTable -- DEFINING A TEMP TABLE!
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
UNION
SELECT
2
,1
,NULL
,NULL
,NULL
,NULL
,NULL
, dbo.getNumOfActionsByMSISDN(MSISDN)
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
UNION
SELECT
3
,2
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,[ActionId]
,[ActionId]
,[ActionName]
,[ActionType]
,[From]
,[To]
,[ActionStart]
,[ActionStop]
,[ActionDurationThreshold]
,[ActionVideoURL]
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
WHERE [MSISDN]=3D@.MSISDN
SET @.ResultXML=3D(SELECT * FROM #TempTable FOR XML EXPLICIT, TYPE)
DROP TABLE #TempTable
RETURN
END
GO
On 15 Kas=C4=B1m, 16:08, ali.koyu...@.gmail.com wrote:
> I use SQL Server 2005.
> How to use TYPE? Can you give me simple example?
> Thanks,
> Ali
> On 15 Kas=C3=BDm, 15:56, "Joe Fawcett" <joefawc...@.newsgroup.nospam> wrote=[/color
]
:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
you
y/ms345110.aspx
>
>
>|||If you're using SQL 2005, how about using FOR XML PATH instead? Much
simpler.
<ali.koyuncu@.gmail.com> wrote in message
news:ea5805dd-f725-49c8-a641-68f2ea767a14@.f13g2000hsa.googlegroups.com...
> Dear SQLXML specialists and other animals,
> I got the following SP, that runs a SQL statement with FOR XML
> EXPLICIT:
> **** SQL STATEMENT *****
> CREATE PROCEDURE SP_F06_GetActions
> (
> @.MSISDN as varchar(20),
> @.SessionId as varchar(64)
> )
> AS
> BEGIN
> SELECT
> 1 as tag
> ,NULL as parent
> ,'F06' as [RESPONSE!1!Flow_Name!Element]
> ,[MSISDN] as [RESPONSE!1!MSISDN!Element]
> ,getdate() as [RESPONSE!1!Timestamp!Element]
> ,@.SessionId as [RESPONSE!1!SessionId!Element]
> ,0 as [RESPONSE!1!Status!Element]
> ,NULL as [Actions!2!NumOfActions!Element]
> ,NULL as [Action!3!Id]
> ,NULL as [Action!3!ActionId!Element]
> ,NULL as [Action!3!ActionName!Element]
> ,NULL as [Action!3!ActionType!Element]
> ,NULL as [Action!3!From!Element]
> ,NULL as [Action!3!To!Element]
> ,NULL as [Action!3!ActionStart!Element]
> ,NULL as [Action!3!ActionStop!Element]
> ,NULL as [Action!3!ActionDurationThreshold!Elemen
t]
> ,NULL as [Action!3!ActionVideoURL!Element]
> FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
> UNION
> SELECT
> 2
> ,1
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> , dbo.getNumOfActionsByMSISDN(MSISDN)
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
> UNION
> SELECT
> 3
> ,2
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,[ActionId]
> ,[ActionId]
> ,[ActionName]
> ,[ActionType]
> ,[From]
> ,[To]
> ,[ActionStart]
> ,[ActionStop]
> ,[ActionDurationThreshold]
> ,[ActionVideoURL]
> FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
> WHERE [MSISDN]=@.MSISDN
> --ORDER BY 3, 1
> FOR XML EXPLICIT
> RETURN
> END
> GO
> **** END OF SQL ***********
> Well, it returns the following XML:
> ***** XML *********************
> <RESPONSE>
> <Flow_Name>F06</Flow_Name>
> <MSISDN>9053*********</MSISDN>
> <Timestamp>2007-11-15T14:37:24.467</Timestamp>
> <SessionId>45717B60-0A0F-434B-9FB9-CC8F647BFEA7</SessionId>
> <Status>0</Status>
> <Actions>
> <NumOfActions>3</NumOfActions>
> <Action Id="10001">
> <ActionId>10001</ActionId>
> <ActionName>Acme</ActionName>
> <ActionType>1</ActionType>
> <From>00:00:00</From>
> <To>12:00:00</To>
> <ActionStart>2007-11-15T09:52:56.513</ActionStart>
> <ActionStop>2007-11-25T09:52:56.513</ActionStop>
> <ActionDurationThreshold>8</ActionDurationThreshold>
> <ActionVideoURL>http://www.dynu.com/media/video/video1.g3p</
> ActionVideoURL>
> </Action>
> <Action Id="10002">
> <ActionId>10002</ActionId>
> <ActionName>Pepsi</ActionName>
> <ActionType>1</ActionType>
> <From>12:00</From>
> <To>16:00:00</To>
> <ActionStart>2007-11-15T09:53:17.640</ActionStart>
> <ActionStop>2007-11-25T09:53:17.640</ActionStop>
> <ActionDurationThreshold>7</ActionDurationThreshold>
> <ActionVideoURL>http://www.dynu.com/media/video/video2.g3p</
> ActionVideoURL>
> </Action>
> <Action Id="10003">
> <ActionId>10003</ActionId>
> <ActionName>Momo Deterjan</ActionName>
> <ActionType>1</ActionType>
> <From>18:00:00</From>
> <To>22:00:00</To>
> <ActionStart>2007-11-15T09:56:32.950</ActionStart>
> <ActionStop>2007-11-25T09:56:32.950</ActionStop>
> <ActionDurationThreshold>6</ActionDurationThreshold>
> <ActionVideoURL>http://www.dynu.com/media/video/video2.g3p</
> ActionVideoURL>
> </Action>
> </Actions>
> </RESPONSE>
> **** END OF XML ************
> Therefore, what I want is to assign this XML to a variable. To do
> that, I tried the following (but failed):
> Exec @.RC=SP_F06_GetActions
> @.MSISDN,
> @.SessionId
> So, my QUESTION is HOW CAN I RETRIEVE THIS XML INTO A VARIABLE?
> Thanks in advance for your help and interest.
> Regards,
> Ali
How to return the result (XML) with FOR XML EXPLICIT
I got the following SP, that runs a SQL statement with FOR XML
EXPLICIT:
**** SQL STATEMENT *****
CREATE PROCEDURE SP_F06_GetActions
(
@.MSISDNas varchar(20),
@.SessionIdas varchar(64)
)
AS
BEGIN
SELECT
1 as tag
,NULL as parent
,'F06' as [RESPONSE!1!Flow_Name!Element]
,[MSISDN] as [RESPONSE!1!MSISDN!Element]
,getdate() as [RESPONSE!1!Timestamp!Element]
,@.SessionId as [RESPONSE!1!SessionId!Element]
,0 as [RESPONSE!1!Status!Element]
,NULL as [Actions!2!NumOfActions!Element]
,NULL as [Action!3!Id]
,NULL as [Action!3!ActionId!Element]
,NULL as [Action!3!ActionName!Element]
,NULL as [Action!3!ActionType!Element]
,NULL as [Action!3!From!Element]
,NULL as [Action!3!To!Element]
,NULL as [Action!3!ActionStart!Element]
,NULL as [Action!3!ActionStop!Element]
,NULL as [Action!3!ActionDurationThreshold!Element]
,NULL as [Action!3!ActionVideoURL!Element]
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
UNION
SELECT
2
,1
,NULL
,NULL
,NULL
,NULL
,NULL
, dbo.getNumOfActionsByMSISDN(MSISDN)
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
UNION
SELECT
3
,2
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,[ActionId]
,[ActionId]
,[ActionName]
,[ActionType]
,[From]
,[To]
,[ActionStart]
,[ActionStop]
,[ActionDurationThreshold]
,[ActionVideoURL]
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
WHERE [MSISDN]=@.MSISDN
--ORDER BY 3, 1
FOR XML EXPLICIT
RETURN
END
GO
**** END OF SQL ***********
Well, it returns the following XML:
***** XML *********************
<RESPONSE>
<Flow_Name>F06</Flow_Name>
<MSISDN>9053*********</MSISDN>
<Timestamp>2007-11-15T14:37:24.467</Timestamp>
<SessionId>45717B60-0A0F-434B-9FB9-CC8F647BFEA7</SessionId>
<Status>0</Status>
<Actions>
<NumOfActions>3</NumOfActions>
<Action Id="10001">
<ActionId>10001</ActionId>
<ActionName>Acme</ActionName>
<ActionType>1</ActionType>
<From>00:00:00</From>
<To>12:00:00</To>
<ActionStart>2007-11-15T09:52:56.513</ActionStart>
<ActionStop>2007-11-25T09:52:56.513</ActionStop>
<ActionDurationThreshold>8</ActionDurationThreshold>
<ActionVideoURL>http://www.dynu.com/media/video/video1.g3p</
ActionVideoURL>
</Action>
<Action Id="10002">
<ActionId>10002</ActionId>
<ActionName>Pepsi</ActionName>
<ActionType>1</ActionType>
<From>12:00</From>
<To>16:00:00</To>
<ActionStart>2007-11-15T09:53:17.640</ActionStart>
<ActionStop>2007-11-25T09:53:17.640</ActionStop>
<ActionDurationThreshold>7</ActionDurationThreshold>
<ActionVideoURL>http://www.dynu.com/media/video/video2.g3p</
ActionVideoURL>
</Action>
<Action Id="10003">
<ActionId>10003</ActionId>
<ActionName>Momo Deterjan</ActionName>
<ActionType>1</ActionType>
<From>18:00:00</From>
<To>22:00:00</To>
<ActionStart>2007-11-15T09:56:32.950</ActionStart>
<ActionStop>2007-11-25T09:56:32.950</ActionStop>
<ActionDurationThreshold>6</ActionDurationThreshold>
<ActionVideoURL>http://www.dynu.com/media/video/video2.g3p</
ActionVideoURL>
</Action>
</Actions>
</RESPONSE>
**** END OF XML ************
Therefore, what I want is to assign this XML to a variable. To do
that, I tried the following (but failed):
Exec @.RC=SP_F06_GetActions
@.MSISDN,
@.SessionId
So, my QUESTION is HOW CAN I RETRIEVE THIS XML INTO A VARIABLE?
Thanks in advance for your help and interest.
Regards,
Ali
<ali.koyuncu@.gmail.com> wrote in message
news:ea5805dd-f725-49c8-a641-68f2ea767a14@.f13g2000hsa.googlegroups.com...
> Dear SQLXML specialists and other animals,
> I got the following SP, that runs a SQL statement with FOR XML
> EXPLICIT:
> **** SQL STATEMENT *****
> CREATE PROCEDURE SP_F06_GetActions
> (
> @.MSISDN as varchar(20),
> @.SessionId as varchar(64)
> )
> AS
> BEGIN
> SELECT
> 1 as tag
> ,NULL as parent
> ,'F06' as [RESPONSE!1!Flow_Name!Element]
> ,[MSISDN] as [RESPONSE!1!MSISDN!Element]
> ,getdate() as [RESPONSE!1!Timestamp!Element]
> ,@.SessionId as [RESPONSE!1!SessionId!Element]
> ,0 as [RESPONSE!1!Status!Element]
> ,NULL as [Actions!2!NumOfActions!Element]
> ,NULL as [Action!3!Id]
> ,NULL as [Action!3!ActionId!Element]
> ,NULL as [Action!3!ActionName!Element]
> ,NULL as [Action!3!ActionType!Element]
> ,NULL as [Action!3!From!Element]
> ,NULL as [Action!3!To!Element]
> ,NULL as [Action!3!ActionStart!Element]
> ,NULL as [Action!3!ActionStop!Element]
> ,NULL as [Action!3!ActionDurationThreshold!Element]
> ,NULL as [Action!3!ActionVideoURL!Element]
> FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
> UNION
> SELECT
> 2
> ,1
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> , dbo.getNumOfActionsByMSISDN(MSISDN)
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
> UNION
> SELECT
> 3
> ,2
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,[ActionId]
> ,[ActionId]
> ,[ActionName]
> ,[ActionType]
> ,[From]
> ,[To]
> ,[ActionStart]
> ,[ActionStop]
> ,[ActionDurationThreshold]
> ,[ActionVideoURL]
> FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
> WHERE [MSISDN]=@.MSISDN
> --ORDER BY 3, 1
> FOR XML EXPLICIT
> RETURN
> END
> GO
> **** END OF SQL ***********
> Well, it returns the following XML:
> ***** XML *********************
> <RESPONSE>
> <Flow_Name>F06</Flow_Name>
> <MSISDN>9053*********</MSISDN>
> <Timestamp>2007-11-15T14:37:24.467</Timestamp>
> <SessionId>45717B60-0A0F-434B-9FB9-CC8F647BFEA7</SessionId>
> <Status>0</Status>
> <Actions>
> <NumOfActions>3</NumOfActions>
> <Action Id="10001">
> <ActionId>10001</ActionId>
> <ActionName>Acme</ActionName>
> <ActionType>1</ActionType>
> <From>00:00:00</From>
> <To>12:00:00</To>
> <ActionStart>2007-11-15T09:52:56.513</ActionStart>
> <ActionStop>2007-11-25T09:52:56.513</ActionStop>
> <ActionDurationThreshold>8</ActionDurationThreshold>
> <ActionVideoURL>http://www.dynu.com/media/video/video1.g3p</
> ActionVideoURL>
> </Action>
> <Action Id="10002">
> <ActionId>10002</ActionId>
> <ActionName>Pepsi</ActionName>
> <ActionType>1</ActionType>
> <From>12:00</From>
> <To>16:00:00</To>
> <ActionStart>2007-11-15T09:53:17.640</ActionStart>
> <ActionStop>2007-11-25T09:53:17.640</ActionStop>
> <ActionDurationThreshold>7</ActionDurationThreshold>
> <ActionVideoURL>http://www.dynu.com/media/video/video2.g3p</
> ActionVideoURL>
> </Action>
> <Action Id="10003">
> <ActionId>10003</ActionId>
> <ActionName>Momo Deterjan</ActionName>
> <ActionType>1</ActionType>
> <From>18:00:00</From>
> <To>22:00:00</To>
> <ActionStart>2007-11-15T09:56:32.950</ActionStart>
> <ActionStop>2007-11-25T09:56:32.950</ActionStop>
> <ActionDurationThreshold>6</ActionDurationThreshold>
> <ActionVideoURL>http://www.dynu.com/media/video/video2.g3p</
> ActionVideoURL>
> </Action>
> </Actions>
> </RESPONSE>
> **** END OF XML ************
> Therefore, what I want is to assign this XML to a variable. To do
> that, I tried the following (but failed):
> Exec @.RC=SP_F06_GetActions
> @.MSISDN,
> @.SessionId
> So, my QUESTION is HOW CAN I RETRIEVE THIS XML INTO A VARIABLE?
> Thanks in advance for your help and interest.
> Regards,
> Ali
Which version of SQL Server? In 2000 you can't do what you want, in 2005 you
need to use the TYPE directive.
http://technet.microsoft.com/en-us/library/ms345110.aspx
Joe Fawcett (MVP - XML)
http://joe.fawcett.name
|||I use SQL Server 2005.
How to use TYPE? Can you give me simple example?
Thanks,
Ali
On 15 Kasm, 15:56, "Joe Fawcett" <joefawc...@.newsgroup.nospam> wrote:
> <ali.koyu...@.gmail.com> wrote in message
> news:ea5805dd-f725-49c8-a641-68f2ea767a14@.f13g2000hsa.googlegroups.com...
>
>
>
>
>
>
>
>
>
> Which version of SQL Server? In 2000 you can't do what you want, in 2005 you
> need to use the TYPE directive.http://technet.microsoft.com/en-us/library/ms345110.aspx
> --
> Joe Fawcett (MVP - XML)
> http://joe.fawcett.name
|||I use SQL Server 2005.
How to use TYPE? Can you give me simple example?
Thanks,
Ali
On 15 Kasm, 15:56, "Joe Fawcett" <joefawc...@.newsgroup.nospam> wrote:
> <ali.koyu...@.gmail.com> wrote in message
> news:ea5805dd-f725-49c8-a641-68f2ea767a14@.f13g2000hsa.googlegroups.com...
>
>
>
>
>
>
>
>
>
> Which version of SQL Server? In 2000 you can't do what you want, in 2005 you
> need to use the TYPE directive.http://technet.microsoft.com/en-us/library/ms345110.aspx
> --
> Joe Fawcett (MVP - XML)
> http://joe.fawcett.name
|||I use SQL Server 2005.
How to use TYPE? Can you give me simple example?
Thanks,
Ali
On 15 Kasm, 15:56, "Joe Fawcett" <joefawc...@.newsgroup.nospam> wrote:
> <ali.koyu...@.gmail.com> wrote in message
> news:ea5805dd-f725-49c8-a641-68f2ea767a14@.f13g2000hsa.googlegroups.com...
>
>
>
>
>
>
>
>
>
> Which version of SQL Server? In 2000 you can't do what you want, in 2005 you
> need to use the TYPE directive.http://technet.microsoft.com/en-us/library/ms345110.aspx
> --
> Joe Fawcett (MVP - XML)
> http://joe.fawcett.name
|||When I use TYPE, it gave me the following error
Msg 1086, Level 15, State 1, Line 72
The FOR XML clause is invalid in views, inline functions, derived
tables, and subqueries when they contain a set operator. To work
around, wrap the SELECT containing a set operator using derived table
syntax and apply FOR XML on top of it.
On 15 Kas?m, 16:08, ali.koyu...@.gmail.com wrote:[vbcol=seagreen]
> I use SQL Server 2005.
> How to use TYPE? Can you give me simple example?
> Thanks,
> Ali
> On 15 Kasym, 15:56, "Joe Fawcett" <joefawc...@.newsgroup.nospam> wrote:
>
>
>
>
>
>
>
>
>
>
>
|||At first, many thans to Joe...
then YAY! I solved. Just changed my SP as follows:
ALTER PROCEDURE SP_F06_GetActions
(
@.MSISDNas varchar(20),
@.SessionIdas varchar(64),
@.ResultXMLas xml=NULL output --NEW!
)
AS
BEGIN
SELECT
1 as tag
,NULL as parent
,'F06' as [RESPONSE!1!Flow_Name!Element]
,[MSISDN] as [RESPONSE!1!MSISDN!Element]
,getdate() as [RESPONSE!1!Timestamp!Element]
,@.SessionId as [RESPONSE!1!SessionId!Element]
,0 as [RESPONSE!1!Status!Element]
,NULL as [Actions!2!NumOfActions!Element]
,NULL as [Action!3!Id]
,NULL as [Action!3!ActionId!Element]
,NULL as [Action!3!ActionName!Element]
,NULL as [Action!3!ActionType!Element]
,NULL as [Action!3!From!Element]
,NULL as [Action!3!To!Element]
,NULL as [Action!3!ActionStart!Element]
,NULL as [Action!3!ActionStop!Element]
,NULL as [Action!3!ActionDurationThreshold!Element]
,NULL as [Action!3!ActionVideoURL!Element]
INTO #TempTable -- DEFINING A TEMP TABLE!
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
UNION
SELECT
2
,1
,NULL
,NULL
,NULL
,NULL
,NULL
, dbo.getNumOfActionsByMSISDN(MSISDN)
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
UNION
SELECT
3
,2
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,[ActionId]
,[ActionId]
,[ActionName]
,[ActionType]
,[From]
,[To]
,[ActionStart]
,[ActionStop]
,[ActionDurationThreshold]
,[ActionVideoURL]
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
WHERE [MSISDN]=@.MSISDN
SET @.ResultXML=(SELECT * FROM #TempTable FOR XML EXPLICIT, TYPE)
DROP TABLE #TempTable
RETURN
END
GO
On 15 Kas?m, 16:08, ali.koyu...@.gmail.com wrote:[vbcol=seagreen]
> I use SQL Server 2005.
> How to use TYPE? Can you give me simple example?
> Thanks,
> Ali
> On 15 Kasym, 15:56, "Joe Fawcett" <joefawc...@.newsgroup.nospam> wrote:
>
>
>
>
>
>
>
>
>
>
>
|||At first, many thans to Joe...
then YAY! I solved. Just changed my SP as follows:
ALTER PROCEDURE SP_F06_GetActions
(
@.MSISDNas varchar(20),
@.SessionIdas varchar(64),
@.ResultXMLas xml=NULL output --NEW!
)
AS
BEGIN
SELECT
1 as tag
,NULL as parent
,'F06' as [RESPONSE!1!Flow_Name!Element]
,[MSISDN] as [RESPONSE!1!MSISDN!Element]
,getdate() as [RESPONSE!1!Timestamp!Element]
,@.SessionId as [RESPONSE!1!SessionId!Element]
,0 as [RESPONSE!1!Status!Element]
,NULL as [Actions!2!NumOfActions!Element]
,NULL as [Action!3!Id]
,NULL as [Action!3!ActionId!Element]
,NULL as [Action!3!ActionName!Element]
,NULL as [Action!3!ActionType!Element]
,NULL as [Action!3!From!Element]
,NULL as [Action!3!To!Element]
,NULL as [Action!3!ActionStart!Element]
,NULL as [Action!3!ActionStop!Element]
,NULL as [Action!3!ActionDurationThreshold!Element]
,NULL as [Action!3!ActionVideoURL!Element]
INTO #TempTable -- DEFINING A TEMP TABLE!
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
UNION
SELECT
2
,1
,NULL
,NULL
,NULL
,NULL
,NULL
, dbo.getNumOfActionsByMSISDN(MSISDN)
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
UNION
SELECT
3
,2
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,[ActionId]
,[ActionId]
,[ActionName]
,[ActionType]
,[From]
,[To]
,[ActionStart]
,[ActionStop]
,[ActionDurationThreshold]
,[ActionVideoURL]
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
WHERE [MSISDN]=@.MSISDN
SET @.ResultXML=(SELECT * FROM #TempTable FOR XML EXPLICIT, TYPE)
DROP TABLE #TempTable
RETURN
END
GO
On 15 Kas?m, 16:08, ali.koyu...@.gmail.com wrote:[vbcol=seagreen]
> I use SQL Server 2005.
> How to use TYPE? Can you give me simple example?
> Thanks,
> Ali
> On 15 Kasym, 15:56, "Joe Fawcett" <joefawc...@.newsgroup.nospam> wrote:
>
>
>
>
>
>
>
>
>
>
>
|||At first, many thans to Joe...
then YAY! I solved. Just changed my SP as follows:
ALTER PROCEDURE SP_F06_GetActions
(
@.MSISDNas varchar(20),
@.SessionIdas varchar(64),
@.ResultXMLas xml=NULL output --NEW!
)
AS
BEGIN
SELECT
1 as tag
,NULL as parent
,'F06' as [RESPONSE!1!Flow_Name!Element]
,[MSISDN] as [RESPONSE!1!MSISDN!Element]
,getdate() as [RESPONSE!1!Timestamp!Element]
,@.SessionId as [RESPONSE!1!SessionId!Element]
,0 as [RESPONSE!1!Status!Element]
,NULL as [Actions!2!NumOfActions!Element]
,NULL as [Action!3!Id]
,NULL as [Action!3!ActionId!Element]
,NULL as [Action!3!ActionName!Element]
,NULL as [Action!3!ActionType!Element]
,NULL as [Action!3!From!Element]
,NULL as [Action!3!To!Element]
,NULL as [Action!3!ActionStart!Element]
,NULL as [Action!3!ActionStop!Element]
,NULL as [Action!3!ActionDurationThreshold!Element]
,NULL as [Action!3!ActionVideoURL!Element]
INTO #TempTable -- DEFINING A TEMP TABLE!
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
UNION
SELECT
2
,1
,NULL
,NULL
,NULL
,NULL
,NULL
, dbo.getNumOfActionsByMSISDN(MSISDN)
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
UNION
SELECT
3
,2
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,[ActionId]
,[ActionId]
,[ActionName]
,[ActionType]
,[From]
,[To]
,[ActionStart]
,[ActionStop]
,[ActionDurationThreshold]
,[ActionVideoURL]
FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
WHERE [MSISDN]=@.MSISDN
SET @.ResultXML=(SELECT * FROM #TempTable FOR XML EXPLICIT, TYPE)
DROP TABLE #TempTable
RETURN
END
GO
On 15 Kas?m, 16:08, ali.koyu...@.gmail.com wrote:[vbcol=seagreen]
> I use SQL Server 2005.
> How to use TYPE? Can you give me simple example?
> Thanks,
> Ali
> On 15 Kasym, 15:56, "Joe Fawcett" <joefawc...@.newsgroup.nospam> wrote:
>
>
>
>
>
>
>
>
>
>
>
|||If you're using SQL 2005, how about using FOR XML PATH instead? Much
simpler.
<ali.koyuncu@.gmail.com> wrote in message
news:ea5805dd-f725-49c8-a641-68f2ea767a14@.f13g2000hsa.googlegroups.com...
> Dear SQLXML specialists and other animals,
> I got the following SP, that runs a SQL statement with FOR XML
> EXPLICIT:
> **** SQL STATEMENT *****
> CREATE PROCEDURE SP_F06_GetActions
> (
> @.MSISDN as varchar(20),
> @.SessionId as varchar(64)
> )
> AS
> BEGIN
> SELECT
> 1 as tag
> ,NULL as parent
> ,'F06' as [RESPONSE!1!Flow_Name!Element]
> ,[MSISDN] as [RESPONSE!1!MSISDN!Element]
> ,getdate() as [RESPONSE!1!Timestamp!Element]
> ,@.SessionId as [RESPONSE!1!SessionId!Element]
> ,0 as [RESPONSE!1!Status!Element]
> ,NULL as [Actions!2!NumOfActions!Element]
> ,NULL as [Action!3!Id]
> ,NULL as [Action!3!ActionId!Element]
> ,NULL as [Action!3!ActionName!Element]
> ,NULL as [Action!3!ActionType!Element]
> ,NULL as [Action!3!From!Element]
> ,NULL as [Action!3!To!Element]
> ,NULL as [Action!3!ActionStart!Element]
> ,NULL as [Action!3!ActionStop!Element]
> ,NULL as [Action!3!ActionDurationThreshold!Element]
> ,NULL as [Action!3!ActionVideoURL!Element]
> FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
> UNION
> SELECT
> 2
> ,1
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> , dbo.getNumOfActionsByMSISDN(MSISDN)
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
> UNION
> SELECT
> 3
> ,2
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,NULL
> ,[ActionId]
> ,[ActionId]
> ,[ActionName]
> ,[ActionType]
> ,[From]
> ,[To]
> ,[ActionStart]
> ,[ActionStop]
> ,[ActionDurationThreshold]
> ,[ActionVideoURL]
> FROM [CampaignDB].[dbo].[VI_MSISDN_RELATED_ACTIONS]
> WHERE [MSISDN]=@.MSISDN
> --ORDER BY 3, 1
> FOR XML EXPLICIT
> RETURN
> END
> GO
> **** END OF SQL ***********
> Well, it returns the following XML:
> ***** XML *********************
> <RESPONSE>
> <Flow_Name>F06</Flow_Name>
> <MSISDN>9053*********</MSISDN>
> <Timestamp>2007-11-15T14:37:24.467</Timestamp>
> <SessionId>45717B60-0A0F-434B-9FB9-CC8F647BFEA7</SessionId>
> <Status>0</Status>
> <Actions>
> <NumOfActions>3</NumOfActions>
> <Action Id="10001">
> <ActionId>10001</ActionId>
> <ActionName>Acme</ActionName>
> <ActionType>1</ActionType>
> <From>00:00:00</From>
> <To>12:00:00</To>
> <ActionStart>2007-11-15T09:52:56.513</ActionStart>
> <ActionStop>2007-11-25T09:52:56.513</ActionStop>
> <ActionDurationThreshold>8</ActionDurationThreshold>
> <ActionVideoURL>http://www.dynu.com/media/video/video1.g3p</
> ActionVideoURL>
> </Action>
> <Action Id="10002">
> <ActionId>10002</ActionId>
> <ActionName>Pepsi</ActionName>
> <ActionType>1</ActionType>
> <From>12:00</From>
> <To>16:00:00</To>
> <ActionStart>2007-11-15T09:53:17.640</ActionStart>
> <ActionStop>2007-11-25T09:53:17.640</ActionStop>
> <ActionDurationThreshold>7</ActionDurationThreshold>
> <ActionVideoURL>http://www.dynu.com/media/video/video2.g3p</
> ActionVideoURL>
> </Action>
> <Action Id="10003">
> <ActionId>10003</ActionId>
> <ActionName>Momo Deterjan</ActionName>
> <ActionType>1</ActionType>
> <From>18:00:00</From>
> <To>22:00:00</To>
> <ActionStart>2007-11-15T09:56:32.950</ActionStart>
> <ActionStop>2007-11-25T09:56:32.950</ActionStop>
> <ActionDurationThreshold>6</ActionDurationThreshold>
> <ActionVideoURL>http://www.dynu.com/media/video/video2.g3p</
> ActionVideoURL>
> </Action>
> </Actions>
> </RESPONSE>
> **** END OF XML ************
> Therefore, what I want is to assign this XML to a variable. To do
> that, I tried the following (but failed):
> Exec @.RC=SP_F06_GetActions
> @.MSISDN,
> @.SessionId
> So, my QUESTION is HOW CAN I RETRIEVE THIS XML INTO A VARIABLE?
> Thanks in advance for your help and interest.
> Regards,
> Ali
Monday, March 12, 2012
How to return sets by filtering those tuples with null values in a MDX statement?
Hi, dear friends,
How can we return the result by filtering tuples with null values? (e.g. I want to return the result for tuples specified by dimention DimA, measures B,C. therefore I want to filter the result with nulls values of B,C for DimA).
Hope it is clear for your help.
And I am looking forward to hearing from you shortly for your help.
Thanks a lot.
With kind regards,
Yours sincerely,
It depends what you mean by "filter" - do you want the null values included or excluded?
And what does B,C mean? Do you mean if either is null or both?
The following is one solution if you want to return members from DimA where both measures are empty (null).
Filter(DimA.members, IsEmtpy(Measures.B) AND IsEmpty(Measures.C)
but I would expect somthing like this could be faster as it could use block computation instead of cell-by-cell evaluation.
INTERSECT(NonEmpty(DimA.Members, Measures.B),NonEmpty(DimA.members, Measures.C))
|||Hi, Darren,
Thanks a lot for your help.
With kind regards,
Yours sincerely,
How to return sets by filtering those tuples with null values in a MDX statement?
Hi, dear friends,
How can we return the result by filtering tuples with null values? (e.g. I want to return the result for tuples specified by dimention DimA, measures B,C. therefore I want to filter the result with nulls values of B,C for DimA).
Hope it is clear for your help.
And I am looking forward to hearing from you shortly for your help.
Thanks a lot.
With kind regards,
Yours sincerely,
It depends what you mean by "filter" - do you want the null values included or excluded?
And what does B,C mean? Do you mean if either is null or both?
The following is one solution if you want to return members from DimA where both measures are empty (null).
Filter(DimA.members, IsEmtpy(Measures.B) AND IsEmpty(Measures.C)
but I would expect somthing like this could be faster as it could use block computation instead of cell-by-cell evaluation.
INTERSECT(NonEmpty(DimA.Members, Measures.B),NonEmpty(DimA.members, Measures.C))
|||Hi, Darren,
Thanks a lot for your help.
With kind regards,
Yours sincerely,
Sunday, February 19, 2012
How To Restrict End User to update field in database manually....
I want to protect a field in a table...i want to restrict users to update the value in that field...by manually logging into that database...it can be updated only through the application...if any body manually update the field value...it has to be captured in log with old value...is it possible to do this sql server...if any of u says yes 'its possible' then :beer: other wise :eek:Why do you allow users to manually log into the database in the first place? If you follow good database application design principles and limit all access to the database to stored procedures, you won't have this problem.|||triggers and history tables
http://weblogs.sqlteam.com/brettk/archive/2004/10/20/2242.aspx|||Dear Blindman,
thks for ur opinion ,i respect ur opinion.our aplication is a huge distributed application its running across 4000 location...we are having all the security design in database level..still worrying about some smart users...and our clients very concern on some values should not be tampered on database...since they had those worst experience previously...and more over 100 people are giving support for this app..who is having rights to access the database.......
Cheers
Sathesh.M|||Can you give the support users SELECT permissions, but not UPDATE permissions? That woud allow them to "see" the data, but not to change it.
-PatP|||triggers and history tables
http://weblogs.sqlteam.com/brettk/archive/2004/10/20/2242.aspx
Damn it. I have to start reading your blog. I just wrote something very similar but mine does not have any caveats and I had to deal with synchronizing some existing history tables with the live tables.|||Can you give the support users SELECT permissions, but not UPDATE permissions? That woud allow them to "see" the data, but not to change it.
-PatP
...and even then, you should not allow them to view the tables directly. You should create SQL Views for the data they are allowed to see and then grant SELECT access to those views.|||...and even then, you should not allow them to view the tables directly. You should create SQL Views for the data they are allowed to see and then grant SELECT access to those views.Yeah, but I was trying to KISS.
-PatP