Utilizing SQL Server 2000 for ColdFusion: User-Defined Functions and XML Support

1. Leveraging SQL Server 2000in ColdFusion ApplicationsDecember 9, 2003Chris LomvardiasSRA InternationalChristopher_Lomvardias@sra.com

2. Presentation Agenda • User-Defined Functions • XML Support • Code Examples • Resources

3. Functions • Built-In Functions in SQL Server 2000 • Built-in subroutines that encapsulate frequently performed logic • Cannot be modified • Rowset Functions • Return object that can be used as table reference • OpenXML (to be discussed later) • Aggregate Functions • Take a collection of values and return a single value • Max(), Min(), Avg(), Sum(), Count() • Scalar Functions • Take a single value and return a single value • Configuration functions (@@version) • Cursor functions • Date and Time functions (getdate) • Mathematical functions (round) • Metadata functions • Security functions • String functions (substring, upper) • System functions (@@identity, case, isnull,nullif) • System statistical functions • Text and image functions

4. User-Defined Functions • New in SQL Server 2000 • User-defined functions introduced in SQL Server 2000, enabling programmers to create custom functions (user-defined functions introduced in ColdFusion 5!) • Advantages • Code simplification • Readability • Code reuse • Maintainability • Three Types of User-DefinedFunctions • scalar • inline table-valued • multistatement table-valued • Syntax • CREATE FUNCTION • ALTER FUNCTION • DROP FUNCTION

5. UDF Types • Scalar • Operate on a single value and then return a single value • Inline Table-Valued • Return clause specifies TABLE (e.g., parameterized view) • Multistatement Table-Valued • Return clause specifies a TABLE type with columns and their data types • CREATE FUNCTION fn_AuthorsInState (@state varchar(5) • RETURNS TABLE • AS • RETURN (SELECT au_lname from authors where state=@state) • CREATE FUNCTION fn_AllAuthors () • RETURNS @AuthorsTable TABLE • ( au_id int, au_lname varchar(40), au_fname varchar(20)) • AS • BEGIN • INSERT @AuthorsTable • SELECT au_id, au_lname, au_fname FROM authors • RETURN • END

6. UDF Limitations • Using built-in functions in UDFs • Cannot use nondeterministic functions inside a UDF (e.g., getdate() cannot be used, though there are some workarounds) • Performance considerations • In some situations UDFs can be slower than using TSQL; if you can do it using TSQL, you are probably better off not writing a UDF • System (Global) UDFs • Not recommended but appear to work; make sure you research implications before implementing

7. FOR XML • FOR XML mode [, XMLDATA] [, ELEMENTS][, BINARY BASE64] • XML mode • Specifies the XML mode. XML mode determines the shape of the resulting XML. Mode can be RAW, AUTO, or EXPLICIT. • XMLDATA • Specifies that an XML-Data schema should be returned. The schema is prepended to the document as an inline schema. • ELEMENTS • If the ELEMENTS option is specified, the columns are returned as sub-elements. Otherwise, they are mapped to XML attributes. This option is supported in AUTO mode only. • BINARY BASE64 • If the BINARY Base64 option is specified, any binary data returned by the query is represented in base64-encoded format.

8. FOR XML Mode • Raw • Returns an element for each row in the query • Auto • Returns rows as nested elements based on table(s) • Explicit • Provides granular control of shape of generated XML • ColdFusion Column = XML_F52E2B61-18A1-11d1-B105-00805F49916B • <cfquery name="xmlQuery" datasource="pubs"> • SELECT au_id, au_lname, au_fname FROM authors • FOR XML AUTO • </cfquery> • <cfset xmlString=""> • <cfloop index="i" from="1" to="#xmlQuery.RecordCount#"> • <cfset xmlString=xmlString & xmlQuery[xmlQuery.ColumnList][i]> • </cfloop> • <cfoutput>#htmleditformat(xmlString)#</cfoutput>

9. FOR XML Limitations • Limitations • Can’t use in a View • Can’t use with Cursors • Doesn’t support Compute By • Can’t use in a subselect, nested Select or Select Into • Can’t use in a User Defined Function • Other Considerations • Doesn’t generate well-formed XML (just fragments) • NULLS do not appear • Watch out for low-order, non-printable ASCII characters (CDATA is an option)

10. FOR XML EXPLICIT • FOR XML EXPLICIT – Universal Table ElementName!TagNumber!AttributeName!Directive • Directives • xml • hide • cdata • xmltext • element SELECT 1 as Tag, null as Parent, au_fname as [Author!1!FirstName!Hide], au_lname as [Author!1!LastName!Element], au_id as [Author!1!ID] FROM authors FOR XML EXPLICIT

11. OPENXML • OpenXML provides a relational view of XML data • SELECT • INSERT • UPDATE • DELETE • Advantages • Reduce database calls • Act on multiple tables in one step • Act on multiple rows in one step • Join with existing tables • Store open content • Disadvantages • Performance issues with large documents

12. OPENXML • Usage in stored procedures • Declare handle • Declare incoming packet • Obtain document handle • Execute statement against packet (Xpath) • Release from memory OPENXML(iDoc, rowpattern, flags) [WITH (rowsetschema [colpatterns] | tablename)] 0 Default value. Attribute centric mapping 1 Use Attribute centric mapping 2 Use element centric mapping 8 Only unconsumed data should be copied to the overflow property @mp;xmltext

13. Additional Options • IIS Virtual Directory (template, URL queries) • Updategrams • XML Bulk Load • SQLXML Managed Classes - .Net • Web Services Support

14. Resources • http://www.sqlxml.org • http://www.perfectxml.com/SQLXML.asp • http://msdn.microsoft.com/sqlxml • http://www.topxml.com/sql/default.asp