ASE111 - Integration, XML, and Web Services Why are They Important to a DBA?

1. ASE111 - Integration, XML, and Web ServicesWhy are They Important to a DBA? Anupam Singh 925.236.6886 singha@sybase.com

2. Sybase Data Management Directions • TCO • Scalable, high-performance OLTP systems • High Availability and Disaster Recovery • Layered Security • Enterprise Integration & Content Management • Native XML handling • Web Services • XML based integration • Support unstructured data and content management

3. Requirement Value Proposition Solution ASE – XML Processing Engine As XML is increasingly used as the standard of communication between business systems, databases will need to deal with XML documents that are exchanged between systems for storage, retrieval and analysis ASE provides built-in/native XML capabilities to effectively store, process and retrieve XML documents • Customers can utilize the existing investments in database platforms to deal with XML content without having to use specialized or custom developed XML processing engines • ASE's XML storage capabilities eliminates the need for external/standalone XML repositories thus providing a proven and tested storage platform for all data -- XML and relational • Reduced Application development effort by leveraging ASE's XML processing capability - parsing & querying made simple.

4. Requirement Value Proposition Solution ASE – XML Integration Platform As business systems move towards process automation and BPM,  transformations between existing data/applications and XML is increasingly important to enable inter-system communication. Data owners need to be able to deal with SQL and XML data in a transparent way given the increasing ubiquity of XML. ASE provides an data integration platform with full XML processing capabilities and a industry specific transformation engine. Full support for XQUERY allows SQL and XML to be used interchangeably with a high degree of integration • A fully integrated transformational engine targeted at specific vertical industries (Finance, Healthcare, etc.) will eliminate the need for external XML processors or custom development to transform data • Reduced Application development effort by leveraging ASE's transformation and Web Services support • Customers will be able to treat XML and SQL data in a uniform manner and leverage ASE's capabilities to integrate legacy applications into BPM environments with no impact to existing client applications.

5. We will try to cover … XML • XSLT, XPATH, XQUERY support • Ability to mix and match XML and relational data • XML CRUD statements for manipulating XML • Ability to return XML to clients in result sets • Putting database queries on a URL with XML result sets over http • XML Indexing • XML schemas – Support for multiple and changing schemas

6. XML Support Requirement Feedback from customers on 12.5.0.3 • STORAGE • Ability to store XML data natively (whole document) • Ability to store portions of XML data (certain fields) • INDEXING • Indexing mechanism should be as fast as typical databases indexes for relational data • Indexing mechanism should deal with dynamic and varying format and structure of XML data • QUERYING • Efficient and Flexible – As simple & powerful as SQL • Must interoperate nicely with SQL • Mapping & TRANSFORMATION • SQL  XML Generation & XML  SQL Generation • XML View of the Relational Content • Transform XML view (of SQL or XML data) to produce custom and industry specific XML output

7. XML Support in ASE 12.5.1 • Native XML Support • Fast XML Indexing • SQL and XML Duality • XPATH, XQUERY and FOR XML Support • XML View, Mapping & Transformation • Support for XML based Integration • Web Service support (SOAP, WSDL)

8. Topics… RELEVANCE SQL  XML • First steps from SQL to XML • Declarative SQL to XML XML RESULTSETS • Incoming XML data mapped to SQL XML  SQL • Incoming XML data queried as XML XQUERY/XPATH • Stored XML data indexing INDEXING, SCHEMAS • SQL/XML Duality CHUNKS, MIX ‘n MATCH SOAP  SQL • Heterogeneous XML data sources inside SQL QUERIES OVER HTTP • SQL as a web service

9. ASE file system XML Processing: The starting point Can the Database Help? Application 2 STEP PROCESS sql results Client sql to xml App logic xml/HTML sql IN MEMORY XPATH/XSL xml/HTML xml objects xml FILE SYS DATABASE

10. Reduce SQL  XML costs • Minimal change to user’s SQL query • Should be declarative • Should give users options • Should perform the SAME as the SQL query • Minimal client side processing • Should work with any SQL syntax

11. Push XML generation into the Database? The for xml clause maps a SQL result to an SQLX document • select 11 as a, 12 as b • union • select 21, 22 • forxml The resulting SQLX-XML document for the above is • <resultset> • <row> <a>11</a> <b>12</b> </row> • <row> <a>21</a> <b>22</b> </row> • </resultset>

12. The for xml clause maps an SQL result to an SQLX document Attach ‘for xml’ clause to any SQL query and you get XML! select t.title_id, title, ord_num, sd.stor_id, stor_name from (salesdetail sd left join titles t on sd.title_id = t.title_id) left join stores on sd.stor_id = stores.stor_id for xml From SQL to XML: Ease of use

13. option “incremental=no” returns whole resultset in 1 row select * from t forxmloptions “incremental=no” option “incremental=yes” returns resultset row-by-row select * from t forxmloptions “incremental=yes” Use Case Can be used to generate XML for large resultset. XML based archival Use Case Can be used to generate XML for smaller resultset XML messaging From SQL to XML: Performance

14. From SQL to XML: User Defined Formats option “columnstyle=attribute” <resultset> <row a=“11” b=“12”/> <row a=“21” b=“22” /> </resultset> option “columnstyle=element” <resultset> <row> <a>11</a> <b>12</b> </row> <row> <a>21</a> <b>22</b> </row> </resultset>

15. select * from titles for xml option columnstyle=attribute, format = yes , header = yes, tablename = "titles” From SQL to XML:User Defined Tags <?xml version="1.0" ?><titles xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><row title_id="BU1032" title="The Busy" type="business" pub_id="1389" price="19.99" advance="5000.00" total_sales="4095" notes="An overview of available database systems with emphasis on common business applications. Illustrated." pubdate="Jun 12 1986 12:00AM" contract="1" /> <row …

16. From XML to SQL: SQL script generation SQLX documents represent SQL tables You can map SQLX documents back to SQL tables • The forsqlscriptj function • Map an SQLX document to an SQL script • Create and populate a table with the data

17. forsqlscriptj: Input SQLX doc Input SQLX document <resultset> <row> <a>11</a> <b>12</b> </row> <row> <a>21</a> <b>22</b> </row> </resultset> Generated SQL script create table "resultset"( "a" integer not null, "b" integer not null ) insert into "resultset“ ("a", "b") values ( 11, 12) insert into "resultset“ ("a", "b") values ( 21, 22)

18. ASE file system What did we tackle? Application Client IN MEMORY XPATH/XSL App logic xml xml objects sql xml FILE SYS DATABASE

19. Topics… RELEVANCE SQL  XML • First steps from SQL to XML • Declarative SQL to XML XML RESULTSETS • Incoming XML data mapped to SQL XML  SQL • Incoming XML data queried as XML XQUERY/XPATH • Stored XML data indexing INDEXING, SCHEMAS • SQL/XML Duality CHUNKS, MIX ‘n MATCH SOAP  SQL • Heterogeneous XML data sources inside SQL QUERIES OVER HTTP • SQL as a web service

20. CUSTOMER TRADES DETAIL DETAIL CID TID … … … … XML indexes XML Storage: What are my options in ASE? • Store and Retrieve XML natively • Schema independent and can be dynamic • Shred XML and store as Relational data, if necessary • Patent pending Fast XML Indexes (as efficient as B-Tree for relation data) • Self defined indexes – No user input necessary • Provides high performance • Support XQUERY and XPATH - Defacto Query Language for XML • Support wildcards and functions for complex querying XMLDocument SHRED SELECT … FOR XML STORE AS TEXT XMLDocument PARSE PARSE XML PARSER ASE

21. XML query as a predicate Can run on any valid XML Can be used as a LIKE clause Do I have a book with a discount Retrieve XML fragments/chunks Can be used to return XML as SQL varchar Give me all the authors of the book with a certain title select * from bookstore_table where ‘//price/@discount ‘ xmltest bookstore_doc select xmlextract(‘//book[title=“History of Trenton”]/author’, bookstore_doc) from bookstore_table The syntax: Using XML Queries in SQL

22. XML Query Language(s) XPath • Used to reference document elements • Provides a common base for: • XSLT • XQuery XQuery • Computational extension of XPath. • Anticipate heavy usage for transformation • Is a great vehicle for heterogeneous data access

23. XPath 101 Here are skeletal forms of those elements • Simple paths /A/B/C • Wild cards /A/*/C • Descendant paths /A//F/G • Attributes /A/B/@T • Subscripts /A/B[1]/C • Predicates /A[B=3]/C /A[//F/@T=4]/C

24. An XML document fragment <bookstore specialty='novel'> <book style='textbook'> • <title>History of Trenton</title> • <author> <name>Mary Bob</name> <publication>Selected Short Stories of <name>Mary Bob</name> </publication> • </author> • <author><name>James Bob</name> </author> • <price discount=“0.05”>55</price> </book> <book> ETC </book> <magazine><title> ETC </title> </magazine> </bookstore>

25. Predicates in XPath Filter elements with predicates in “[…]” • /bookstore/book[title="History of Trenton"]/@style • /bookstore/book[@style="textbook"]/title • /bookstore/book[@style=“textbook" and title=“History of Trenton"] /author[name=“Mary Bob"]/publication • /bookstore/book[//name=“Mary Bob”]/price/@discount • /bookstore/book[price=“12” and price/@discount=“0.05”]//publication

26. XML query as a predicate Can run on any valid XML Can be used as a LIKE clause Retrieve XML fragments/chunks Can be used to return XML as SQL varchar select * from bookstore_table where ‘//price/@discount ‘ xmltest bookstore_doc select xmlextract(‘//book[title=“History of Trenton”]/author’, bookstore_doc) from bookstore_table XPath in SQL: A closer look

27. XML query as a extractor Can run on different types Result can be inserted into a table Retrieve XML fragments/chunks Can be used to return XML as SQL varchar insert into xmlinstab select xmlextract('//book/title/text()' , imagecol returns varchar(200)) , xmlextract('//book/@style', textcol returns varchar(200)) from xmltab XPath in SQL: Shredding

28. Path Processor XML Engine in ASE Architecture ASE Native XML Engine XML Query Engine Table XML Query Applications XML Store Engine XML Data XML Parser I/O Streaming File CIS

29. Storing XML in SQL: The xmlparse function XML documents can be stored either • As character text • As parsed XML • As files on file system The xmlparse built-in function • parse XML documents • Creates path, value indexes • xmlparse(doc)

30. XML documents can be stored either As character text As parsed XML As files on file system The xmlparse built-in function parse XML documents Creates path, value indexes xmlparse(doc) String insertStmt = "insert xmltab values(xmlparse(?))"; FileInputStream fis = new FileInputStream(xmlFile); pstmt.setAsciiStream(1, fis, (int)fis.available()); Indexing XML in SQL: The xmlparse function

31. <paymentSchedule> <payerPartyReference href="#CNPTY"></payerPartyReference> <receiverPartyReference href="#SY"></receiverPartyReference> <amount>200000</amount> <currency>USD</currency> <adjustedPaymentDate>2002-05-18</adjustedPaymentDate> <knownUnknown>Known</knownUnknown> <derivationType>fixed</derivationType> <calculatedPayment> <adjustedStartDate>2001-05-18</adjustedStartDate> Path Index All paths are indexed /paymentSchedule /paymentSchedule/payerPartyReference … /paymentSchedule/calculatedPayment/adjustedStartDate … Index Usage: Various Indexes

32. <paymentSchedule> <payerPartyReference href="#CNPTY"></payerPartyReference> <receiverPartyReference href="#SY"></receiverPartyReference> <amount>200000</amount> <currency>USD</currency> <adjustedPaymentDate>2002-05-18</adjustedPaymentDate> <knownUnknown>Known</knownUnknown> <derivationType>fixed</derivationType> <calculatedPayment> <adjustedStartDate>2001-05-18</adjustedStartDate> Value Index All values are indexed /paymentSchedule/amount/value /paymentSchedule/adjustedPaymentDate/value … /paymentSchedule/calculatedPayment/adjustedStartDate/value … Index Usage: Various Indexes

33. Indexing XML: Usage of all indexes Query Example Query tradeHeader of all transactions tradeID between the range of 10 to 20 "/transaction[transactionData/tradeId >=10 and transactionData/tradeId <=20]//trade/tradeHeader” <tradeHeader> <partyTradeIdentifier> <partyReference href="#CPTY"></partyReference> <tradeId>NUUS001</tradeId></partyTradeIdentifier> <partyTradeIdentifier> <partyReference href="#SY"></partyReference> <tradeId>NUUS001</tradeId></partyTradeIdentifier> <tradeDate>2001-03-21</tradeDate> </tradeHeader> PATH INDEX LINK INDEX VALUE INDEX PATH INDEX

34. Indexing XML: Performance Query Example Query tradeHeader of all transactions tradeID between the range of 10 to 20 "/transaction[transactionData/tradeId >=10 and transactionData/tradeId <=20]//trade/tradeHeader” PATH INDEX • The path index reads ONLY required paths. • For a 10K document or a 2MB document, the size of paths read might be the same! • Wildcard queries use the path index to resolve paths – NO top down traversal. VALUE INDEX • Query Engine does NOT go through each value • Value index brings in only the required ‘nodes’ of XML • 90% of the document could be eliminated if the query is narrow enough ALL INDEXES • Drastically reduce memory requirement at query time thus increasing throughput • Drastically reduces the computation for each query thus reducing response time

35. CUSTOMER RESULTS TRADES DETAIL DETAIL DETAIL RID TID CID … … … … … … XML indexes SQL and XML: I want to use them together • Complete interoperability between XML and SQL at the language and storage level • Support XPATH 2.0, XQUERY 1.0 and SQLX • select …. from … FORXML will generate results in XML • select …. Xmlparse (doc) will parse the xml document seamlessly • Mapping functions to map SQL results to XML and to map XML documents to SQL SELECT … FOR XML SQL RESULT SQL + XPATH QUERY XMLDocument XMLDocument XMLDocument XPATH QUERY XMLRESULT ASE

36. Use XML Data to QUALIFY SQL data select manager, ssn from employeetab, t1 where xmlextract("/hr/employee[manager='John Cox'][contacts/phone/mobile][name/@id = '11']/name/@ssn", xmlcol returns varchar(200)) = t1.ssn SQL/XML Duality

37. Use SQL data to qualify XML data select manager, ssn from employeetab, t1 where "/hr/employee[manager='John Cox'][contacts/phone/mobile][name/@ssn = " + t1.ssn + "]/name/@ssn" xmltest xmlcol SQL/XML Duality

38. ASE file system What did we tackle? Application Client App logic sql-xml xml objects sql-xml FILE SYS DATABASE

39. Topics… RELEVANCE SQL  XML • First steps from SQL to XML • Declarative SQL to XML XML RESULTSETS • Incoming XML data mapped to SQL XML  SQL • Incoming XML data queried as XML XQUERY/XPATH • Stored XML data indexing INDEXING, SCHEMAS • SQL/XML Duality CHUNKS, MIX ‘n MATCH SOAP  SQL • Heterogeneous XML data sources inside SQL QUERIES OVER HTTP • SQL as a web service

40. create proxy_table xfstab external directory at "/usr/u/singha/xpath_testing/docs" create table bookstoretab(xmlcol image) insert bookstoretab select xmlparse(content) from xfstab where filename ="employee.xml" select xmlextract("/bookstore/book[author/first-name = 'Joe']", xmlcol ) from xmltab Create a proxy table for a directory Create an image column Index the file in the server Query the image column Heterogeneous data access – File System

41. ASE file system What did we tackle? Application Client App logic xml xml objects sql xml

42. SQL and XML: But my data is on the internet! • Data is accessible through web services • Make ASE a consumer and producer of web services • Access, search XML over the internet through web services over http • Expose stored procedures as web services • Model Web Service as a proxy table

43. sp_addserver webservices, sds, webservices webservices...gen_sproc_from_wsdl "WSDL File URL", "ASE host name", ASE port number select RRETURN from GETPRICE where _ISBN='0694003611' Add XML Connect as a specialty server or a web service, create a proxy table Use the web service as a proxy table Web Services: ASE as a consumer

44. ASE file system What did we tackle? Application Client XML CONNECT SOAP Server App logic xml xml objects sql xml

45. file system Architecture for XML based Web Services Full Text Search SOAP Server SOAP Server xml xml XML Connect ODBC Oracle, Informix, MSFT, Rdb, Lotus, ODBC, etc. App logic ASE xml objects Ct-Lib Direct Connect sql-xml Applications

46. ASE XML/Integration Roadmap ASE - 12.5.2/12.6 ASE - 15.0 ASE - 12.5.1 • Web Services & Enhanced XML Services • HTTP/SOAP Support • WSDL/UDDI Support • Rich Transformation Support • Enhanced XQUERY support • XML Integration Services • XML Schema support • Multi Document Indexes • XML Data type support • Virtual Data Integration • Federated Querying • Native XML Services • Native XML Support • Fast XML Indexing • SQL and XML Duality • XPATH, XQUERY and FOR XML Support • XML View, Mapping & Transformation

47. Summary • ASE 12.5.1 provides a solid support for XML • Native XML Handling • Rich XML transformation • Useful Web Services functionality • Meeting the customer needs by improving ASE to include new capabilities • High volume & efficient XML data handling • Continue to support emerging XML standards • Web Services support • Rich XML Transformation capabilities • Information Integration capabilities • Working with partnership to deliver cutting edge, end-to-end solution • Committed to making ASE the data backbone for XML, Web Services and Integration

48. Did we cover your questions? XML • XSLT, XPATH, XQUERY support • Ability to mix and match XML and relational data • XML CRUD statements for manipulating XML • Ability to return XML to clients in result sets • Putting database queries on a URL with XML result sets over http • XML Indexing • XML schemas – Support for multiple and changing schemas