Link to home
Start Free TrialLog in
Avatar of Webboy2008
Webboy2008

asked on

classic asp, xml, and stored procedure

I have table structure. one of the column is called message, and have xml event log.
I am able to use query (attached) to get the meesage column.

My question is: I need to show the message (now in xml format) and shown on the webpage coding in classic asp(not .net/c#) within the Recordset Loop.

like the following:
while not rs.eof
message = rs("message")
extractmessagexml = "<tr><td>" & EventId & "</td></tr>"
extractmessagexml =......
wend
next




<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

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

SELECT DocuSignMessage FROM Table1 WHERE EnvelopeId = @EnvelopeId ORDER BY CREATEDDATE


End

Open in new window

Avatar of IgorNed
IgorNed

Use Response.Write to show your Recordset output on the webpage.
Avatar of Webboy2008

ASKER

IgorNed: It won't work by simple adding response.write..it shows all the tags and not shown in proper order.
I'm not sure what's the expected output.  Do you want to display the XML content on the page, or just the elements?  Also, what's you SQL Server database version?

If you want to display the XML you need to escape the < > characters as &lt; and &gt; and use the <pre> tag for proper alignment.  Something like this:

"<tr><td><pre>" & Replace(Replace(message,">","&gt;"),"<","&lt;") & "</pre></td></tr>"

Open in new window


If you want the values in the XML, you can change your query to a XML SQL Query, but that only works on SQL Server 2005 / 2008.
My sql server is 2005
The goal is to format like that
<tr>
 <td>Id</td>
 <td>logdate</td>
</tr>
You can try something like this to return the XML values as columns.  Then you just need to reference them as regular columns on the ASP code.

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(DocuSignMessage As XML) msg FROM Table1 WHERE EnvelopeId = @EnvelopeId) X
Cross Apply msg.nodes('/DocuSignEnvelopeEvents/Event') As MSG(evt)
ORDER BY X.CREATEDDATE


End

Open in new window


Here is a sample:
declare @temp table (EnvelopeId int, CreatedDate datetime, DocuSignMessage varchar(4000))

insert into @temp values(1, GETDATE(),
'<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>')

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(DocuSignMessage As XML) msg From @temp) X
Cross Apply msg.nodes('/DocuSignEnvelopeEvents/Event') As MSG(evt)
Order By X.CreatedDate

Open in new window


Result set:
logTime                 Source               UserName             Action               Message              EnvelopeStatus       ClientIPAddress      Information          GeoLocation
----------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
2011-03-10 19:39:42.413 API                  John Smiths          Registered           how are you          Created              127.1.1.1                                 
2011-03-10 19:39:42.773 API                  Sam Chan             Sent Invitations     hello                Sent                 127.1.1.2            Nothing              

Open in new window

Will the attached codes work if I have more than 2 <Events>?
It may have 2 or 3 or 5...we don't yet..

I copied the stored procedure you post. and it does not work.
It complaint about the X before the Cross.
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(DocuSignMessage As XML) msg FROM Table1 WHERE EnvelopeId = @EnvelopeId) X
Cross Apply msg.nodes('/DocuSignEnvelopeEvents/Event') As MSG(evt)
ORDER BY X.CREATEDDATE


End

Open in new window

What's the error msg?
Incorrect syntax near by 'Apply'
This is table structure

EnvelopeId
Message
Status
QuoteId
CreatedDate
CreatedBy

This is my change

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 Message.nodes('/DocuSignEnvelopeEvents/Event') As MSG(evt)
ORDER BY X.CREATEDDATE
Please try the following:

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

Still have error: Incorrrect syntax near 'Apply'
Let me know the result of the following SQL on your system:

SELECT @@version
look like it is running on sql 2000. If it is sql 2000, any work around?
ASKER CERTIFIED SOLUTION
Avatar of wdosanjos
wdosanjos
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
very helpful experts