xWeb: ExecuteMethod Case Study

This topic outlines a case study in the use of the ExecuteMethod web method. In this case study, we develop two ExecuteMethod queries:

  • Event Calendar—A list of events that occur between two given dates, with the option to filter based on the event type.
  • Event Detail—More information about a particular event, including sessions at the event.

Steps

Add Web Service Definition

First, add a Web Service in the Toolkit module.

Add Web Service
Add XML Web Service

Enter the web service name and web service namespace. For our purposes, it does not matter what you enter in the web service namespace; you will only need to use the web service name to call the web method.

Add Web Service
Add XML Web Service

Add Web Method

Web Method

From the child form, add two Web Methods:

  • EventCalendar
  • EventDetail

Leave the allow anonymous access check box cleared. Enter any notes for reference.

After adding these two web methods, you will see them both in the child form.

Web Method Child Form
Web Method child form

SQL for EventCalendar

Create a database Stored Procedure to return the information you want for the EventCalendar web method. See the following example:

CREATE procedure dbo.av_EventCalendar_ws
 
AS
 
begin
 
SET nocount ON
 
SELECT 
	evt_key,
	evt_title,
	evt_start_date,
	evt_end_date
 
FROM ev_event (nolock)
WHERE 
	dbo.av_date_range_between (
		evt_post_to_web_date, 
		evt_remove_from_web_date, 
		getdate() ) = 1
AND evt_delete_flag = 0
ORDER BY evt_start_date 
 
end
 
GRANT execute ON dbo.av_EventCalendar_ws TO netForumUser,netForumAdmin,AvectraAdmin
go

Observe that when you call this SP in SQL Management Studio, you see the following type of record set returned:

SQL Results

Next, modify the stored procedure to include this line at the end after the ORDER BY clause: 

ORDER BY evt_start_date 
FOR XML PATH ('Event'), ROOT('Events')

This is FOR XML PATH syntax that was introduced in SQL 2005.

Execute the SP again and you will see this:

SQL Xpath 2
SQL Results in XML

If you click the link, it will open up the XML like this below: 

<Events>
<Event>
<evt_key>C9F0B1E1-096A-4C25-817A-925A6F8B4B30</evt_key>
<evt_title>2006 ASTC Annual Technology Conference</evt_title>
<evt_start_date>2006-10-15T00:00:00</evt_start_date>
<evt_end_date>2006-10-20T00:00:00</evt_end_date>
</Event>
<Event>
<evt_key>3EAD2437-EF72-4A3D-AF15-1D51849E5630</evt_key>
<evt_title>2007 ASTC Annual Technology Conference</evt_title>
<evt_start_date>2007-10-15T00:00:00</evt_start_date>
<evt_end_date>2007-10-20T00:00:00</evt_end_date>
</Event>
<Event>
<evt_key>C677D959-3DA6-471F-BAB8-376CA55E4484</evt_key>
<evt_title>Partnership Conference</evt_title>
<evt_start_date>2008-02-05T00:00:00</evt_start_date>
<evt_end_date>2008-02-06T00:00:00</evt_end_date>
</Event>
<Event>
<evt_key>3790A7C0-823C-4F09-89CE-109DF2C58282</evt_key>
<evt_title>2008 ASTC Annual Technology Conference</evt_title>
<evt_start_date>2008-10-15T00:00:00</evt_start_date>
<evt_end_date>2008-10-20T00:00:00</evt_end_date>
</Event>
</Events>

Take a look again at the SQL and the Node above: 

FOR XML PATH ('Event'), ROOT('Events')

The ROOT command created a root node called <Events>, and the PATH command created multiple <Event> nodes, one for each record returned.

Add Web Method Node

XML Node
XML Node

Jump back to netFORUM iWeb and navigate to the EventCalendar web method definition by clicking the goto arrow. Then expand the nodes child form and add one new node. Enter xml in the node name, any number in order, and the name of the SP in the node sql like this: 

execute dbo.av_EventCalendar_ws

and save the record.

Important! You must name the node namexml if you use FOR XML PATH syntax. Otherwise, the web method will fail. The exact word xml instructs the underlying code to use FOR XML PATH processing instead of conventional SQL recordsets.

Add Web Method Security

Security
Security

Expand the authorized web servers child form and add a record. In the server ip enter the name of the xWeb User(s) who are permitted to call this query. In this example, the user is AVxWebUser.

Run xWeb for EventCalendar

Your method should be ready to test now.

First, call the Authenticate web method to get a token. You will need to substitute the name of your xWeb User's own <userName> and <password>. 

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://www.avectra.com/2005/">
<soapenv:Header/>
<soapenv:Body>
<ns:Authenticate>
<ns:userName>AVxWebUser</ns:userName>
<ns:password>zqom8shV9ds</ns:password>
</ns:Authenticate>
</soapenv:Body>
</soapenv:Envelope>

You should get a result like this containing a <Token> element with a value. 

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<soap:Header>
<AuthorizationToken soap:actor="2c502763-89f4-4576-ba03-0cf02217ce51" xmlns="http://www.avectra.com/2005/">
<Token>ae545ec9-bc8c-4560-a2b6-f35a85c6033e</Token>
</AuthorizationToken>
</soap:Header>
<soap:Body>
<AuthenticateResponse xmlns="http://www.avectra.com/2005/">
<AuthenticateResult>http://www.avectra.com/2005/</AuthenticateResult>
</AuthenticateResponse>
</soap:Body>
</soap:Envelope>

Next, call the ExecuteMethod web method with this Request. Notice that we pass the web service name to the <serviceName> parameter and the web method name to the <methodName> parameter. 

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://www.avectra.com/2005/">
<soapenv:Header>
<ns:AuthorizationToken>
<ns:Token>dfe32036-d56d-438a-bfd0-b415e8d686df</ns:Token>
</ns:AuthorizationToken>
</soapenv:Header>
<soapenv:Body>
<ns:ExecuteMethod>
<ns:serviceName>AVEventInformation</ns:serviceName>
<ns:methodName>EventCalendar</ns:methodName>
<ns:parameters>
</ns:parameters>
</ns:ExecuteMethod>
</soapenv:Body>
</soapenv:Envelope>

You should get this result. Note that the XML data returned in the <Events> node is the same as what you saw returned above when this was executed in SQL Server Management Studio. 

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<soap:Header>
<AuthorizationToken xmlns="http://www.avectra.com/2005/">
<Token>49b7a8ca-93e3-4644-b45b-69b6c2a59882</Token>
</AuthorizationToken>
</soap:Header>
<soap:Body>
<ExecuteMethodResponse xmlns="http://www.avectra.com/2005/">
<ExecuteMethodResult>
<Events xmlns="">
<Event>
<evt_key>C9F0B1E1-096A-4C25-817A-925A6F8B4B30</evt_key>
<evt_title>2006 ASTC Annual Technology Conference</evt_title>
<evt_start_date>2006-10-15T00:00:00</evt_start_date>
<evt_end_date>2006-10-20T00:00:00</evt_end_date>
</Event>
<Event>
<evt_key>3EAD2437-EF72-4A3D-AF15-1D51849E5630</evt_key>
<evt_title>2007 ASTC Annual Technology Conference</evt_title>
<evt_start_date>2007-10-15T00:00:00</evt_start_date>
<evt_end_date>2007-10-20T00:00:00</evt_end_date>
</Event>
<Event>
<evt_key>C677D959-3DA6-471F-BAB8-376CA55E4484</evt_key>
<evt_title>Partnership Conference</evt_title>
<evt_start_date>2008-02-05T00:00:00</evt_start_date>
<evt_end_date>2008-02-06T00:00:00</evt_end_date>
</Event>
<Event>
<evt_key>3790A7C0-823C-4F09-89CE-109DF2C58282</evt_key>
<evt_title>2008 ASTC Annual Technology Conference</evt_title>
<evt_start_date>2008-10-15T00:00:00</evt_start_date>
<evt_end_date>2008-10-20T00:00:00</evt_end_date>
</Event>
</Events>
</ExecuteMethodResult>
</ExecuteMethodResponse>
</soap:Body>
</soap:Envelope>

SQL for EventDetail

Now let's write the SQL for EventDetail. We will develop a SP that will return information about the main event and any sessions, given a evt_key primary key for a specific event. 

CREATE procedure dbo.av_EventDetail_ws
@evt_key av_key
AS
 
begin
 
SET nocount ON 
SELECT 
	evt_key,
	evt_title,
	evt_short_description,
	evt_start_date,
	evt_end_date,
-- Nested subnode for Sessions:
(
SELECT ses_key, ses_title
FROM dbo.ev_session (nolock)
WHERE 
		ses_evt_key = evt_key
AND dbo.av_date_range_between(
			ses_post_to_web_date,
			ses_remove_from_web_date,
			getdate()) = 1
AND ses_delete_flag = 0
FOR XML PATH ('Session'), ROOT('Sessions'), type
)
 
FROM ev_event (nolock)
WHERE 
	evt_key = @evt_key
FOR XML PATH ('Event'), ROOT('Events')
 
end
go
 
GRANT execute ON dbo.av_EventDetail_ws TO netForumUser,netForumAdmin,AvectraAdmin
go

Note the nested subnode for Sessions that filters (similar to an embedded sub-select) based on the parent event in this line: 

WHERE 
		ses_evt_key = evt_key

When executed, you will see a result like this: 

<Events>
<Event>
<evt_key>C9F0B1E1-096A-4C25-817A-925A6F8B4B30</evt_key>
<evt_title>2006 ASTC Annual Technology Conference</evt_title>
<evt_start_date>2006-10-15T00:00:00</evt_start_date>
<evt_end_date>2006-10-20T00:00:00</evt_end_date>
<Sessions>
<Session>
<ses_key>F4E3604A-705E-409C-A509-3E6B21A2AB70</ses_key>
<ses_title>Awards Dinner</ses_title>
</Session>
<Session>
<ses_key>33D2E995-423B-4E0C-96B6-A1EFD293EE90</ses_key>
<ses_title>Technology and the Association</ses_title>
</Session>
</Sessions>
</Event>
</Events>

Configure Method

Just as you did for the EventCalendar method above, in netFORUM, navigate to the EventDetail method setup.

Add a node and enter the following:

  • node name - enter xml
  • order - enter any number
  • node sql - enter this: 
execute dbo.av_EventDetail_ws @evt_key = {evt_key}

Similar to what you did before, you are entering the name of a stored procedure, but you are also passing a parameter.

The parsed value of {evt_key} will enable a calling program to pass that parameter when calling ExecuteMethod.

Configure Security

As you did with EventCalendarabove, add a authorized web server for the xWeb User who will be authorized to call this query.

Run xWeb for EventDetail

Run the xWeb application in your chosen program.

Get a new token.

Similar to above, run the ExecuteMethod for EventDetail with this request. The assumption is that you have retrieved a particular evt_key from the EventCalendar method and now you want to get more detail about a particular event: 

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://www.avectra.com/2005/">
<soapenv:Header>
<ns:AuthorizationToken>
<ns:Token>d00d6725-9309-4763-8a17-258a4fe70023</ns:Token>
</ns:AuthorizationToken>
</soapenv:Header>
<soapenv:Body>
<ns:ExecuteMethod>
<ns:serviceName>AVEventInformation</ns:serviceName>
<ns:methodName>EventDetail</ns:methodName>
<ns:parameters>
<ns:Parameter>
<ns:Name>evt_key</ns:Name>
<ns:Value>C9F0B1E1-096A-4C25-817A-925A6F8B4B30</ns:Value>
</ns:Parameter>
</ns:parameters>
</ns:ExecuteMethod>
</soapenv:Body>
</soapenv:Envelope>

Note that this time you must populate the <parameters> node with a <Parameter>. The <Name> element much match up with any ask-at-runtime parameters. The <Value> element contains the value you want to pass.

The response will be similar to the following: 

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<soap:Header>
<AuthorizationToken xmlns="http://www.avectra.com/2005/">
<Token>446f60d8-dde8-4cb7-94db-968fc4ef7dd0</Token>
</AuthorizationToken>
</soap:Header>
<soap:Body>
<ExecuteMethodResponse xmlns="http://www.avectra.com/2005/">
<ExecuteMethodResult>
<Events xmlns="">
<Event>
<evt_key>C9F0B1E1-096A-4C25-817A-925A6F8B4B30</evt_key>
<evt_title>2006 ASTC Annual Technology Conference</evt_title>
<evt_start_date>2006-10-15T00:00:00</evt_start_date>
<evt_end_date>2006-10-20T00:00:00</evt_end_date>
<Sessions>
<Session>
<ses_key>F4E3604A-705E-409C-A509-3E6B21A2AB70</ses_key>
<ses_title>Awards Dinner</ses_title>
</Session>
<Session>
<ses_key>33D2E995-423B-4E0C-96B6-A1EFD293EE90</ses_key>
<ses_title>Technology and the Association</ses_title>
</Session>
</Sessions>
</Event>
</Events>
</ExecuteMethodResult>
</ExecuteMethodResponse>
</soap:Body>
</soap:Envelope>

More Options

This case study can be expanded in the following ways:

  • You can add more than one parameter
  • You can have an infinite level of nested subnodes
  • The top node does not have to be a single record. You could expand the original EventCalendar to return multiple events, with nested subnodes for <Session> within each <Event> node. In this scenario, you would be "merging" EventCalendar and EventDetail.
  • You don't have to use SPs, although we recommend it for speed, security and organization. You can enter any inline SQL in the node sql.
  • Obviously you can adapt these principles to any kinds of data, not just Events and Sessions
  • Once you have the web methods running, you can develop applications to call these web methods programatically.
  • The convention of adding multiple nodes and nested nodes described in DynamicXmlBuilder goes away. With FOR XML PATH, you write all your SQL in just the very top node and you will never add sub-nodes in metadata. Your SQL may of course have nested nodes, but this is not expressed in metadata.
Nested Nodes