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
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>
Create procedure dbo.up_GetWebQuoteTransStatusInDetailsForDocSignAttached
(
@QuoteId INT
)
as
set nocount on
Begin
SELECT DocuSignMessage FROM Table1 WHERE EnvelopeId = @EnvelopeId ORDER BY CREATEDDATE
End
Use Response.Write to show your Recordset output on the webpage.
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 < and > and use the <pre> tag for proper alignment. Something like this:
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.
If you want to display the XML you need to escape the < > characters as < and > and use the <pre> tag for proper alignment. Something like this:
"<tr><td><pre>" & Replace(Replace(message,">",">"),"<","<") & "</pre></td></tr>"
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.
ASKER
My sql server is 2005
ASKER
The goal is to format like that
<tr>
<td>Id</td>
<td>logdate</td>
</tr>
<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.
Here is a sample:
Result set:
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
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
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
ASKER
Will the attached codes work if I have more than 2 <Events>?
It may have 2 or 3 or 5...we don't yet..
It may have 2 or 3 or 5...we don't yet..
ASKER
I copied the stored procedure you post. and it does not work.
It complaint about the X before the Cross.
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
What's the error msg?
ASKER
Incorrect syntax near by 'Apply'
ASKER
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('EnvelopeSta tus[1]', 'varchar(100)') [EnvelopeStatus],
MSG.evt.value('ClientIPAdd ress[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 DocuSignEnvelopeAuditEvent s
WHERE QuoteId = @QuoteId) X
Cross Apply Message.nodes('/DocuSignEn velopeEven ts/Event') As MSG(evt)
ORDER BY X.CREATEDDATE
EnvelopeId
Message
Status
QuoteId
CreatedDate
CreatedBy
This is my change
Select
MSG.evt.value('logTime[1]'
MSG.evt.value('Source[1]',
MSG.evt.value('UserName[1]
MSG.evt.value('Action[1]',
MSG.evt.value('Message[1]'
MSG.evt.value('EnvelopeSta
MSG.evt.value('ClientIPAdd
MSG.evt.value('Information
MSG.evt.value('GeoLocation
From (Select CREATEDDATE, Cast(Message As XML) msg FROM DocuSignEnvelopeAuditEvent
WHERE QuoteId = @QuoteId) X
Cross Apply Message.nodes('/DocuSignEn
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
ASKER
Still have error: Incorrrect syntax near 'Apply'
Let me know the result of the following SQL on your system:
SELECT @@version
SELECT @@version
ASKER
look like it is running on sql 2000. If it is sql 2000, any work around?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
very helpful experts