• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 402
  • Last Modified:

sql, asp.net

I run the sql script and it raises error message in sql server saying
Incorrect synatx nearby 'apply'.

I also include the table structure as well as one of the value in MESSAGE table column.

Thanks,

500 points ready if any experts can resolve that...thank,
Table Columns:
EnvelopeId
Message
Status
QuoteId
CreatedDate
CreatedBy

Create procedure dbo.up_GetWebQuoteTransStatusInDetailsForDocSignAttached
(
@QuoteId INT
)
as
set nocount on
Begin

Select
      MSG.evt.value('logTime[1]', 'datetime') [logTime],
      MSG.evt.value('Source[1]', 'varchar(20)') [Source],
      MSG.evt.value('UserName[1]', 'varchar(100)') [UserName],
      MSG.evt.value('Action[1]', 'varchar(100)') [Action],
      MSG.evt.value('Message[1]', 'varchar(4000)') [Message],
      MSG.evt.value('EnvelopeStatus[1]', 'varchar(100)') [EnvelopeStatus],
      MSG.evt.value('ClientIPAddress[1]', 'varchar(100)') [ClientIPAddress],
      MSG.evt.value('Information[1]', 'varchar(100)') [Information],
      MSG.evt.value('GeoLocation[1]', 'varchar(100)') [GeoLocation]
From (Select CREATEDDATE, Cast([Message] As XML) msg FROM DocuSignEnvelopeAuditEvents
WHERE QuoteId = @QuoteId) X
Cross Apply msg.nodes('/DocuSignEnvelopeEvents/Event') As MSG(evt)
ORDER BY X.CREATEDDATE


End

Open in new window

<DocuSignEnvelopeEvents EnvelopeId="5d7f2f13-508a-4eeb-a55a-c2feeadb6505" xmlns="">
  <Event>
    <logTime>2011-03-10T11:39:42.4128057-08:00</logTime>
    <Source>API</Source>
    <UserName>John Smiths</UserName>
    <Action>Registered</Action>
    <Message>how are you</Message>
    <EnvelopeStatus>Created</EnvelopeStatus>
    <ClientIPAddress>127.1.1.1</ClientIPAddress>
    <Information />
    <GeoLocation />
  </Event>
  <Event>
    <logTime>2011-03-10T11:39:42.772183-08:00</logTime>
    <Source>API</Source>
    <UserName>Sam Chan</UserName>
    <Action>Sent Invitations</Action>
    <Message>hello</Message>
    <EnvelopeStatus>Sent</EnvelopeStatus>
    <ClientIPAddress>127.1.1.2</ClientIPAddress>
    <Information>Nothing</Information>
    <GeoLocation />
  </Event>
</DocuSignEnvelopeEvents>

Open in new window

0
Webboy2008
Asked:
Webboy2008
  • 2
  • 2
1 Solution
 
sventhanCommented:
try this...
Table Columns:
EnvelopeId
Message
Status
QuoteId
CreatedDate
CreatedBy

Create procedure dbo.up_GetWebQuoteTransStatusInDetailsForDocSignAttached
(
@QuoteId INT
)
as
set nocount on
Begin

Select
      MSG.evt.value('logTime[1]', 'datetime') [logTime],
      MSG.evt.value('Source[1]', 'varchar(20)') [Source],
      MSG.evt.value('UserName[1]', 'varchar(100)') [UserName],
      MSG.evt.value('Action[1]', 'varchar(100)') [Action],
      MSG.evt.value('Message[1]', 'varchar(4000)') [Message],
      MSG.evt.value('EnvelopeStatus[1]', 'varchar(100)') [EnvelopeStatus],
      MSG.evt.value('ClientIPAddress[1]', 'varchar(100)') [ClientIPAddress],
      MSG.evt.value('Information[1]', 'varchar(100)') [Information],
      MSG.evt.value('GeoLocation[1]', 'varchar(100)') [GeoLocation]
From (Select CREATEDDATE, Cast([Message] As XML) msg FROM DocuSignEnvelopeAuditEvents
WHERE QuoteId = @QuoteId) X
Cross Apply msg.nodes('/DocuSignEnvelopeEvents/Event') 
ORDER BY X.CREATEDDATE


End

Open in new window

0
 
SharathData EngineerCommented:
Whats the SQL Server version?
0
 
Webboy2008Author Commented:
2005
0
 
SharathData EngineerCommented:
Webboy2008 - I did not get any error. I ran the same query you posted.
0
 
Webboy2008Author Commented:
Really? Then maybe it is sql 2000.
What is workaround if it is sql 2000?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now