1 / 126

SAS912: XML Support in ASA

SAS912: XML Support in ASA. Matthew Young-Lai ASA Query Processing iAnywhere Solutions, Inc. August 2003. Introduction to XML. What is XML?. Extensible Markup Language A text markup language like HTML More flexible because the tags are not pre-defined. Elements.

afia
Download Presentation

SAS912: XML Support in ASA

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SAS912: XML Support in ASA Matthew Young-LaiASA Query ProcessingiAnywhere Solutions, Inc.August 2003

  2. Introduction to XML

  3. What is XML? • Extensible Markup Language • A text markup language like HTML • More flexible because the tags are not pre-defined

  4. Elements • An XML document contains elements: <name> content </name> • Elements can be nested: <name> <name1> content </name1> </name>

  5. Attributes • Elements can contain attributes: <name att1=‘content’ att2=“content” > </name> • Can abbreviate the end tag if an element contains nothing but attributes: <name att1=‘content’ />

  6. Other Structures • CDATA sections • Processing instructions • Namespace declarations and prefixes • Comments • Entity references • Character references

  7. well-formed valid Legal XML • Two levels of legality:

  8. Well-formed XML • Single root element • All opened tags must be closed • Wrong: <li> … <li> … • No overlapping elements • Wrong: <b>…<i> … </b> … </i> • etc.

  9. Valid XML • Elements and attributes are used “correctly” according to a model specified with either • A DTD • context free grammar • An XML Schema • DTD plus data types • XML format

  10. XML Support in ASA • Intended for import and export • Not intended to turn ASA into a hierarchical database • Storage of XML as strings is reasonable in some cases

  11. 2. Exporting XML Tagging Result Sets

  12. Comparison of Methods

  13. Other Points of Comparison • Efficiency • EXPLICIT can be better than SQLX (unions vs subqueries) • Compatibility • SQLX is a draft standard that other vendors are committed to supporting

  14. 2.1 FOR XML RAW • Append to a query: select emp_id as id, emp_fname as name from employee FOR XML RAW

  15. FOR XML RAW (continued) • Result is a 1-row, 1-column result set • The column is a string in the database encoding <row id="102" name="Fran"/> <row id="105"name="Matthew"/> <row id="129" name="Philip"/> <row id="148" name="Julie"/> …

  16. FOR XML RAW (continued) • Result is not well-formed unless you insert a root element: • Example select XMLELEMENT( name root, (select emp_id as id, emp_fname as name from employee for xml raw))

  17. FOR XML RAW (continued) • Result <root> <row id="102" name="Fran"/> <row id="105"name="Matthew"/> <row id="129" name="Philip"/> <row id="148" name="Julie"/> … </root>

  18. FOR XML RAW (continued) • Use ELEMENTS to generate elements rather than attributes • Example select emp_id as id, emp_fname as name from employee for xml raw, ELEMENTS

  19. FOR XML RAW (continued) • Result <row> <id>102</id> <name>Fran</name> </row> <row> <id>105></id> <name>Matthew</name> </row> …

  20. Name Quoting • Illegal names are quoted • Example select emp_id as “emp id” from employee for xml raw

  21. Name Quoting (Continued) • Result <row emp_x0020_id="102"/> <row emp_x0020_id="105"/> <row emp_x0020_id="129"/> <row emp_x0020_id="148"/>

  22. Content Quoting • All markup characters are quoted in content • Example • select ‘<y/>’ x • for xml raw, elements • Result • <row><x>&lt;y/&gt;</x></row>

  23. Content Quoting (continued) • Quoting is not done for content of type xml • Example • select cast( ‘<y/>’ as xml ) x • for xml raw, elements • Result • <row><x><y/></x></row>

  24. Base64 Encoding • Binary columns are Base64 encoded create variable x long binary; set x = xp_read_file( ‘c:\\windows\\explorer.exe’ ); select x for xml raw

  25. Base64 Encoding (continued) • Result <row x="TVqQAAMAAAAEAAAA//8 AALgAAAAAAAAAQAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAA6AAAAA4fug 4AtAnNIbgBTM0hVGhpcyBwcm9ncmF TIGNhbm5vdCBiZSBydW4gaW4gRE9T …"/>

  26. Treatment of NULLs • By default, elements or attributes generated by NULL values are omitted • Example select 1 x, NULL y for xml raw • Result <row x=“1”/>

  27. Treatment of NULLs (continued) • Example Select 1 x, NULL y for xml raw, elements • Result <row> <x>1</x> </row>

  28. Treatment of NULLs (continued) • Use the FOR_XML_NULL_TREATMENT option to change this: set temporary option for_xml_null_treatment=empty • Default is “omit”

  29. Treatment of NULLs (continued) • Example: select 1 x, NULL y for xml raw • Result: <row x=“1” y=“”/>

  30. Treatment of NULLs (continued) • Example: select 1 x, NULL y for xml raw, elements • Result: <row> <x>1</x> <y/> </row>

  31. Schema of the Result • If you need a DTD or XML Schema, you must write it yourself • This applies to all export techniques

  32. 2.2 FOR XML AUTO • Append to a query: select e.emp_id, s.cust_id, s.region from employee e key join sales_order s order by e.emp_id FOR XML AUTO • Splits up the result set • vertically by which tables are referenced • horizontally by values

  33. Vertical Splitting e.emp_id s.cust_id s.region 129 119 Eastern 129 119 Western 129 131 Eastern 195 109 Eastern 195 121 Central

  34. Vertical Splitting e.emp_id s.cust_id s.region 129 119 Eastern 129 119 Western 129 131 Eastern 195 109 Eastern 195 121 Central

  35. Horizontal Splitting e.emp_id s.cust_id s.region 129 119 Eastern 129 119 Western 129 131 Eastern 195 109 Eastern 195 121 Central

  36. Horizontal Splitting e.emp_id s.cust_id s.region 129 119 Eastern 129 119 Western 129 131 Eastern 195 109 Eastern 195 121 Central

  37. e.emp_id s.cust_id s.region 129 119 Eastern 129 119 Western 129 131 Eastern 195 109 Eastern 195 121 Central Horizontal Splitting

  38. FOR XML AUTO (continued) • Every segmented region generates an element • The element is named after the table that the columns came from

  39. e.emp_id s.cust_id s.region 129 119 Eastern 129 119 Western 129 131 Eastern 195 109 Eastern 195 121 Central FOR XML AUTO (continued) 2 <e> elements 5 <s> elements

  40. FOR XML AUTO (continued) • A region is a child of the region to its left

  41. e.emp_id s.cust_id s.region 129 119 Eastern 129 119 Western 129 131 Eastern 195 109 Eastern 195 121 Central FOR XML AUTO (continued) <e> <s/> <s/> <s/> </e> <e> <s/> <s/> </e>

  42. FOR XML AUTO (continued) • Every column is an attribute: <e emp_id=‘129’> <s cust_id=‘119’ region=‘Eastern’/> <s cust_id=‘119’ region=‘Western’/> <s cust_id=‘121’ region=‘Eastern’/> </e> <e emp_id=195> <s cust_id=‘109’ region=‘Eastern’/> <s cust_id=‘121’ region=‘Central’/> </e>

  43. FOR XML AUTO (continued) • These work the same as RAW mode: • ELEMENTS mode • Name quoting • Content Quoting • Base64 encoding • Treatment of NULL values

  44. 2.3 SQLX • Five functions • xmlelement, xmlconcat, xmlforest, xmlgen, xmlagg • An XML type • create domain xml long varchar

  45. XMLELEMENT() • Example select xmlelement( name x, ‘abc’ ) • Result <x>abc</x>

  46. XMLELEMENT (continued) • Can insert attributes select xmlelement( name x, xmlattributes( ‘abc’ as y, ‘def’ as z ) ) • Result <x y=‘abc’ z=‘def’/>

  47. XMLELEMENT (continued) • Can have any number of arguments: select xmlelement( name x, ‘ab’, ‘de’, ‘f’ ) • Result <x>abcdef</x>

  48. XMLFOREST() • Example select xmlforest( ‘abc’ as x, ‘def’ as y ) • Result <x>abc</x> <y>def<y>

  49. XMLFOREST (continued) • Example select xmlforest( emp_id as id, emp_lname as name, state ) from employee • Result <id>102</id> <name>Whitney</name> <state>MA</state> <id>105</id> <name>Cobb</name> <state>MA</state> …

  50. XMLCONCAT() • Example select xmlconcat( xmlelement( name x, ‘abc’ ), xmlelement( name y, ‘def’ ) ) • Result <x>abc</x> <y>def</y>

More Related