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

<ali.koyu***@gmail.com> wrote in message

Show quote

Hide quote

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

I use SQL Server 2005.

How to use TYPE? Can you give me simple example?

Thanks,

Ali

Show quote

Hide quote

I use SQL Server 2005.

How to use TYPE? Can you give me simple example?

Thanks,

Ali

Show quote

Hide quote

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ım, 16:08, ali.koyu***@gmail.com wrote:

Show quote

Hide quote

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=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:

Show quote

Hide quote

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=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:

Show quote

Hide quote

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=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:

Show quote

Hide quote

I use SQL Server 2005.

How to use TYPE? Can you give me simple example?

Thanks,

Ali

Show quote

Hide quote

If you're using SQL 2005, how about using FOR XML PATH instead?  Much
simpler.

<ali.koyu***@gmail.com> wrote in message

Show quote

Hide quote

XML Hierarchy Query
Creating XML
Un-encoding XML special characters, please help
dynamic xpath in XQUERY exist() function
Extracting xml values using xquery