XML, CFMX CFML XML, CFMX CFML & &

SQL XMLSQL XMLKevin Penny, MMCPKevin Penny, MMCP

kevin@hotgigs.comkevin@hotgigs.com

What is XML?What is XML?

► Animal, vegetable, or mineral?Animal, vegetable, or mineral?► Best reference: Best reference: www.w3schools.comwww.w3schools.com► XML stands for EXML stands for EXXtensible tensible MMarkup arkup LLanguageanguage

► XML was designed to describe dataXML was designed to describe data► XML can be used to Share Data/Store Data, XML can be used to Share Data/Store Data,

Create New LanguagesCreate New Languages► XML is designed to be self-descriptiveXML is designed to be self-descriptive

<Animal><Animal>► <Dog name=“Bailey”><Dog name=“Bailey”>

<Type>Golden Retriever</Type><Type>Golden Retriever</Type>► </Dog></Dog>

</Animal></Animal>

XML UncoveredXML Uncovered

<?xml version="1.0" encoding="ISO-8859-1"?><?xml version="1.0" encoding="ISO-8859-1"?>

<Animal><Animal>►<Dog name=“Bailey”><Dog name=“Bailey”>

<Type>Golden <Type>Golden Retriever</Type>Retriever</Type>

►</Dog></Dog></Animal></Animal> ELEMENT

ATTRIBITE

XML Declaration

ROOT ELEMENT

Rules of the RoadRules of the Road

►XML is cAsE SensitiveXML is cAsE Sensitive►Elements must have a closing tagElements must have a closing tag►Elements must be properly nestedElements must be properly nested►Attribute values must be quotedAttribute values must be quoted►All XML documents must have a root All XML documents must have a root

elementelement

CFML and XMLCFML and XML► New great XML functions New great XML functions

XMLParse(xmlDocument)XMLParse(xmlDocument)► Creates a tree structure of the XML document as an array of structuresCreates a tree structure of the XML document as an array of structures► Data can come from a database query, a file on the server, an http request etc.Data can come from a database query, a file on the server, an http request etc.

XMLSearch(xmlDocument, xPathString)XMLSearch(xmlDocument, xPathString)► Used to navigate/search an xml document using elements (walking the nodes)Used to navigate/search an xml document using elements (walking the nodes)

XMLFormat(string)XMLFormat(string)► Used to escape special XML characters in a string so that it is safe to use with Used to escape special XML characters in a string so that it is safe to use with

XML XML ► < > ‘ “ &< > ‘ “ &

XMLTransform(xmlDocument,xslString)XMLTransform(xmlDocument,xslString)► Applies an XSLT to an XML documentApplies an XSLT to an XML document

XMLNew() or <CFXMLXMLNew() or <CFXML► Creates an XML DocumentCreates an XML Document

► Great ReferenceGreat Reference http://www.how2cf.com/files/papers/cfxml.pdfhttp://www.how2cf.com/files/papers/cfxml.pdf

CFML XML IACFML XML IA

► CFML XML in ActionCFML XML in Action

Let’s Dive inLet’s Dive in Example Example

►CreateCreate►ParseParse►SearchSearch►ExtractExtract

►Cfug/Index.cfmCfug/Index.cfm

SQL XMLSQL XML

►Microsoft SQL Server 2000’s support for XML Microsoft SQL Server 2000’s support for XML Creating queries that create an XML outputCreating queries that create an XML output

►FOR XML CauseFOR XML Cause

Inputting XML to a procedure that can Inputting XML to a procedure that can manipulate the XML Document and put the data manipulate the XML Document and put the data into a table etc.into a table etc.► i.e. Complex form data can be passed to a stored i.e. Complex form data can be passed to a stored

procedure and manipulated procedure and manipulated ►sp_xml_preparedocumentsp_xml_preparedocument►OPENXML OPENXML

SQL XML meets CFML XMLSQL XML meets CFML XML

►ExampleExample SQL Query as XMLSQL Query as XML SQL OPENXML Example in a Stored SQL OPENXML Example in a Stored

ProcedureProcedure CFML Call to the Procedure inserting the CFML Call to the Procedure inserting the

XML DocumentXML Document

Questions?Questions?

►DTD’s?DTD’s?►XPathXPath►XSLTXSLT►SchemasSchemas►SQL OPENXMLSQL OPENXML

ReferencesReferences

►CFML XMLCFML XML http://www.how2cf.com/files/papers/cfxmlhttp://www.how2cf.com/files/papers/cfxml

.pdf.pdf

►XMLXML www.w3schools.comwww.w3schools.com