Http Endpoint and FOR XML - Return XmlElement in Native XML WebServices in SQL Server 2005
by David Hayden ( Sarasota .NET Developer )
In a previous post:
I played around with the new XML Data Type, XML Indexes, and XML Schema Collection in SQL Server 2005. As a side benefit, I used the new Http Endpoint feature in SQL Server 2005, which is essentially native XML Web Services without the need for IIS.
A question I received on Http Endpoint is if the DataSet is the only way to return data from an Http Endpoint. The answer to this is no, but a couple of things are coming into play here.
Specifying FORMAT of WEBMETHOD - ALL_RESULTS or ROWSETS_ONLY
If you look at the create statement for the Http Endpoint below, you will notice I specified the FORMAT as ROWSETS_ONLY, which essentially means I am returning a DataSet. If I changed the FORMAT to ALL_RESULTS, the return would instead be an object array. When you consume the Http Endpoint in a winform application, you will see a difference in the return value of the WEBMETHOD. Specifying ROWSETS_ONLY causes the return value to be a DataSet. Specifying ALL_RESULTS causes the return value of the WEBMETHOD to be object[].
CREATE ENDPOINT Library_Books
STATE = Started
AS HTTP
(
PATH = '/Library/Books',
AUTHENTICATION = (INTEGRATED),
PORTS = (CLEAR),
SITE = '*'
)
FOR SOAP
(
WEBMETHOD 'FindBooksByKeyword'
(NAME = 'Library.dbo.FindBooksByKeyword',
FORMAT = ROWSETS_ONLY),
WSDL = DEFAULT,
DATABASE = 'Library',
NAMESPACE = DEFAULT
)
FORMAT = ALL_RESULTS for Http Endpoint
If you specify FORMAT = ALL_RESULTS, you are not limited to just a DataSet. You can return an object array back to the client. Here are some common return values:
- SELECT statement - DataSet
- SELECT statement using FOR XML - XmlElement
- Error - SqlMessage
- Message - SqlMessage
- Output parameter- SqlParameter
- Rows affected - SqlRowCount
- RETURN Value - System.Int32
FOR XML AUTO
If we changed the stored procedure in the previous example to include FOR XML AUTO:
SELECT Title, BookIndex.value('(/index/keyword[.=
sql:variable("@Keyword")]/@page)[1]','varchar(25)')
as Page
FROM Books
WHERE BookIndex.exist('/index/keyword[.=
sql:variable("@Keyword")]') = 1
FOR XML AUTO
and modified the CREATE ENDPOINT command so that FORMAT = ALL_RESULTS ( forget about XMLSCHEMA for this example ):
CREATE ENDPOINT Library_Books
STATE = Started
AS HTTP
(
PATH = '/Library/Books',
AUTHENTICATION = (INTEGRATED),
PORTS = (CLEAR),
SITE = '*'
)
FOR SOAP
(
WEBMETHOD 'FindBooksByKeyword'
(NAME = 'Library.dbo.FindBooksByKeyword',
FORMAT = ALL_RESULTS),
WSDL = DEFAULT,
DATABASE = 'Library',
NAMESPACE = DEFAULT
)
we now essentially return an XmlElement ( System.Xml.XmlElement ) in position 0 of the object array:
XmlElement xml = libraryService.
FindBooksByKeyword(keyword)[0] as XmlElement;
System.Xml.XmlElement.InnerXml
The results are no longer a DataSet now, but the following XML Fragments located in the XmlElement's InnerXml property:
<Books Title="Pro ADO.NET 2.0" Page="16" />
<Books Title="Expert ASP.NET 2.0" Page="321" />
This is, of course, using the keyword of “ADO.NET“ as shown in the original example:
Conclusion
So, you are not limited to returning just a DataSet when using Http Endpoints in SQL Server 2005. I just specified FORMAT = ROWSETS_ONLY since I was only return a set of rows from a SELECT statement and could then avoid indexing an object array for which was already a DataSet. However, if you prefer to use the FOR XML in your select statments to return XML Fragments or want to return an array of objects, you can do so by specifying FORMAT = ALL_RESULTS.
Source: David Hayden ( Florida .NET Developer )
SQL Server 2005 Tutorials