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

Webboy2008Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

IgorNedCommented:
Use Response.Write to show your Recordset output on the webpage.
Webboy2008Author Commented:
IgorNed: It won't work by simple adding response.write..it shows all the tags and not shown in proper order.
wdosanjosCommented:
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.
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

Webboy2008Author Commented:
My sql server is 2005
Webboy2008Author Commented:
The goal is to format like that
<tr>
 <td>Id</td>
 <td>logdate</td>
</tr>
wdosanjosCommented:
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

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

Webboy2008Author Commented:
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

wdosanjosCommented:
What's the error msg?
Webboy2008Author Commented:
Incorrect syntax near by 'Apply'
Webboy2008Author Commented:
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
wdosanjosCommented:
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

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

SELECT @@version
Webboy2008Author Commented:
look like it is running on sql 2000. If it is sql 2000, any work around?
wdosanjosCommented:
Unfortunately, there is no work around from a SQL Server 2000 point of view.

You will need to parse the XML on the ASP side then, something like this:  (If Msxml2.DOMDocument.6.0 does not work, change 6.0 to 5.0, 4.0, or 3.0)

Set xmlDoc = CreateObject("Msxml2.DOMDocument.6.0")

while not rs.eof
   xmlDoc.loadXML rs("message")
   extractmessagexml = "<tr><td>" & EventId & "</td></tr>"

   For Each child In xmlDoc.getElementsByTagName("Event")
      extractmessagexml = extractmessagexml & _
         "<tr><td>" & child.selectSingleNode("logTime").Text & "</td>" & _
         "<td>" & child.selectSingleNode("Source").Text & "</td>" & _
         "<td>" & child.selectSingleNode("UserName").Text & "</td>" & _
         "<td>" & child.selectSingleNode("Action").Text & "</td>" & _
         "<td>" & child.selectSingleNode("Message").Text & "</td>" & _
         "<td>" & child.selectSingleNode("EnvelopeStatus").Text & "</td>" & _
         "<td>" & child.selectSingleNode("ClientIPAddress").Text & "</td>" & _
         "<td>" & child.selectSingleNode("Information").Text & "</td>" & _
         "<td>" & child.selectSingleNode("GeoLocation").Text & "</td></tr>" & _
   Next
wend
next

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Webboy2008Author Commented:
very helpful experts
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.